Run Excel Macro from Access

[ryguy7272]

I found the sample code below on this DG; modified slightly for my specific
path and workbook name:
DoCmd.TransferSpreadsheet acExport, 8, "Marks Query#1",
"C:\Documents and Settings\rshuell\Desktop\Ryan\", True

Set objXls = CreateObject("Excel.Application")
myFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Marks
Query#1.xls"
objXls.Workbooks.Open ("" & myFile)
objXls.Visible = True
Set MyBook = objXls.Workbooks("Marks Query#1.xls")

SheetsCount = MyBook.Worksheets.Count

Set MySheet = MyBook.Worksheets(SheetsCount)
MySheet.Activate
MySheet.Application.Run "Macro1"


This runs from Access (of course). When the macro fires, I get this message:
"Microsoft Jet database engine cannot open the file’C:\Documents and
Settings\etc... It is already open by another user or you need specific
permission to view its data" How do I handle this? I'd like to be able to
fire my Excel macro from Access. If i can't get it working, I'd like to open
the Excel workbook, and at the very least run a snippet of code which
is...'Selection.RemoveSubtotal'. I apply subtotals to a sheet in the
workbook, and email it to a colleague. I think I need to remove the
subtotals before exporting an Access Query to this workbook. Any ideas?

Thanks,
Ryan---

 

[Magius96]

I've run into this one myself, and never got it working correctly. Instead I
built the macro into my Access code. What I mean is, perhaps its better to
have access programattically perform all the functions that the macro would
have done, this way you don't have to invoke the macro in Excel, just let
Access handle it.

 

[Ken Snell \(MVP\)]

Here is some generic code for how to run an EXCEL macro from ACCESS:

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
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
End Sub