Shell not opening my .bat file

[Andyrue]

Access 2003 Problem

Ok, this is my first time tinkering with VBA. What I have is a method that does some stuff and then when it is done, I want it to run a .bat file that simply runs a python program. I spent hours trying to get the Shell command to run it, but it would just keep flashing the command prompt. At one point when I was using some combination of Call Shell I got the command prompt to open and stay open, but it wasn't anywhere near the path I type in. To make sure I was doing things right, I had it open notepad.exe. Worked like a charm. What am I supposed to do? I've tried

call Shell ( Environ$ ( "COMSPEC" ) & " /c c:\directory\test.bat", vbNormalFocus)

and also the basic Shell ("c:\directory\test.bat",vbNormalFocus)

My access db is in C:\directory
My .bat file is in C:\directory
My python program is in C:\directory

from what I read, paths shouldn't even be a problem since all my stuff is in the same directory. I have tried both absolute paths and relative, both just flash the cmd prompt. Any advice is greatly appreciated. Thanks

 

[Bodisathva]

if the batch file is in the same directory as the Access db, that puts it in the same path and the C:\DirectoryName is not needed, try using just Shell "test.bat", vbNormalFocus

(You can also omit the parens if you're not going to bother with the returned value)

 

[Andyrue]

Yes, I've tried just test.bat and just /test.bat, and any possible combination I could think of. It just opens the command prompt with the current path being "C:\Documents & Settings/Andrew" it's not making any sense.

 

[DJkarl]

Yes, I've tried just test.bat and just /test.bat, and any possible combination I could think of. It just opens the command prompt with the current path being "C:\Documents & Settings/Andrew" it's not making any sense.

Does the batch file run when you open it outside of Access? My guess based on the comment above is that the bat file is attempting to change directories or open something in another path that has spaces in it, if the path is not enclosed in double quotes it may not work.

 

[Andyrue]

Yes, the batch file runs perfect if I execute it outside of access. It has only one command. runImport.py no paths or anything since the file is in the same folder as the batch file.

 

[boblarson]

I've run into this problem before as well, and while I can't tell you the WHY about what I had to do, I can tell you what I did to make it work.

Changing .bat to .cmd worked for me in my situation and I can't tell you why that was, and logically it shouldn't have mattered, but for some unknown reason it did. So, give that a shot and see. You got nothing to lose...

 

[Andyrue]

I've run into this problem before as well, and while I can't tell you the WHY about what I had to do, I can tell you what I did to make it work.

Changing .bat to .cmd worked for me in my situation and I can't tell you why that was, and logically it shouldn't have mattered, but for some unknown reason it did. So, give that a shot and see. You got nothing to lose...

ok, just tried this. Same result. I'm noticing when I don't put the full path name though I get a "File not found" error in access. I've double and triple checked the file name and I am absolutely sure that it is spelled right and in the same directory as everything else. Why might it do this? Thanks for all the help

 

[Guus2005]

Using the "dir" statement try to determine from which directory your command is started. Try to do

if len(dir(strPath))>0 then 'found
   runcommand 'Using shell or whatever.
endif

or i.s.o. a relative path, try to use the UNC path "//server/user/..."

HTH

 

[Andyrue]

Using the "dir" statement try to determine from which directory your command is started. Try to do

if len(dir(strPath))>0 then 'found
   runcommand 'Using shell or whatever.
endif

or i.s.o. a relative path, try to use the UNC path "//server/user/..."

HTH

I tried replacing "runcommand" with my shell call and I get a "type mismatch" error.

 

[Guus2005]

where do you get this error? On which line?

 

[Andyrue]

where do you get this error? On which line?

I don't know. I have a button that runs this function and it just pops up an alert box saying "Type mismatch". I'm going to guess it errors on the "if len(dir(strPath))>0 " line because if I change strPath to "C:\directory" it doesn't error


This is what I have that says Type Mismatch.
If Len(Dir(strpath)) > 0 Then
Shell "import.bat", vbNormalFocus

End If


This says "File not found" - that's the shell command
If Len(Dir("c:\directory")) > 0 Then 'found
Shell "import.bat", vbNormalFocus 'Using shell or whatever.

End If

 

[Guus2005]

when you get an error, press control-break.
The debug window opens showing you where the error occures.

Did you compile the database?

 

[Andyrue]

Ok, I didn't write the code, I'm only trying to modify it. This is the line it errors on...has nothing to do with what I've done.

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click <--------Highlighted Yellow

End Sub

 

[WayneRyan]

Andyrue,

I can't tell from this if your directory spec has embedded spaces.

If your target directory has embedded spaces, you have to do something
a little different:

Shell ("""c:\directory\test.bat""",vbNormalFocus)

Not quite sure of the syntax, I encountered this a few years ago when
putting together VB scripts to launch apps.

I currently shell a few .bat files, but none of the paths have spaces. It
must have something to do with your problem.

hth,
Wayne

 

[Andyrue]

The name of the folder is actually "directory" the real full path is "C:\directory\import.bat" It was different, but for simplicity sake I moved everything so there would be no spaces. I had the shell print out a dir listing to a file and it listed everything in My Documents. It doesn't make sense why it would look there since it's in C:\directory\import.bat.

This is the whole sub

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

If MsgBox("Loading data will overwrite any existing information. Continue?", vbYesNo, "Import Tables") = vbYes Then
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "DELETE * FROM Officer"
cmd.Execute
cmd.CommandText = "DELETE * FROM Organization"
cmd.Execute
cmd.CommandText = "DELETE * FROM OfficeTitle"
cmd.Execute
cmd.CommandText = "DELETE * FROM [Committee]"
cmd.Execute
cmd.CommandText = "DELETE * FROM CommitteeMember"
cmd.Execute
cmd.CommandText = "DELETE * FROM Child"
cmd.Execute
cmd.CommandText = "DELETE * FROM Pastor"
cmd.Execute


Set cmd = Nothing

DoCmd.TransferText acImportDelim, , "OfficeTitle", CurrentProject.Path + "\officetitle.txt", True
DoCmd.TransferText acImportDelim, "Organization Import Specification", "Organization", CurrentProject.Path + "\organization.txt", True
DoCmd.TransferText acImportDelim, , "Officer", CurrentProject.Path + "\officer.txt", True
DoCmd.TransferText acImportDelim, , "Committee", CurrentProject.Path + "\committee.txt", True
DoCmd.TransferText acImportDelim, , "CommitteeMember", CurrentProject.Path + "\committeemember.txt", True
DoCmd.TransferText acImportDelim, , "Child", CurrentProject.Path + "\child.txt", True
DoCmd.TransferText acImportDelim, , "Pastor", CurrentProject.Path + "\pastor.txt", True

'Shell Environ$("COMSPEC") & " /c dir > c:\dir.txt"
End If
If Len(Dir(strpath)) > 0 Then
Shell "import.bat", vbNormalFocus

End If
'Call Shell(Environ$("COMSPEC") & " import.cmd", vbNormalFocus)
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


It imports the data from the text files into the database, after that I want to run my script that modifies the text files. It is written in python so I have to load it from a batch file. It all runs fine when executed from a command prompt. The batch file simply says "dirImport.py" and all it does is parse through and delete all double quote marks, then import the new text files to my MySQL database.

 

[WayneRyan]

Andyrue,

Does your code work if you single-step through it with the debugger?

What is strPath?
Why don't you have a path pointing to "import.bat"?

It doesn't look like the Shell ever gets executed.

If Len(Dir(strpath)) > 0 Then
  Shell "import.bat", vbNormalFocus
End If

Wayne

 

[Andyrue]

Andyrue,

Does your code work if you single-step through it with the debugger?

What is strPath?
Why don't you have a path pointing to "import.bat"?

It doesn't look like the Shell ever gets executed.

If Len(Dir(strpath)) > 0 Then
  Shell "import.bat", vbNormalFocus
End If

Wayne

Taking the shell call out of the if statement and just trying to execute it with:

Shell "c:\directory\import.bat", vbNormalFocus

doesn't work. It flashes the command prompt and doesn't work. If I make the command prompt stay open, it opens with the current working directory set to My Documents. The access database and all other related files are in C:\directory\

it is a member directory, which is why the folder is named directory

 

[Guus2005]

Ok, I didn't write the code, I'm only trying to modify it. This is the line it errors on...has nothing to do with what I've done.

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click       <--------Highlighted Yellow
[COLOR="Red"]Resume[/COLOR]
End Sub

continuing to debug:
Put the red text as described above, press ctrl-f9. The Yellow line moves one line down. Now press F8. The line it jumpes to is where your error occures.
Which one is that?

 

[boblarson]

What is likely happening is this:

If it stops here: Resume Exit_Command0_Click it either can't find it which if you are using the error handler the way Microsoft does it (I don't do it as I think it's extra steps for nothing) it would look like this:

Exit_Command0_Click:
   Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click   
End Sub

 

[Andyrue]

Fixed

I got it working. The problem was I had to use absolute paths everywhere, even within my batch file and within my python program. It doesn't make alot of sense since everything is in the same folder, but it's working now. There was never anything wrong with my VBA code, minus the fact I had to use absolute paths there as well. Thanks for everyones help