Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run macro via /x

Posted on 2009-04-02
11
Medium Priority
?
724 Views
Last Modified: 2012-05-06
Hi,

I'm using access 2003.

I'm using the following code to open my database and after that I'll try to automatically run a macro called test;

Dim WshShell, oExec
Set WshShell = CreateObject("WScript.Shell")
Dim sCmd
sCmd = Chr(34) & "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\Users\paul tikken\WordMerge2008.mdb" & Chr(34) & _
" /wrkgrp " & Chr(34) & "C:\Users\paul tikken\Beveiliging.mdw" & Chr(34) & " /user administrator /pwd ******* /X test"
Set oExec = WshShell.Exec(sCmd)

Access will open as it should but when it comes to the macro access tells me that it cannot find the test macro. I've checked the spelling several times, seems to match.

What is the problem?
0
Comment
Question by:_Knocks_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 58
ID: 24053271
Looks fine here.  I don't believe it is case sensitive, but as a test, try something simpler like calling the macro '1'.
JimD.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 24053289
Nothing stands out - but you do have some extra characters in your cmd...use this instead...

You might also try renaming the macro.
Or could you place it in an autoexec macro?
sCmd = Chr(34) & "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE " & Chr(34) & "C:\Users\paul tikken\WordMerge2008.mdb" & Chr(34) & _
" /wrkgrp " & Chr(34) & "C:\Users\paul tikken\Beveiliging.mdw" & Chr(34) & " /user administrator /pwd ******* /X test"

Open in new window

0
 

Author Comment

by:_Knocks_
ID: 24053432
Jdettman; I tried to rename the macro to 1, it wouldn't take it (integer only) so I renamed it m1, still the same problem; access tells me it cannot find the macro. Do I need to refer to the module name as well? I can't find anything the documentation.

Sirbounty; those additonal charaters where neccessay because my path's have spaces in them, I tried your code, it didn't work at all.
Unfortunally I can't place it in a autoexec macro, because the macro I woul like to run is connected to the task scheduller and I only want the macro to run when tasked by the task scheduller, otherwise every end user who opens the database will trigger the macro, right?

cheers,

Paul
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 67

Expert Comment

by:sirbounty
ID: 24053468
Hmm - Jim would know better on that - not sure if you can limit it by username or not.
I would suspect so - grab the %username% environment variable - if it's SYSTEM, then launch the code, if not skip it...

I noticed the spaces in the paths, but apparently misread something somewhere...thought there was an extra space or two.
0
 

Author Comment

by:_Knocks_
ID: 24053541
Good suggestion sirbounty, I'll go and work on that!
0
 
LVL 58
ID: 24054055
<<Jdettman; I tried to rename the macro to 1, it wouldn't take it (integer only) so I renamed it m1, still the same problem; access tells me it cannot find the macro. Do I need to refer to the module name as well? I can't find anything the documentation.>>
No, no need for anything else, but I started thinking what you might mean by "module".
m1 should be the name of the macro in the database container. Access will not be able to find it if you used 'm1' in the macro name column inside of a marco. Is that what you meant by "module'?
JimD.

CorrectWay.jpg
Wrongway.jpg
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24054057
Try this ...

Dim RetVal as double
Dim sCmd
sCmd = Chr(34) & "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "C:\Users\paul tikken\WordMerge2008.mdb" & Chr(34) & _
" /wrkgrp " & Chr(34) & "C:\Users\paul tikken\Beveiliging.mdw" & Chr(34) & " /user administrator /pwd ******* /X test"

RetVal = Shell(sCMD, vbMaximizedFocus)


ET
0
 
LVL 58
ID: 24054076
Actually I just noticed I switched that around (1m instead of m1), but you get the idea.
JimD.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24054297
Knocks ...

I tried this is my test db and it worked.  Notice, I'm not inserting the Chr(34).

Function TestOpenWithMacro()
    Dim RetVal As Double
    Dim sCmd
    sCmd = "C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" & " " & "C:\MyFolder\MyDB.mdb" & _
    " /wrkgrp " & "C:\MyFolder\MySecured.mdw" & " /user ets /pwd password /X test"

    RetVal = Shell(sCmd, vbMaximizedFocus)


End Function

Hope this helps ...

ET
0
 

Author Comment

by:_Knocks_
ID: 24057128
I think I got my head around the problem, I was trying to run a macro written in VBA instead of in the macro section of the db, and I was trying to run that VBA macro. Now I've made a macro in the db and it works great!

Cheers
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 24059717
<< I was trying to run a macro written in VBA instead >>
  Ah.  Well first, just to be clear on the terms, in VBA you write procedures, which are either subs (don't return a value) or function calls (return a value).
  and you can (and should) use VBA procedures whenever possible instead of a macro (marco's have no error trapping).
  You can do this by creating a macro with a single RunCode action and then name your VBA function.  Then from the command line call that macro with the /x switch.  Note that the VBA procedure must be a function when called from a macro.  It cannot be a sub.
JimD.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question