Running Excel Macro from Module (1 Viewer)

[Huskergirl]

I am a newbie when it comes to using VBA code in an Access module. I am attempting to run a macro in Excel 2007 via a module in Access 2007. I found some code but I am getting "Compile error: Expected: line number or label or statement or end of statement". When I close the message box, the "6." is highlighted. Below is my code:

Option Compare Database
Visual Basic:
1.Sub RunExcelMacro()
2.Dim xl As Object
3.
4.'Step 1: Start Excel, then open the target workbook.
5. Set xl = CreateObject("Excel.Application")
6. xl.Workbooks.Open ("C:\Documents and Settings\nel58556\My Documents\Net Improvement Report\CM NEW RPS - PM AT RISK.xlsm")
7.
8.'Step 2: Make Excel visible
9. xl.Visible = True
10.
11.'Step 3: Run the target macro
12. xl.Run "MyMacro"
13.
14.'Step 4: Close and save the workbook, then close Excel
15. xl.ActiveWorkbook.Close (True)
16. xl.Quit
17.
18.'Step 5: Memory Clean up.
19. Set xl = Nothing
20.
21.End Sub

I have no idea how to fix this so any help would be greatly appreciated!!

Thanks in advance,

Sara

 

[boblarson]

Do you have the periods in the numbering? If so, get rid of them. Line numbers don't have periods. Also is the Visual Basic: actually in the code? That should not be there.

 

[Huskergirl]

THANK YOU!!! Had I thought about it as similar to Excel VBA, I probably would have figured it out but, as it was, my head was ready to explode. The revised code is below and works perfectly!

Option Compare Database
Sub RunExcelMacro()
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\Documents and Settings\nel58556\My Documents\Net Improvement Report\CM NEW RPS - PM AT RISK.xlsm")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Run the target macro
xl.Run "DeleteWorksheets"
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Sub

Thank you so much for the quick response, my weekend is made...

Sara