G
Guest
Hi,
I have written an excel addin that builds reports on a workbook when the
user clicks on a customized toolbar button (that calls a macro). In
addition to that, I also need to be able to run that macro automatically at
6am every morning through a vb application that would always be running in
the background.
There are two main scenarios that I need to handle in that vb application.
One (the easiest one) is when excel is not running. I have a simple shell
command that calls excel (which loads the addin) and also sends a string with
the argument that runs the macro. In the addin side, upon workbook open,
there is code that reads in the command line, parses it and runs the macro if
the correct argument is sent.
That scenario works well.
The second scenario is when excel is already running. I am getting a
reference of that specific excel object through the GetObject function (so
that I can see the actual instance running instead of creating a new
instance). Through that reference, I can see the whether or not the
specific workbook is loaded, and if it's not, I open it. Now, my obstacle
is when trying to run the macro from the addin... how do I call that macro?
Thank you.
This is the code that I have so far:
strFileName = "C:\Projects\ExcelAddIn\ApiCallTest.xls"
bExcelRunning = ProcessIsRunning("Excel")
If bExcelRunning = False Then
ExcelWasNotRunning = True
RetVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
C:\Projects\ExcelAddIn\ApiCallTest.xls /e/RunReport/", 1)
Else
Set appXL = GetObject(, "Excel.Application")
For i = 1 To appXL.Workbooks.Count
If appXL.Workbooks(i).FullName = strFileName Then
bFound = True
Exit For
End If
Next i
If bFound = False Then
'open the workbook
Set objWorkbook = GetObject(strFileName)
bWorkbookWasClosed = True
appXL.Visible = True
appXL.Parent.Windows(1).Visible = True
End If
'***Here I need to call the "RunReport" macro ***
End If
If ExcelWasNotRunning = True Then
appXL.Application.Quit
End If
Set appXL = Nothing
End Sub
I have written an excel addin that builds reports on a workbook when the
user clicks on a customized toolbar button (that calls a macro). In
addition to that, I also need to be able to run that macro automatically at
6am every morning through a vb application that would always be running in
the background.
There are two main scenarios that I need to handle in that vb application.
One (the easiest one) is when excel is not running. I have a simple shell
command that calls excel (which loads the addin) and also sends a string with
the argument that runs the macro. In the addin side, upon workbook open,
there is code that reads in the command line, parses it and runs the macro if
the correct argument is sent.
That scenario works well.
The second scenario is when excel is already running. I am getting a
reference of that specific excel object through the GetObject function (so
that I can see the actual instance running instead of creating a new
instance). Through that reference, I can see the whether or not the
specific workbook is loaded, and if it's not, I open it. Now, my obstacle
is when trying to run the macro from the addin... how do I call that macro?
Thank you.
This is the code that I have so far:
strFileName = "C:\Projects\ExcelAddIn\ApiCallTest.xls"
bExcelRunning = ProcessIsRunning("Excel")
If bExcelRunning = False Then
ExcelWasNotRunning = True
RetVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
C:\Projects\ExcelAddIn\ApiCallTest.xls /e/RunReport/", 1)
Else
Set appXL = GetObject(, "Excel.Application")
For i = 1 To appXL.Workbooks.Count
If appXL.Workbooks(i).FullName = strFileName Then
bFound = True
Exit For
End If
Next i
If bFound = False Then
'open the workbook
Set objWorkbook = GetObject(strFileName)
bWorkbookWasClosed = True
appXL.Visible = True
appXL.Parent.Windows(1).Visible = True
End If
'***Here I need to call the "RunReport" macro ***
End If
If ExcelWasNotRunning = True Then
appXL.Application.Quit
End If
Set appXL = Nothing
End Sub