Run Access Macro from within Excel
I am new to macro programming. It is possible to run an Access Macro from an
Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
The easiest way is to set a reference in your Excel project to the Access
object library. (Tools - References, and scroll down to find "Microsoft Access XX.0 Object Library," and check the box.) This will allow you to get all the Intellisense features for Access. So, in Excel Sub RunAccessMacro() Dim appAcc as Access.Application 'Opens Access or gets reference to app already running Set appAcc = New Access.Application 'Optional to show or hide Access appAcc.Visible = True appAcc.Open "C:\Database with the Macro.mdb" 'For running a regular Access macro appAcc.DoCmd.RunMacro "MyAccessMacro" 'For running a VBA macro appAcc.RunMacro "MyAccessMacro" 'MORE OF YOUR CODE 'Close Access appAcc.Quit 'This will close Access, even w/o the 'Quit' command Set appAcc = Nothing End Sub "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel-Addendum
If you don't set a reference to Access in your Excel project, you can also
create one using CreateObject or GetObject, but those are a bit more complicated. "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
yes..
here is an app I use frequently After updating and reviewing a monthly data sheet, when the user exits the data is autoamtivcally moved into an access table and access does some stuff befire returning here to close Excel Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim accFil As Object DR_Name = Sheets("Audit").Cells(8, "I").Value 'Cell(I8) of the template gets the Excel file name to be imported during processing 'So if we have a name here then we need to import otherwise we're not ready to import so exit If Len(Trim(DR_Name)) <= 1 Then Exit Sub Set accFil = CreateObject("Access.application") With accFil.Application .Visible = True .Application.OpenCurrentDatabase ("FCSRecon.mdb") .Run ("Monthly_Import") 'After importing Acces will return here to close Excel + But Access will remain open End With 'Close Excel Application.Quit End Sub Please rate this post below thanx bc "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
Thanks for your response. However, there is not a "References" options in the
Tools in my version of Excel (2003). If there another way to Access? "Eric White" wrote: The easiest way is to set a reference in your Excel project to the Access object library. (Tools - References, and scroll down to find "Microsoft Access XX.0 Object Library," and check the box.) This will allow you to get all the Intellisense features for Access. So, in Excel Sub RunAccessMacro() Dim appAcc as Access.Application 'Opens Access or gets reference to app already running Set appAcc = New Access.Application 'Optional to show or hide Access appAcc.Visible = True appAcc.Open "C:\Database with the Macro.mdb" 'For running a regular Access macro appAcc.DoCmd.RunMacro "MyAccessMacro" 'For running a VBA macro appAcc.RunMacro "MyAccessMacro" 'MORE OF YOUR CODE 'Close Access appAcc.Quit 'This will close Access, even w/o the 'Quit' command Set appAcc = Nothing End Sub "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
I found it in the VB program. I thought you meant the Excel menu.
Thanks "Eric White" wrote: The easiest way is to set a reference in your Excel project to the Access object library. (Tools - References, and scroll down to find "Microsoft Access XX.0 Object Library," and check the box.) This will allow you to get all the Intellisense features for Access. So, in Excel Sub RunAccessMacro() Dim appAcc as Access.Application 'Opens Access or gets reference to app already running Set appAcc = New Access.Application 'Optional to show or hide Access appAcc.Visible = True appAcc.Open "C:\Database with the Macro.mdb" 'For running a regular Access macro appAcc.DoCmd.RunMacro "MyAccessMacro" 'For running a VBA macro appAcc.RunMacro "MyAccessMacro" 'MORE OF YOUR CODE 'Close Access appAcc.Quit 'This will close Access, even w/o the 'Quit' command Set appAcc = Nothing End Sub "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
Is there a way to use this code if the person has the object Library, but not
Access itself? "Eric White" wrote: The easiest way is to set a reference in your Excel project to the Access object library. (Tools - References, and scroll down to find "Microsoft Access XX.0 Object Library," and check the box.) This will allow you to get all the Intellisense features for Access. So, in Excel Sub RunAccessMacro() Dim appAcc as Access.Application 'Opens Access or gets reference to app already running Set appAcc = New Access.Application 'Optional to show or hide Access appAcc.Visible = True appAcc.Open "C:\Database with the Macro.mdb" 'For running a regular Access macro appAcc.DoCmd.RunMacro "MyAccessMacro" 'For running a VBA macro appAcc.RunMacro "MyAccessMacro" 'MORE OF YOUR CODE 'Close Access appAcc.Quit 'This will close Access, even w/o the 'Quit' command Set appAcc = Nothing End Sub "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
Run Access Macro from within Excel
No. You are using the object model to write the code that will open Access
itself and run an Access macro within Access. Gotta have the Access engine to do that. Same thing if you wanted to run an Access report: you'd need the Access engine. The object model contains no app-like capabilities, just simply provides you the means to write code that will manipulate the app. Of course, data can be retrieved without having Access itself using the Access, DAO, ADO, etc. object models. Queries or other SQL can be retrieved and *possibly* even run without having Access open. One gotcha would be if any Access-specific or user-defined functions are used in queries, you'd probably need to run the queries in Access or be prepared to make substitutions. An example of this is NZ() (Null to zero). That's an Access function, not a VB function, so if VB (or Jet) encounters it without Access open to provide interpretation, you're going to get an "undefined function" error. HTH, "Amery" wrote in message ... Is there a way to use this code if the person has the object Library, but not Access itself? "Eric White" wrote: The easiest way is to set a reference in your Excel project to the Access object library. (Tools - References, and scroll down to find "Microsoft Access XX.0 Object Library," and check the box.) This will allow you to get all the Intellisense features for Access. So, in Excel Sub RunAccessMacro() Dim appAcc as Access.Application 'Opens Access or gets reference to app already running Set appAcc = New Access.Application 'Optional to show or hide Access appAcc.Visible = True appAcc.Open "C:\Database with the Macro.mdb" 'For running a regular Access macro appAcc.DoCmd.RunMacro "MyAccessMacro" 'For running a VBA macro appAcc.RunMacro "MyAccessMacro" 'MORE OF YOUR CODE 'Close Access appAcc.Quit 'This will close Access, even w/o the 'Quit' command Set appAcc = Nothing End Sub "Thebeej" wrote: I am new to macro programming. It is possible to run an Access Macro from an Excel spreadsheet? Can you open Access, run the macro, close Access from the spreadsheet? |
All times are GMT +1. The time now is 03:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com