I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

The following works for me (change parameters and include full path to your db:

Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "MyFullDBPathName.mdb"
oAccess.Run "MyTestMacro", "MyParameter"

HTH

"Souris" wrote:

I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

Thanks for the message,
It works from Excel side, but Access macro does not recongnize the parameter
from Excel.

The Access Macro execute the same parameter no matter what Excel sends to.

any ideas?

Thanks millions,

"XP" wrote:

The following works for me (change parameters and include full path to your db:

Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "MyFullDBPathName.mdb"
oAccess.Run "MyTestMacro", "MyParameter"

HTH

"Souris" wrote:

I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

Not really.

Try placing a test program in Access that displays an alternate message
based on the argument passed. For example:

Sub Test(argTest)
If argTest = "Hello" Then
Msgbox "Hello"
Else
Msgbox "Goodbye"
End if
End Sub

When making a call, change the contents of your parameter to either "Hello"
or "Goodbye" and see if the correct message is displayed. If not, then
perhaps your parameter isn't being loaded correctly before the call takes
place?

HTH


"Souris" wrote:

Thanks for the message,
It works from Excel side, but Access macro does not recongnize the parameter
from Excel.

The Access Macro execute the same parameter no matter what Excel sends to.

any ideas?

Thanks millions,

"XP" wrote:

The following works for me (change parameters and include full path to your db:

Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "MyFullDBPathName.mdb"
oAccess.Run "MyTestMacro", "MyParameter"

HTH

"Souris" wrote:

I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

which version MS Access do you use?

I use Access 2000.
Does Ms Access 2000 support this?

Thanks again,

"XP" wrote:

Not really.

Try placing a test program in Access that displays an alternate message
based on the argument passed. For example:

Sub Test(argTest)
If argTest = "Hello" Then
Msgbox "Hello"
Else
Msgbox "Goodbye"
End if
End Sub

When making a call, change the contents of your parameter to either "Hello"
or "Goodbye" and see if the correct message is displayed. If not, then
perhaps your parameter isn't being loaded correctly before the call takes
place?

HTH


"Souris" wrote:

Thanks for the message,
It works from Excel side, but Access macro does not recongnize the parameter
from Excel.

The Access Macro execute the same parameter no matter what Excel sends to.

any ideas?

Thanks millions,

"XP" wrote:

The following works for me (change parameters and include full path to your db:

Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "MyFullDBPathName.mdb"
oAccess.Run "MyTestMacro", "MyParameter"

HTH

"Souris" wrote:

I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

I use 2000 at work and XP at home.

This should also work on older versions.

Did you try the msgbox test? That should really work and also help pinpoint
where the issue is...

"Souris" wrote:

which version MS Access do you use?

I use Access 2000.
Does Ms Access 2000 support this?

Thanks again,

"XP" wrote:

Not really.

Try placing a test program in Access that displays an alternate message
based on the argument passed. For example:

Sub Test(argTest)
If argTest = "Hello" Then
Msgbox "Hello"
Else
Msgbox "Goodbye"
End if
End Sub

When making a call, change the contents of your parameter to either "Hello"
or "Goodbye" and see if the correct message is displayed. If not, then
perhaps your parameter isn't being loaded correctly before the call takes
place?

HTH


"Souris" wrote:

Thanks for the message,
It works from Excel side, but Access macro does not recongnize the parameter
from Excel.

The Access Macro execute the same parameter no matter what Excel sends to.

any ideas?

Thanks millions,

"XP" wrote:

The following works for me (change parameters and include full path to your db:

Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "MyFullDBPathName.mdb"
oAccess.Run "MyTestMacro", "MyParameter"

HTH

"Souris" wrote:

I need run an Access macro from Excel spreadsheet and pass a paramter from a
cell of the spreadsheet.

Is it possible to have a macro to accept paramter in Access?
If yes, can I pass parameter from excel to access macro?
If yes, any example to do so?

Your information is great appreciated,

I had similar issues with trying to run Project VBA from Excel. What
you need to do is make sure the correct References are selected in
your Excel VBA compiler. Open the VBA window, click Tools
=References and then check Microsoft Access 11.0 Object Library. The
two should be able to communicate after that. Also, if you need to
run this from different computers, or have others who don't know
anything about VBA, you could program the reference in in your macro.
Chip Pearson (http://www.cpearson.com/excel/MainPage.aspx) explained
how to do this in an email:

You can certainly add a reference using VBA code. To accomplish this,
two
things must be true. First, the object library being referenced must
exist
on the target machine and must be registered with Windows in the
Registry.
For example, you can safely set a reference via code to the VBA
Extensibility Library since you can be sure that the library will
exist on
the machine. However, if you are distributing a DLL or typelib that
you
created yourself, you must put the file in an appropriate location and
then
register that file with Windows, typically by using the RegSvr32.exe
program
(or RegAsm.exe for NET components).

Next, the code that adds the reference must execute before the VBA
RunTime
decides to recompile the code. If you distribute a workbook between
machines, VBA may determine that the code should be recompiled when
the
workbook is opened, prior to the execution of Auto_Open or
Workbook_Open.
(The most common reason for a recompilation at start up is that the
workbook
was written and last compiled on a different version of Excel and/or
Windows
than the versions on which it is being presently run.) Because the
compilation takes place before the code to add the reference is
executed,
you will get compiler errors (which cannot be ignored or trapped with
an On
Error statement). The error you will get is "User-defined type not
defined". For example, if you have a line of code like

Dim V As SomeObj

and SomeObj is defined in a DLL that is not referenced, the compiler
will
complain about the SomeObj data type before the file that defines
SomeObj is
added to the references list. It is sort of a chicken and the egg
situation
- the code can't run until the reference is added, but the reference
cannot
be added until the code runs.

If you know the exact file name to be referenced, you can add a
reference
using AddFromFile. For example, the following adds a reference to the
VBA
Extensibility library.

ThisWorkbook.VBProject.References.AddFromFile _
Filename:="C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"

However, the same DLL or typelib may reside in different locations on
different machines and operating system versions. In this case, you
can use
the GUID (Globally Unique Identifier) of the library. The GUID is
unique
to a specific typelib or DLL, and the GUID for that component will be
the
same on all machines. For example, the GUID for the Extensibility
library is
used only by that library and nothing else, and the GUID will be the
same on
every machine regardless of the operating system version and
regardless of
where in the machine that file resides.

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=0, Minor:=0

By using 0s for Major and Minor, you will reference the most recent
version.
When adding a reference via GUID, VBA will search the registry for the
specified GUID and take a few jumps around the registry to find the
file
associated with the GUID.

You can use AddFromFile or AddFromGUID in the Workbook_Open event to
set the
reference when the workbook is opened. Be sure to use an On Error
statement
to ignore the error is you are attempting to add a reference that
already
exists.

If necessary, you can use what is called "Late Binding" and declare
everything that exists in the library to be referenced As Object
rather than
As WhateverObject and use the CreateObject method to create an
instance of
the root object. For example, with "Early Binding" you would access
the
Scripting RunTime's FileSystemObject with code like:

Dim OneFolder As Scripting.Folder
Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject
Set OneFolder = FSO.GetFolder("C:\Temp")
Debug.Print OneFolder.Path

This code requires a reference to the "Microsoft Scripting RunTime
Library".
Instead of relying on the reference to the Scripting Runtime, you can
use
Late Binding as shown below.

Dim OneFolder As Object
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set OneFolder = FSO.GetFolder("C:\Temp")
Debug.Print OneFolder.Path

You'll take a performance hit with late binding, since code must
execute
behind the scenes to determine what the properties of and methods an
object
supports.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group 1998 - 2008
Pearson Software Consulting, LLC
The San Diego Project Group, LLC
www.cpearson.com


Hopefully that helps you out.

Regards,
Thedude

Thanks for the message,



" wrote:

I had similar issues with trying to run Project VBA from Excel. What
you need to do is make sure the correct References are selected in
your Excel VBA compiler. Open the VBA window, click Tools
=References and then check Microsoft Access 11.0 Object Library. The
two should be able to communicate after that. Also, if you need to
run this from different computers, or have others who don't know
anything about VBA, you could program the reference in in your macro.
Chip Pearson (http://www.cpearson.com/excel/MainPage.aspx) explained
how to do this in an email:

You can certainly add a reference using VBA code. To accomplish this,
two
things must be true. First, the object library being referenced must
exist
on the target machine and must be registered with Windows in the
Registry.
For example, you can safely set a reference via code to the VBA
Extensibility Library since you can be sure that the library will
exist on
the machine. However, if you are distributing a DLL or typelib that
you
created yourself, you must put the file in an appropriate location and
then
register that file with Windows, typically by using the RegSvr32.exe
program
(or RegAsm.exe for NET components).

Next, the code that adds the reference must execute before the VBA
RunTime
decides to recompile the code. If you distribute a workbook between
machines, VBA may determine that the code should be recompiled when
the
workbook is opened, prior to the execution of Auto_Open or
Workbook_Open.
(The most common reason for a recompilation at start up is that the
workbook
was written and last compiled on a different version of Excel and/or
Windows
than the versions on which it is being presently run.) Because the
compilation takes place before the code to add the reference is
executed,
you will get compiler errors (which cannot be ignored or trapped with
an On
Error statement). The error you will get is "User-defined type not
defined". For example, if you have a line of code like

Dim V As SomeObj

and SomeObj is defined in a DLL that is not referenced, the compiler
will
complain about the SomeObj data type before the file that defines
SomeObj is
added to the references list. It is sort of a chicken and the egg
situation
- the code can't run until the reference is added, but the reference
cannot
be added until the code runs.

If you know the exact file name to be referenced, you can add a
reference
using AddFromFile. For example, the following adds a reference to the
VBA
Extensibility library.

ThisWorkbook.VBProject.References.AddFromFile _
Filename:="C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"

However, the same DLL or typelib may reside in different locations on
different machines and operating system versions. In this case, you
can use
the GUID (Globally Unique Identifier) of the library. The GUID is
unique
to a specific typelib or DLL, and the GUID for that component will be
the
same on all machines. For example, the GUID for the Extensibility
library is
used only by that library and nothing else, and the GUID will be the
same on
every machine regardless of the operating system version and
regardless of
where in the machine that file resides.

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=0, Minor:=0

By using 0s for Major and Minor, you will reference the most recent
version.
When adding a reference via GUID, VBA will search the registry for the
specified GUID and take a few jumps around the registry to find the
file
associated with the GUID.

You can use AddFromFile or AddFromGUID in the Workbook_Open event to
set the
reference when the workbook is opened. Be sure to use an On Error
statement
to ignore the error is you are attempting to add a reference that
already
exists.

If necessary, you can use what is called "Late Binding" and declare
everything that exists in the library to be referenced As Object
rather than
As WhateverObject and use the CreateObject method to create an
instance of
the root object. For example, with "Early Binding" you would access
the
Scripting RunTime's FileSystemObject with code like:

Dim OneFolder As Scripting.Folder
Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject
Set OneFolder = FSO.GetFolder("C:\Temp")
Debug.Print OneFolder.Path

This code requires a reference to the "Microsoft Scripting RunTime
Library".
Instead of relying on the reference to the Scripting Runtime, you can
use
Late Binding as shown below.

Dim OneFolder As Object
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set OneFolder = FSO.GetFolder("C:\Temp")
Debug.Print OneFolder.Path

You'll take a performance hit with late binding, since code must
execute
behind the scenes to determine what the properties of and methods an
object
supports.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group 1998 - 2008
Pearson Software Consulting, LLC
The San Diego Project Group, LLC
www.cpearson.com


Hopefully that helps you out.

Regards,
Thedude