Automating macro to run macro from Access
 

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

Automating macro to run macro from Access
 

'/=======================================/
' 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
'/=======================================/


--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Cam" wrote:

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

Automating macro to run macro from Access
 

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

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Cam" wrote:

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

Automating macro to run macro from Access
 

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?


"Gary Brown" wrote:

'/=======================================/
' 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
'/=======================================/


--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Cam" wrote:

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