3

I have test.bat file contains script:

copy host_name table_name -p table_name -t "file.csv"

Normally when I click on it, it's working fine. Now, I want to run test.bat file from Excel using vba.

strPath = ws1.Range("G2").value & "\" 'Directory to folder with bat
Shell strPath & "\test.bat", vbNormalFocus

something is wrong, because I see only snapshot/clip: like something is open and close into one sec...

Community
  • 1
  • 1
4est
  • 3,010
  • 6
  • 41
  • 63
  • Sometimes it can be hard to read the output from a batch file, as they can close very quickly. You could add the [pause](https://technet.microsoft.com/en-gb/library/bb490965.aspx) command to the end of your script. This will prevent the window from closing until you've pressed a key. Or you could redirect the [output to a log file](http://stackoverflow.com/questions/20484151/redirecting-output-from-within-batch-file). In either case this should allow you capture any error messages returned by your batch. – David Rushton Jun 20 '16 at 10:16
  • 2
    Do you have 2 backslashes once the string is built? – ThunderFrame Jun 20 '16 at 10:17
  • yes, backslashes is into strPath – 4est Jun 20 '16 at 10:18

2 Answers2

7

just resolved it:

ChDir ThisWorkbook.Path & "\folder\"
Shell ThisWorkbook.Path & "\folder\test.bat", vbNormalFocus
4est
  • 3,010
  • 6
  • 41
  • 63
0

im not sure with VBA and shell you using, but try something like this

Dim winShell As Object: Set winShell = CreateObject("WScript.Shell")

    Dim WaitOnReturn As Boolean: WaitOnReturn = False
    Dim windowStyle As Integer: windowStyle = 1

    'Run the desired pair of shell commands in command prompt.
    Call winShell.Run("cmd /k " & command1 & " & " & command2, windowStyle, WaitOnReturn)

    'Release pointer to the command prompt.
    Set winShell = Nothing

just replace my commands with your command(s) and lets see if its works

edit: just for completion my commands looks like this

Dim command1 As String: command1 = "cd /d" & projectDirectoryCell.Value

Dim command2 As String: command2 = "create_sensi.bat"
Luboš Suk
  • 1,526
  • 14
  • 38
  • I tried to do command1 = "..cd" , but shell is always display: C:\users\my_user\documents> how to change root catalog? – 4est Jun 20 '16 at 12:01
  • @4est just as you do in standart cmd line something like `cd /d "C:\Program Files"` for example – Luboš Suk Jun 20 '16 at 12:09