Automating macro to run macro from Access


C

Cam

Hello,

I have an Excel file with several sheets of data from source in Access
macro. What is the macro code to call the macro named "CreateData" in Access
macro, then run refresh all in Excel to refresh all worksheets?
The manual way I am doing right now is:
1) Open Access file, then run the macro named "CreateData"
2) Open Excel file, then run an Excel macro called "Refresh" to refresh all
worksheets.

Would like to perform all at once in Excel. Thank you
 
Ad

Advertisements

G

Gary Brown

'/=======================================/
' Sub Purpose: run MS Access macro or procedure
' from Access in Excel
' Reference to MS Access not required - late binding
'/=======================================/
'
Public Sub CallFromMSAccess()
Dim appAccess As Object
Dim strDatabaseLocation As String

On Error GoTo err_Sub

'- - - - V A R I A B L E S - - - - - -
strDatabaseLocation = "C:\Temp\db1.mdb"
'- - - - - - - - - - - - - - - - - - -

'open an instance of Microsoft Access and a database
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDatabaseLocation

'run an Access MACRO
appAccess.DoCmd.RunMacro "MyMacro"

'run an Access PROCEDURE
appAccess.Run "MyProcedure"

exit_Sub:
On Error Resume Next
'close Microsoft Access
appAccess.Quit
Set appAccess = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & ") - " & Now()
GoTo exit_Sub

End Sub
'/=======================================/
 
G

Gary Brown

Public Sub Calc_Workbook()
'calculate entire workbook - [F9]
Application.Calculate
' MsgBox "The entire workbook has been recalculated."
End Sub
 
Ad

Advertisements

C

Cam

Hello Gary,

Thanks for your help. A question I am not sure what is it in the code.

'run an Access PROCEDURE
appAccess.Run "MyProcedure"

I am not sure what procedure is it. Also your second response, what is it
function?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top