Executing customized batch file in Macro (Access 2007)

[CarolCook]

Hi:
I am using Access 2007 to accomplish (what I thought was) a simple 4 step task: give the user a linked list of values and prompt the user to select one. Use that value in a DOS batch file as part of a switch. Ask the user to confirm. Report completion of batch file.

In other works, user selects FOO from the combo box in step one. Step 2, user double clicks a button and the following text runs as a batch file:

cd folder for location of FOO2.exe
FOO2 <path name for template file> -add FOO

"Do you want to add FOO?"
yes
FOO has been added or no, operation has been cancelled.

Have tried using TEMPVARS but don't know how to name them and then append to text. Also, can't seem to string together multiple dos commands 1. cd 2. execute

Any help is appreciated.

 

[jrbarnett]

You will find this far easier to accomplish if you run your batch file (via the Shell command) through VBA rather than a macro.

I am assuming as you have posted in an Access forum that the batch file works correctly if you run it from the command line with appropriate parameters, it is just the Access interface to run it that doesn't work.

John

 

[CarolCook]

Hi John:
Thanks for responding! Yes, I am using VBA and yes, the batch file does work in and of itself. I have tried using the SHELL command. My issue is that the batch file has two separate commands and in addition, some of the paths included have spaces. So in the batch command, I have enclosed these in quotation marks. Nested quotation marks (one for the shell string and another embedded for the path) don't seem to work.

Also and as I said, not sure how to capture what the user selects in the combo box (complicated by the fact that what the user sees is a composite - name&" "&value and I only want to capture the bound column.) As you point out, probably not the best idea to do that with a macro and then second piece with VBA. Probably need to do both with VBA pass a parameter with a public routine.

Once that is done, I need to build a string out of it by appending it to some fixed text and then execute that with SHELL.

Carol

 

[jrbarnett]

To capture the bound column of a combo box, just access the combo boxes value:

eg

strSomeVariable = cboCombo.Value

I'd also think about doing your batch file bits through vba, something like - for the first part:

ChDir "<path to FOO2.EXE>"
Shell "FOO2 <path name for template file> -add " & cboCombo.Value

- this way it passes across the value of the input combobox.

for the second part:

If MsgBox ("Do you want to add " & cboCombo.Value & "?", vbQuestion+vbYesNo) = vbYes Then
 ' do something here to add FOO (cboCombo selected value)
 MsgBox cboCombo.Value & " has been added or no, operation has been cancelled.", vbOKOnly+vbInformation
End If

You might even be able to strip the batch file out altogether.

John

 

[CarolCook]

Hi John:
Here is my code which only returns "The VDM redirector is already loaded" and then I can't use Exit to get out of the DOS window, I need to cancel. I can't get this to execute in the VBA window either with F5. (n4 is the executable that has to be run from the command prompt, projectselect is the name of the combo box)
Thanks so much for your help
Carol


Private Sub command7_click()

Dim Pause As String
Pause = "command.com /p"

ChDir "C:\Program Files\beta master"

Shell "n4 project create -t 'C:\Program Files\beta master\test template.xlsx' -u -n " & ProjectSelect.Value

Shell Pause
End Sub

 

[CarolCook]

Hi John:
Got it working. VBA does not like embedded spaces which requre embedded quotes. Am progressing! Is there some way to make the completion message wait until the DOS batch file has actually completed?
Carol

 

[jrbarnett]

Easiest way is to turn your Shell line into a batch file in its own right, passing in variables to the batch file from your Shell command in Access with a pause command at the end to allow capture of the output of your n4 program.

You can then dispense with the "Shell Pause" line and also the ChDir line, since this can also be done from within the batch file.

John

 

[CarolCook]

John:
You have been more than helpful and I feel badly pestering you with more questions. Is there any way I could buy an hour or so of your time to go through this interractively via webex? I haven't working in Access VBA in about 2 years and am exceedingly rusty as you can tell.
Carol

 

[jrbarnett]

For the batch file:

Copy and paste the following into Notepad. Save the resulting file as runn4.bat, ensuring that Notepad doesn't add a ".txt" extension to the end:

@echo off
set FILEPATH=C:\Program Files\betamaster
CD %FILEPATH%
%FILEPATH%\n4 project create -t '%FILEPATH%\test template.xlsx' -u -n %1
pause
set FILEPATH=

Now replace
Shell "FOO2 <path name for template file> -add " & cboCombo.Value

with
Shell "runn4.bat " & cboCombo.Value

This is untested, it may work as is, I would suggest taking a backup of your Access database before trying this out. If it works, you will be able to remove the ChDir and Shell Pause lines from your Access VBA.

John