Using existing Access VBA with external VBScript 1

[SlakeB]

I am using VBScript for a number of windows scripting tasks (i.e. retrieving remote CSV files and reformatting them for entry into an Access DB). The DB I'm working with has a number of macros already set up to import data, sort it, put it in the correct tables, etc. As it stands, I have to come into Access and push the form buttons corresponding to these macros one by one. Is there a way for me to automate all of this by using an external .vbs file?

 

[RobSchultz]

This should give you an idea...

  Dim oWSH As WshShell
  Dim retVal As Long
  
  Set oWSH = New WshShell
  retVal = oWSH.Run("msaccess.exe c:\TestDB.mdb /x TestMacro", 1, True)
    
  Set oWSH = Nothing

Make sure your macro ends with a Quit command so the access process will close and control returns to your script. Use a 1 in the .Run method to show a normal active window or a 0 to hide it completely.

HtH,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[SlakeB]

Thanks that worked great. I had to make a couple small changes to not get errors in the vbscript:

instead of:
Dim oWSH As WshShell
Dim retVal As Long

Set oWSH = New WshShell


I used:
Dim oWSH
Dim retVal

Set oWSH = WScript.CreateObject("WScript.Shell")

 

[SlakeB]

Along the same lines: do you know how to run an Access sub procedure from an external vbscript file? I want a .vbs file to replicate all of the actions that currently take place when a button (sub procedure) is pushed on a form. For example the current sub runs two macros, then opens a query, then runs one more macro.

Is there a way to call the sub(and therefore everything in it) from .vbs?

 

[EBGreen]

The only way that I have been able to do this (from the command line) is to create a macro that calls the sub then call the macro from the command line as Rob already showed.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]