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?

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?

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?

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?

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?

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?

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?

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?