Database Reference
In-Depth Information
Virtually anything that you can do using Excel you can do using VBA. After all, Excel was written using
the VBA language. So, you can modify cells by adding data to them, outlining them, moving them, copying
them, or clearing them. You can manipulate rows or columns of data. Files can be read, written, or
converted from one format to another. By writing the VBA code yourself, you control the order of the
actions that Excel takes. The intent here is to show you how to create formatted Excel output files that are
ready for delivery to your user to execute in minutes instead of spending much more time doing it manually
yourself. Making them high quality will be left to you. Of course, you need to write the VBA code.
A Sample VBS Script to Open Excel; Load, Execute, and Delete a VBS Macro
Now, for the fun part, we need a VBS routine to do the work. We need it to open the XML file, load our
VBA macros, build our graph, and save our data in the XLS format. The code is in Figure 13.3.7a, with a
detailed description of how it works following the code.
Figure 13.3.7a: The Contents of the VBS Script File VBS_Execute_script.vbs.
Dim Input_Excel, output_excel, bas_code_path, vba_module, vba_code, objxl, objwk,
vbCom, myMod
➊
Input_Excel = WScript.Arguments(0) 'Full File path and Input file name
output_excel = WScript.Arguments(1) 'Full File path and Output file name
bas_code_path= WScript.Arguments(2) 'Full File path Location of .bas file
vba_module = WScript.Arguments(3) 'VBA module name (without the
.b
as)
vba_code = WScript.Arguments(4) 'VBA subroutine name to execute
➋
set objxl = CreateObject("Excel.Application") 'Start Excel
➌
set objwk = objxl.Workbooks.Open(Input_Excel) 'Open the input file
➌
'Activate special software
set vbCom = objxl.ActiveWorkbook.VBProject.VBComponents
➍
objxl.DisplayAlerts = wdAlertsNone 'Turn off error messages
➎
if vba_module <> "" then
vbCom.Import ("" & bas_code_path & vba_module & ".bas") 'Import VBA Code
vbCom.Import ("" & bas_code_path & "Common.bas") 'Import Common Routines
objxl.Run "" & vba_module & "." & vba_code & "" 'Run VBA Code
'Remove VBA modules
Set myMod = objxl.ActiveWorkbook.VBProject.VBComponents("" & vba_module & "")
objwk.VBProject.VBComponents.Remove myMod
Set myMod = objxl.ActiveWorkbook.VBProject.VBComponents("Common")
objwk.VBProject.VBComponents.Remove myMod
end if
➏
'Save as Excel *.xls workbook
if Input_Excel <> output_excel then objxl.ActiveWorkbook.SaveAs output_excel,
51
'
56=xlExcel8, 51=xlsx format
➐
if Input_Excel = output_excel then objxl.ActiveWorkbook.Save
➑
objxl.Workbooks.Open(output_excel).Close
➒
objxl.Quit
set objxl = nothing
set objwk = nothing
Here is a step-by-step explanation of the VBS code in Figure 13.3.7a. These descriptions highlight the
function of the VBS command without regard to the syntax. The general form of the commands that begin
with “SET” are to create a user-named object or instance of whatever is on the right side of the equals sign.
Then the name created on the left side of the equals sign can be used as a shorthand definition of the thing
on the right side. This is similar to the way a LIBNAME or FILENAME statement works in SAS. Other
code (with an equals sign) is an assignment of values. The code in Figure 13.3.7a
Search WWH ::
Custom Search