Open Excel, Call (Excel) Macro from Access

[DummyForAccess]

I'm trying to automate a very manual process. I have a database built in Access that will import and refresh tables. Then I need to have a macro that opens an Excel spreadsheet and calls an (Excel) macro. (This macro is necessary because it formats the spreadsheet in a certain way, populates worksheets with data from my refreshed tables, and creates dropdown pick-lists from those worksheets). Most of it works well, but I'm having a problem getting Accsess to get the Excel macro running...

I have code that I got to paste into a module to open excel and call the (excel) query. Problem is if I use "open Module" cmd in an Access Macro, it just opens the module in a vb development window. I know I'm missing a step or something???

 

[yotwiggy]

You might try looking into AutoExec from Excel. At one time when one opened excel this would fire off, but I don't know what it is in later versions.

 

[GHolden]

If you open Excel using Automation in VBA you can run a macro like...

Private Sub mRunMacro()
Dim ExcelApp As New Excel.Application

With ExcelApp
'open Excel with file C:\Test
.Workbooks.Open "C:\Test"

'Make Excel visible
.Visible = True

'This runs Macro1
.Run ("Macro1&quot

End With

End Sub There are two ways to write error-free programs; only the third one works.

 

[DummyForAccess]

The code I'm using is similar, if I run it from the Module (VB) window, it works fine. The problem I'm having is how do I call the module or the sub from Access? If I use the Open Module option in a Macro it just opens the module, it doesn't run. In Help it says to create a function that calls the sub? How do I create a function?? Ugh...

 

[GHolden]

OK, make it a public sub in the code module.

Public Sub mRunMacro()
Dim ExcelApp As New Excel.Application

With ExcelApp
'open Excel with file C:\Test
.Workbooks.Open "C:\Test"

'Make Excel visible
.Visible = True

'This runs Macro1
.Run ("Macro1&quot

End With

End Sub

In the form or where ever you want to execute if from just call the sub.

eg. if you've make a command button called cmdOpenExcel

In the on click event put..

Private Sub cmdOpenExcel_Click()
mWriteToExcel
End Sub There are two ways to write error-free programs; only the third one works.

 

[ejc00]

If you haven't gotten it to work yet... you can try this:

Public Function RunExcelMacro()

Dim XL As Excel.Application
Dim xlBook As Excel.Workbook

Set XL = CreateObject("Excel.Application&quot

Set xlBook = XL.Workbooks.Open(&quot;C:\<insert path and file name here>.xls&quot

XL.Application.Run &quot;'<workbook name>.xls'!<macro name>&quot;

XL.Workbooks.Close
Set xlBook = Nothing

XL.Quit
Set XL = Nothing

End Function


Let me know if this doesn't work, or if you have any other questions...

Good luck

 

[DummyForAccess]

I haven't had a chance to try either, the second code looks much like what I had, so I'm sure it will work. Can you tell me how to get the function to start in an Access Macro(if that's possible)?

 

[ejc00]

To get the code which I sent before to run from a macro, use the &quot;RunCode&quot; action in the macro... and for the FUNCTION NAME use &quot;RunExcelMacro()&quot;

I hope this helps.

 

[DummyForAccess]

Gawd, this is soooo close to working. From what I can tell, this code works as a sub. I just need one thing, how do I write a function that calls the sub, I don't need it to do anything else, just to call the sub. (can you believe I had 2 vb classes and can't remember much, I haven't used it much, so I've lost it I guess...)

 

[DummyForAccess]

Actually, I finally got it. Had to create a function called runexcel() and then have the function call the sub. I couldn't think of the syntax, I was taht frustrated, but alas I guessed at the right syntax. It works beautifully. I'm actually getting some automation into my life. Life is good. Thanks for your help!!