Results 1 to 5 of 5
  1. #1
    itm is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    69

    Question Trying to excute a batch file from VBA code

    Hi,
    I am using MS Access 2007 and I am trying, from my VBA code, to execute (or run) a Batch file. Now the batch foile works, but not from my code.
    I used to be able to just do this --> Call Shell("S:\Accounting\AP\Tex Files\File List Generator.cmd ", 1)
    ANd it would runit. But using this I get a message telling me that it is an invalid procedure call. So upon looking around on the net I found tha ton place said that with MS Access 2007 you need to do this:
    Call Shell(Environ$("COMSPEC") & " /c S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\File List Generator.cmd ", vbNormalFocus)
    But while it does not error out it dooes noting eitheer. What the batch file does, when it works, is produces a list of all the file names in a folder.


    Any ideas would be relly appreciated.
    Thank you
    itm

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your profile shows Access 2003. Have you switched? What OS are you using?

  3. #3
    itm is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    69

    Trying to execute a .Bat file

    Well I use everthing in my work from MS Access 97 to 2007, so that is probably why that said it. However, as I noted in this post, I ma using a MS Access 2007 on a Windows 7 machine (64 bit process).

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your issue may be Win7 rather than ac2007. Care to post a sample of your db so I can try it? I have the same setup.

  5. #5
    itm is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    69
    Quote Originally Posted by RuralGuy View Post
    Your issue may be Win7 rather than ac2007. Care to post a sample of your db so I can try it? I have the same setup.

    Here is what my code looks like now withthe different things I tryed.
    Code:
    Private Sub cmdProcess_Click()
    Dim fs As Variant
    Dim a As String
    Dim b As String
    Dim RetVal
    Dim strCmd As String
    On Error GoTo ErrorHandler:
    DoCmd.RunSQL "Delete * from Hld"
    If MsgBox("Is this the location and name of the folder you want ?" & vbCrLf & "S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\", vbYesNo + vbInformation, "Cutoff Date Check") = vbYes Then
         
         Call Shell("S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\File List Generator.bat", vbNormalFocus)
         'Call Shell(Environ$("COMSPEC") & " /k S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\File List Generator.bat", vbNormalFocus)
         'Shell "cmd /k" & "S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\File List Generator.bat", vbNormalFocus
        
         DoEvents
         'Runs the application and creates the list of files names
    Else
        a = BrowseFolder("Please select the folder you want to generate a list from") 'Gets the file path from the user
        
        a = a & "\"
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.copyfile "S:\Accounting\DB\development\File List Generator.bat", a
        Set fs = Nothing
        
        a = a & "File List Generator.bat"
        
        Call Shell(a, 1) 'Runs the application and creates the list of files names
        'Note: this list will be brought into the database and it has a .pdf extension and a header which will have to be addressed.
           
    End If
      b = "S:\Accounting\DB\development\hld.txt"
      DoCmd.TransferText acImportDelim, "Hld Import Specification", "Hld", b
      
    Exit Sub
      
    ErrorHandler:
        ''' If we ran into any errors this will explain what they are.
        MsgBox Err.Description, vbCritical, "Shell Error"
     
    End Sub
    I also tryed it on an XP machine and got the same thing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-20-2011, 01:04 AM
  2. VBA code for export to pdf file
    By Milan25 in forum Programming
    Replies: 4
    Last Post: 02-01-2011, 12:37 PM
  3. Run batch file
    By shay in forum Access
    Replies: 3
    Last Post: 12-15-2010, 09:47 AM
  4. How do you file save an 'Attachment' content through code?
    By morespamforya in forum Programming
    Replies: 3
    Last Post: 08-06-2010, 08:58 AM
  5. Running Batch File
    By NoiCe in forum Programming
    Replies: 2
    Last Post: 03-21-2010, 07:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums