Running Excel macro from Access code

[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.RunMacrpen("UDeskRep")

End Function

 

[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.RunMacrpen("UDeskRep")

End Function

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

 

[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.RunMacrpen("UDeskRep")

End Function

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