Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with automating macro | Excel Programming | |||
Automating Input during a macro.... | Excel Programming | |||
New to VBA..need help with automating GETSAVEAS Macro in Excel 2003 | Excel Programming | |||
automating macro | Excel Programming | |||
Frequently used macro-automating? | Excel Programming |