Code to open MS Excel and run macro

[jd316632]

I've already got in MS Access 07 a macro to export my data into an excel
doc across multiple sheets and the name to update since it is ran daily.
Now what I would like to do is have Access just tell Excel to run the
macro that handles the formatting across all the sheets.

I've searched the forums all I'm able to find is:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing

Updated with my information it looks like:

Dim xlsx As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "All Open Tickets All Queues - " &
Format(Date(),"mm-dd-yyyy") & ".xlsx"
strMacro = "PERSONAL.XLSB!All_Open_Tickets_Formatting"
Set xlsx= CreateObject("Excel.Application")
xlsx.Visible = True
Set xwkb = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\All Open Tickets All Queues - " &
Format(Date(),"mm-dd-yyyy") & ".xlsx" & strFile)
xlsx.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xlsx.Quit
Set xlsx = Nothing


but I get a compile error for invalid outside procedure for
strFile = "All Open Tickets All Queues - " &
Format(Date, "mm-dd-yyyy") & ".xlsx"

If I knew more about VBA I might be able to figure why however in
looking for various forums I'm not able to find a code that will do the
trick.

Any help is greatly appreciated.

 

[Ken Snell [MVP]]

A syntax error for basic setup -- change this code line:

Set xwkb = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\All Open Tickets All Queues - " &
Format(Date(),"mm-dd-yyyy") & ".xlsx" & strFile)


to this:

Set xwkb = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\" & strFile)



However, the macro is not located in the strFile file; it's located in the
Personal.xlsb file. So your code won't work with this step:

xlsx.Run strFile & "!" & strMacro


I've not tried to run a macro from a personal workbook file before (in fact,
I'm not sure that EXCEL will open the Personal.xlsb file when you create an
EXCEL instance in code), so what I'm going to suggest is a "test and try"
idea. Try changing the above code line to this:

xlsx.Run strMacro


Or replace the "xlsx.Run strFile & "!" & strMacro" step with these steps
(change the generic path to the real path):

Dim xlwbP As Object
Set xlwbP = xls.Workbooks.Open("PathtothePersonalWorbookFiles" & _
"\Personal.xlsb"
xlsx.Run strMacro
xlwbP.Close False
Set xlwbP = Nothing

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[jd316632]

First off thank you for your assistance with this

Dim xlsx As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "All Open Tickets All Queues - " &
Format(Date(),"mm-dd-yyyy") & ".xlsx"
strMacro = "PERSONAL.XLSB!All_Open_Tickets_Formatting"
Set xlsx= CreateObject("Excel.Application")
xlsx.Visible = True
Set xwkb = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\" & strFile)
Dim xlwbP As Object
Set xlwbP = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Application Data\Microsoft\Excel\XLSTART" & _
"\Personal.xlsb"
xlsx.Run strMacro
xlwbP.Close False
Set xlwbP = Nothing
xwkb.Close False
Set xwkb = Nothing
xlsx.Quit
Set xlsx = Nothing

I'm still getting an error regarding (Invalid Outside Procedure) with
the red font highlighted once I click ok.
strFile = "All Open Tickets All Queues - " & Format(Date, "mm-dd-yyyy")
& ".xlsx"

 

[Ken Snell [MVP]]

This code line:

strFile = "All Open Tickets All Queues - " & Format(Date, "mm-dd-yyyy")
& ".xlsx"


needs to be a single line in your VBA code module. It cannot be on two
separate lines.

 

[jd316632]

It is all on one line when I receive the above mentioned error

 

[Ken Snell [MVP]]

I do not understand how setting a string text value to a string variable
would cause an error "Invalid Outside Procedure".

Is your code inside a Sub or Functioni procedure? Or is the code just
sitting in a module all by itself?

Your code must be inside a Sub or Function procedure.

 

[jd316632]

You're exactly right, it didn't have the function/end function. I've
added that, now it's opening the correct excel document but does not run
the macro.

I'm receiving a run time error (424) object required. When I debug the
line highlighted is:
Set xlwbP = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Application Data\Microsoft\Excel\XLSTART" & _
"\Personal.xlsb")

I've attempted to change the xls.Workbooks.Open to xlsb, and making
changing the file path to ("C:\Documents and
Settings\JHenson\Application
Data\Microsoft\Excel\XLSTART\Personal.xlsb") both have failed. I've
also checked the macro name to verify it is correct and tried removing
the PERSONALXLSB! in front of the macro name. All have returned the
same error.

Any suggestions would be greatly appreciated

 

[Ken Snell [MVP]]

Try deleting the "b" from "xlsb" in the code step lines:

Set xlwbP = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Application Data\Microsoft\Excel\XLSTART" & _
"\Personal.xls")

 

[jd316632]

That is what it was originally set as, I tried adding the b to xls and
it didn't work to I changed it back. As it stands now the code
currently looks like

Function Excel_Macros()

Dim xlsx As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "All Open Tickets All Queues - " & Format(Date, "mm-dd-yyyy")
& ".xlsx"
strMacro = "PERSONAL.XLSB!All_Open_Tickets_Formatting"
Set xlsx = CreateObject("Excel.Application")
xlsx.Visible = True
Set xwkb = xlsx.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\" & strFile)
Dim xlwbP As Object
Set xlwbP = xls.Workbooks.Open("C:\Documents and
Settings\JHenson\Application Data\Microsoft\Excel\XLSTART" & _
"\Personal.xls")
xlsx.Run strMacro
xlwbP.Close False
Set xlwbP = Nothing
xwkb.Close False
Set xwkb = Nothing
xlsx.Quit
Set xlsx = Nothing

End Function

It opens the excel document on my desktop, it's when attempting to run
the macro that i receive the above mentioned error. I've also tried
unhiding the Personal.xlsb workbook as well, regardless of whether it is
hidden or not, or if the code has xls or xlsb I receive a run time error
(424) Object Required.

 

[Ken Snell [MVP]]

You also need to eliminate the "b" in this code step line:

strMacro = "PERSONAL.XLSB!All_Open_Tickets_Formatting"

Change it to

strMacro = "PERSONAL.XLS!All_Open_Tickets_Formatting"

 

[jd316632]

Working perfectly, final code looks like:

Function All_Tickets_All_Queues_Formatting()

Dim xlsx As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "All Open Tickets All Queues - " & Format(Date, "mm-dd-yyyy")
& ".xlsx"
strMacro = "PERSONAL.XLSB!All_Open_Tickets_Formatting"
Set xlsx = CreateObject("Excel.Application")
xlsx.Visible = True
Set xwkb = xlsx.Workbooks.Open("C:\Documents and
Settings\JHenson\Desktop\" & strFile)
Dim xlwbP As Object
Set xlwbP = xlsx.Workbooks.Open("C:\Documents and
Settings\JHenson\Application Data\Microsoft\Excel\XLSTART" & _
"\Personal.xlsb")
xlsx.Run strMacro
xlwbP.Close False
Set xlwbP = Nothing
xwkb.Save
Set xwkb = Nothing

End Function

It opens the excel doc, performs the macro and saves the changes. It
leaves the document open so I can review before closing.

Thanks for all your help, it's greatly appreciated!