run msaccess macro from PS

Immortal_Creations

unread,

Mar 28, 2008, 8:28:00 AM3/28/08

to

I cannot for the life of me find any documentation on actually running a
macro in Microsoft Access from a powershell script. I am in the process of
re-writing some of my .bat scripts but i can't seem to get these to work. I
get about as far as opening the database up, but the /x macroname command
that use to work in cmd is no longer supported. Any suggestions?

Marco Shaw [MVP]

unread,

Mar 28, 2008, 8:43:44 AM3/28/08

to

Pretty much everything you do in cmd, should work somehow in PSH also.

Can't try it right now, but try passing this as "/x macroname" (with the
quotes) to the Access EXE.

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

Immortal_Creations

unread,

Mar 28, 2008, 8:55:01 AM3/28/08

to

This is the code i have right now
c:\powersource\masterupdate.mdb "/x gr8eqlzero"

like i said, it opens up Access, but doesn't run the specified macro.
If you know of any good sites that show what type of script i should be
writing this is, it would be helpful. I'm a bit confused about the language
needed to code these scripts, since microsoft says it's a new language, but
all the posts i see are writing it in VB.

Marco Shaw [MVP]

unread,

Mar 28, 2008, 9:00:55 AM3/28/08

to

Immortal_Creations wrote:
> This is the code i have right now
> c:\powersource\masterupdate.mdb "/x gr8eqlzero"
>
> like i said, it opens up Access, but doesn't run the specified macro.
> If you know of any good sites that show what type of script i should be
> writing this is, it would be helpful. I'm a bit confused about the language
> needed to code these scripts, since microsoft says it's a new language, but
> all the posts i see are writing it in VB.

Well, you can use PowerShell to call your original cmd script.

I think you should be trying something like:
PSH>SOME_PATH\msaccess.exe "c:\powersource\masterupdate.mdb" "/x gr8eqlzero"

Immortal_Creations

unread,

Mar 28, 2008, 9:35:00 AM3/28/08

to

"c:\program files\microsoft office\office11\msaccess.exe"
"c:\powersource\masterupdate.mdb" "/x gr8eqlzero" doesn't work. i get the
error "unexpected token 'c:\powersource\masterupdate.mdb is expressin or
statement. At Line:1 char:92
+ "C:\program files\microsoft office\office11\msaccess.exe"

"c:\powersource\masterupdate.mdb" <<<< "/x gr8eqlzero"

I don't want to call the original .bat file because i am working on
consolidating my scripts and programs that runs withing the old .bat files
into powershell.

My old .bat files open up my access file, run the macro, open up a software
program called "Postie" to email the results of the macro from a location and
exit out of the bat file.

That's what I'm trying to do with powershell.

Immortal_Creations

unread,

Mar 28, 2008, 9:53:04 AM3/28/08

to

Here is what the old batch file looked like
Why doesn't powershell accept these types of commands and where can i learn
more about the Powershell language?

"D:\Microsoft Office\Office\MSACCESS.EXE" d:\powersource\masterupdate /x
GR8EQLZERO
D:\postie\POSTIE.EXE -config -to:em...@address.com -bcc:em...@address.com
-nomsg -s:"GR8EQLZERO" -a:d:\query\GR8EQLZERO.CSV -v:2
>log:d:\query\GR8EQLZERO.txt
exit

AlanC

unread,

Mar 28, 2008, 10:24:54 AM3/28/08

to

> > PSH>SOME_PATH\msaccess.exe "c:\powersource\masterupdate.mdb" "/x gr8eqlzero"- Hide quoted text -
>
> - Show quoted text -

I am a bit of a beginner with PowerShell so this may not be the best
way to do it:-

--------------------------------------------------------------------------------------------
$accessApp = new-object -com access.application

