Database Reference
In-Depth Information
'wait for user
Response = MsgBox(Msg, Style, Title)
For I = 1 To my_count
my_book = Workbooks(i).Name
my_sheet = Workbooks(i).Sheets(1).Name
Msg = "sub=workbook count loop - workbook counter= " + Str(i)
Title = "Workbook name = " + my_book
Response = MsgBox(Msg, Style, Title)
if my_book <> "PERSONAL.XLSB" Then
Call Macro1
End If
Next
End Sub
An Explanation of the “Workbook_Open” Excel Macro in Figure 12.4.2b
The code above (called Excel subroutine “Private Sub Workbook_open()”) seems a little more
complex than needed, and it is a little (but the message boxes can go away). If the macro had been
a simple call to “Macro1”, you might think it would have worked--but remember that now your
computer has a new hidden workbook that Excel will always (yes, always) open.
The Excel VBA desktop in Figure 12.4.1 above has TWO workbooks open, “PERSONAL.XLSB”
and “Hello_World_Test.xml”.
One thing that has been relatively consistent is that when SAS uses the “X” command, both
workbooks will be open (“Hello_World_Test.xml” and “PERSONAL.XLSB”) when the
“WORKBOOK_OPEN” macro runs.
This will allow the “Macro1” code to execute. However, a double-click on an Excel file icon will
usually have only one workbook open. Then the Excel macro “WORKBOOK_OPEN” runs (that
workbook is “PERSONAL.XLSB”). So, we will assume for the purposes of this explanation that
all calls to Excel come from SAS and the “X” command.
Figure 12.4.1 above sets up two message boxes to be displayed. The first shows the executing
subroutine, and the second shows the code is on the processing loop and the file index counter that
is being used. See Figures 12.4.3 and 12.4.4.
The second time through the workbook count loop, the code for MACRO1 executes and displays
word “World”. See Figure 12.4.5.
Figure 12.4.6 is the workbook after the “Workbook_open” processing is complete, and the
workbook is ready for you to use.
Search WWH ::




Custom Search