Running Excel macro from Access code

A

amanda.pinckard

I have an access database which has all my data and queries to feed an
excel file which has links to the access database via ODBC get data.
I then have a macro in Excel which runs all of the updates to the
various ranges of data in the excel file. I would like my code in
access which runs the initial steps needed in the database to launch
excel and the file and then run the macro, save the file as a new name
and close the file. Ideally I would be able to program the file to go
through and run each of the 20 categories by automatically launching
the file running the macro, saving as the new file name, closing and
then moving on to the next category. I have been able to get it to
open excel and the file but not to actually run the macro, I haven't
even started getting to save it or close it!
Can anyone help? This code would save me hours and hours of work every
month.
Thanks!

Function Test1()

'declare variables
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "PERSONAL.xls"

'Open Excel w personal workbook open
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.Workbooks.Open("C:\Documents and Settings\APinckard
\Application Data\Microsoft\Excel\XLSTART\" & strFile)
xls.Visible = True

strFile = "desk report.xls"
strMacro = "UDeskRep"

Set xlWB = xls.Workbooks.Open("C:\Reports08\Reports-021408\" &
strFile)
xls.Visible = True

'XL.Run "UDeskRep"
'Set xlWB = xls.RunMacro.Open("UDeskRep")

End Function
 
A

amanda.pinckard

I have an access database which has all my data and queries to feed an
excel file which has links to the access database via ODBC get data.
I then have a macro in Excel which runs all of the updates to the
various ranges of data in the excel file.  I would like my code in
access which runs the initial steps needed in the database to launch
excel and the file and then run the macro, save the file as a new name
and close the file.  Ideally I would be able to program the file to go
through and run each of the 20 categories by automatically launching
the file running the macro, saving as the new file name, closing and
then moving on to the next category.  I have been able to get it to
open excel and the file but not to actually run the macro, I haven't
even started getting to save it or close it!
Can anyone help? This code would save me hours and hours of work every
month.
Thanks!

Function Test1()

'declare variables
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "PERSONAL.xls"

'Open Excel w personal workbook open
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.Workbooks.Open("C:\Documents and Settings\APinckard
\Application Data\Microsoft\Excel\XLSTART\" & strFile)
xls.Visible = True

strFile = "desk report.xls"
strMacro = "UDeskRep"

Set xlWB = xls.Workbooks.Open("C:\Reports08\Reports-021408\" &
strFile)
xls.Visible = True

'XL.Run "UDeskRep"
'Set xlWB = xls.RunMacro.Open("UDeskRep")

End Function
Click to expand...

Ok Yeah! I got A macro to finally run, however the actual macro I
want it to run references the database that is making the macro run so
I am getting an error saying that the database is locked and can not
be accessed. Is there any way around this?
Thanks!
Amanda
 
J

Jeanette Cunningham

Amanda,
would you tell us what you are trying to get the access database to do.
It looks like you want to open a report, is this it?

Jeanette Cunningham

I have an access database which has all my data and queries to feed an
excel file which has links to the access database via ODBC get data.
I then have a macro in Excel which runs all of the updates to the
various ranges of data in the excel file. I would like my code in
access which runs the initial steps needed in the database to launch
excel and the file and then run the macro, save the file as a new name
and close the file. Ideally I would be able to program the file to go
through and run each of the 20 categories by automatically launching
the file running the macro, saving as the new file name, closing and
then moving on to the next category. I have been able to get it to
open excel and the file but not to actually run the macro, I haven't
even started getting to save it or close it!
Can anyone help? This code would save me hours and hours of work every
month.
Thanks!

Function Test1()

'declare variables
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "PERSONAL.xls"

'Open Excel w personal workbook open
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.Workbooks.Open("C:\Documents and Settings\APinckard
\Application Data\Microsoft\Excel\XLSTART\" & strFile)
xls.Visible = True

strFile = "desk report.xls"
strMacro = "UDeskRep"

Set xlWB = xls.Workbooks.Open("C:\Reports08\Reports-021408\" &
strFile)
xls.Visible = True

'XL.Run "UDeskRep"
'Set xlWB = xls.RunMacro.Open("UDeskRep")

End Function
Click to expand...

Ok Yeah! I got A macro to finally run, however the actual macro I
want it to run references the database that is making the macro run so
I am getting an error saying that the database is locked and can not
be accessed. Is there any way around this?
Thanks!
Amanda
 

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

Similar Threads

Run Excel macro with arguments from Access 3
Macro in one location and file to run the macro in another locatio 1
Referencing a public variable in excel from access 2
Exporting Data to Excel from Access for Analysis 1
Run Excel Macro from Access and save changes to .CSV format 4
run an Excel macro from Access : Run time error 1004 2
Running an Excel Macro from Access 6
Text to Columns Access VBA 1

Top