call a excel function from a vb project

A

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
 
N

ndalal

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top