call a excel function from a vb project

[Alex281]

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

[ndalal]

I am looking for the same answer. If you are able to find the answer to
this, please post it here. Thanks !!

[Tim Williams]

Try

appXL.Run "FileName.xla!SubName"

Tim

"Alex281" <[email protected]> wrote in message
news:[email protected]...
> 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
>