Database Reference
In-Depth Information
must be delivered to the Visual Basic Script processor on one line. Some of the code (like lines (1) and (7)
wrapped unintendedly and should be on one line.
➊
The code starts by defining the names of input parameters, object names, or variables to be used by the
VBS routine. This is done without giving these names any data type or usage definition at the
beginning of the routine.
➋
The five lines describing the WScript arguments include a number in parentheses that defines the input
parameters number. We will send these parameters to the VBS routine as part of the “X” command
when the VBS code is executed from SAS. The parameters are numbered starting from zero and will
have the usage as described in the comments on the right of each line.
➌
The VBS command CreatObject actually starts running Excel, and the Workbooks.open command
opens the Excel workbook named in the parameter sent from SAS. The workbook is called
“Input_Excel”. The SAS program provided the full path and filename of the *.xml file created by the
ODS tagset template EXCELXP. You still do not see anything; it is all hidden.
➍
The SET command that creates vbCom is providing access to extra Windows software available from
the operating system. This is required for some of the other commands to work.
➎
The “objxl.DisplayAlerts = wdAlertsNone” command turns off all messages that VBS may send.
➏
The VBS code from the IF statement to the END IF statement is described here.
a.
If the parameter called vbs_module, which is passed in argument 3 of the SAS X command, is
not empty, then the code between the “IF” and “END IF” commands is executed. When the
parameter has a value, it is the name of a *.bas file to include into the processing. The
parameter called bas_code_path contains the location of the *.bas code modules.
b.
The vbCom.Import VBS commands each open a *.bas module. The code in parentheses
concatenates characters to build the full path and file name of the *.bas files.
c.
The first *.bas file read is the Excel macro you want to execute.
d.
The second *.bas module is where some common macro subroutines are stored. This code
exists optionally to provide standardized and optimized code to all of the users of your
system. This could be considered optional, but it is well worth the effort to create this library
of basic Excel macros to perform small tasks. This frees your time and makes writing a
program faster.
e.
The objxl.run command executes the Excel macros.
f.
When the VBA macros finish running, the “.VBProject.VBComponents.Remove” command
deletes the Excel macros from the workbook.
g.
The “END IF” command finishes the block of VBS code that executes the Excel macro. Steps
7 through 12 are optional. If no *.bas module is passed, this routine can be used to just rename
a file or convert the input file into a *.xlsx Excel file format. This routine converts to a *.xlsx
output file, but the ActiveWorkbook.SaveAs command described in Step 7 can be modified to
do other conversions.
If the Input_Excel and Output_Excel parameter values are the same, then the Output_Excel file is just
saved in its updated form. The design of this VBS code routine was to accept a *.xml output from the
SAS ODS tagset EXCELXP. After processing the Excel VBA macros, the VBS code module will
provide an Excel *.xlsx file in native Excel format.
➐
This step is executed if the Input_Excel parameter equals the Output_Excel parameter. If the
parameters are equal than no conversion has been requested. If the file paths and names are the same,
the input file is saved in the same format as it was opened..
➑
After you save any changes made, the Excel workbook is closed and the Visual Basic Script is shut
down. Control of the computer is returned to the next process. When this *.vbs code module is called
from SAS using the “X” command and when the SAS options XWAIT and XSYNC are active, the
SAS program will wait for the VBA code to complete and solve some timing issues that could
otherwise cause errors.
➒
Depending upon the size of your Excel XML file and how well your VBA macros are written, it can take
from a few seconds to a couple of hours to format your file. But, for this process to take a couple of hours it
will need a workbook with several hundred pages or very poorly written VBA macros. The good thing is
Search WWH ::
Custom Search