Building a System of Excel Macros Executable by SAS - Exchanging Data Between SAS and Microsoft Excel - page 204

“Workbook_Open” VBA subroutine. The change we need to make is to have the Excel Workbook_Open
VBA subroutine be selective in choosing which workbooks to modify.
The VBA code in Figure 12.4.8 below shows the code for the new “ Workbook_Open ” VBA subroutine.
This macro will test cell A1 of the first worksheet and determine if it contains a special code to trigger
processing a VBA macro. The message box calls are also removed from the updated “ Workbook_Open ”
VBA subroutine. There is also a new subroutine required for Module1. Here we will just put the code in
Figure 12.4.9 as a place holder. This subroutine will display only a message box to indicate that the routine
was executed. This example is designed to detect the special code “graph_1” (case-sensitive) after stripping
off two question marks from the front and back of the string. Failure to find either question mark will leave
the contents of cell A1 undisturbed and return control to Excel. If the question marks are found, the data
between the question marks will be removed and the remainder of the cell value restored. This can be
changed to suit your needs.
Figure 12.4.8: Excel Workbook_open Code to Process an Excel Macro Based Upon a
Control Field.
Private Sub Workbook_Open()
Dim my_count As Integer, my_book As String, my_sheet As String
my_count = Workbooks.Count
For i = 1 To my_count
my_book = Workbooks(i).Name
my_sheet = Workbooks(i).Sheets(1).Name
If my_book <> "PERSONAL.XLSB" Then
my_cell_a1 = Workbooks(i).Sheets(1).Range("A1").Value
my_cell_size = Len(my_cell_a1)
' look for a second ? from right side of value '
my_flag_size = InStrRev(my_cell_a1, "?") - 1
' if my_flag_size is greater than 1 then two ? were found
' this is a special processing workbook
' so get the info and process the workbook
' else ignore and return control to Excel and the user
If my_flag_size > 1 Then
' get left ? character and data between the two ?'s
my_report = Left(my_cell_a1, my_flag_size)
' remove the left ?
my_data_size = Len(my_report) - 1
my_report = Right(my_report, my_data_size)
' remove control characters from Cell A1 value
' and store in cell A1
Workbooks(i).Sheets(1).Range("A1").Value = _
Right(my_cell_a1, (my_cell_size - (my_flag_size + 1)))
' add new cases to process new reports
Select Case my_report
Case "graph_1"
Call Module1.my_graph_1(my_sheet)
Case Else
End Select
End If
End If
Next
End Sub