$accessApp.Application.OpenCurrentDatabase("c:\powersource
\masterupdate.mdb")

$accessApp.Application.DoCmd.RunMacro("gr8eqlzero")

$accessApp.Application.CloseCurrentDatabase()
--------------------------------------------------------------------------------------------

Alan

Marco Shaw [MVP]

unread,

Mar 28, 2008, 10:36:12 AM3/28/08

to

Immortal_Creations wrote:
> Here is what the old batch file looked like
> Why doesn't powershell accept these types of commands and where can i learn
> more about the Powershell language?

This worked for me:
PS C:\Program Files\Microsoft Office\Office12> ./msaccess.exe
Database1.accdb /x macro1

There's a way, I'm sure. I'm just having problems with calling the
application and passing the arguments.

Marco

Marco Shaw [MVP]

unread,

Mar 28, 2008, 10:37:35 AM3/28/08

to

> --------------------------------------------------------------------------------------------
> $accessApp = new-object -com access.application
>
> $accessApp.Application.OpenCurrentDatabase("c:\powersource
> \masterupdate.mdb")
>
> $accessApp.Application.DoCmd.RunMacro("gr8eqlzero")
>
> $accessApp.Application.CloseCurrentDatabase()
> --------------------------------------------------------------------------------------------

Cool! Thanks for sharing.

Immortal_Creations

unread,

Mar 28, 2008, 11:18:01 AM3/28/08

to

AlanC - copied your script and now received the following error with the code
you provided.

Exception calling "RunMacro" with "1" argument(s): "The RunMacro action was
canceled." At C:\tst.ps1:5 char:38
+ $accessapp.application.DoCmd.RunAMacro< <<<< "gr8eqlzero")
Exception calling "CloseCurrentDatabase" with "0" argument(s): "The
expression you entered refers to an object that is closed or doesn't exist.
At C:\tst.ps1:7 char:44

Immortal_Creations

unread,

Mar 28, 2008, 11:22:01 AM3/28/08

to

I thought Powershell was a "new" programming language. It's obvious that
normal cmds we are all use to don't work the way they use to. What language
should I be researching here? everyone's giving replies using a different
language.

AlanC

unread,

Mar 28, 2008, 11:51:08 AM3/28/08

to

> > --------------------------------------------------------------------------------------------

> > $accessApp = new-object -com access.application
>
> > $accessApp.Application.OpenCurrentDatabase("c:\powersource
> > \masterupdate.mdb")
>
> > $accessApp.Application.DoCmd.RunMacro("gr8eqlzero")
>
> > $accessApp.Application.CloseCurrentDatabase()

> > --------------------------------------------------------------------------------------------
>
> > Alan- Hide quoted text -

>
> - Show quoted text -

It may depend on which version of Access you are using. I have 2002
installed on my PC.

PowerShell uses the .NET architecture.

Basically the script above creates a MS Access object ($accessApp).
It then uses standard VBA calls (Application.OpenCurrentDatabase etc)
to open the mdb file, run a macro and close the mdb file. These calls
may be different of you have an older/newer version of MS Access.

Alan

Immortal_Creations

unread,

Mar 28, 2008, 12:05:03 PM3/28/08

to

it's office 2003. but my macro uses the quit command after the transfer of
text so i'm not sure that the closecurrentdatabase would be necessary.

"AlanC" wrote:

> On Mar 28, 3:18 pm, Immortal_Creations <Jos...@bluestarusa.com> wrote:
> > AlanC - copied your script and now received the following error with the code
> > you provided.
> >
> > Exception calling "RunMacro" with "1" argument(s): "The RunMacro action was
> > canceled." At C:\tst.ps1:5 char:38
> > + $accessapp.application.DoCmd.RunAMacro< <<<< "gr8eqlzero")
> > Exception calling "CloseCurrentDatabase" with "0" argument(s): "The

> > expression you entered refers to an object that is closed or doesn't exist..

AlanC

unread,

Mar 28, 2008, 12:45:07 PM3/28/08

to

That might be the problem. The macro I wrote was just a simple one
that ran an SQL command to insert a record into a table. If you take
out the quit and try it again, what happens?

Alan

Marco Shaw [MVP]

unread,

Mar 28, 2008, 12:54:14 PM3/28/08

to

PowerShell is a new language, but may not have direct interfaces into
everything.

For example, it would be up to the MS Office team to add PowerShell
support to the Office Suite.

Adding PowerShell support to non-server products is likely not a current
priority.

Only the future will tell...

Marco

Immortal_Creations

unread,

Mar 28, 2008, 12:59:01 PM3/28/08

to

Looks like that worked alan. I'm sure I'll be back on here trying to get ftp
to work next. lol I already have the email with attachments working so i
can just copy and paste the code i already have written into the necessary
scripts. Thanks for your time. sure I'll be asking more questions

Marco Shaw [MVP]

unread,

Mar 28, 2008, 1:34:01 PM3/28/08

to

Immortal_Creations wrote:
> Looks like that worked alan. I'm sure I'll be back on here trying to get ftp
> to work next. lol I already have the email with attachments working so i
> can just copy and paste the code i already have written into the necessary
> scripts. Thanks for your time. sure I'll be asking more questions

FTP, you have a few options, for example:
1. http://www.nsoftware.com/powershell
2. http://scriptolog.blogspot.com/2007/09/ftp-transfers-automation.html
3. ftp.exe (from DOS)
4. Using the .NET Framework classes (difficult)

#1 would also help you with your email, and there are also other
solutions also for sending SMTP mail from PowerShell.

Marco

Shay Levi

unread,

Mar 28, 2008, 6:53:14 PM3/28/08

to

Dmitry Sotnikov posted this in the PowrGUI forum. It's the same issue only
with Excel.
Maybe you can use it with regard to Access:

# start Excel
$excel = New-Object -comobject Excel.Application

#open file
$FilePath = 'C:\temp\Book1.xlsm'
$workbook = $excel.Workbooks.Open($FilePath)

#make it visible (just to check what is happening)
$excel.Visible = $true

#access the Application object and run a macro
$app = $excel.Application
$app.Run("Macro1")

-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

>> ----------------------- $accessApp = new-object -com

Immortal_creations

unread,

Apr 7, 2008, 4:26:03 PM4/7/08

to

Seems as though the code works, but the code
$accessApp.Application.CloseCurrentDatabase() mearly closes the db and
not the msaccess process. What's the procedure for closing out the
msaccess after closecurrentdatabase()?

--
Immortal_creations