VBA Shell to batch file -- adding command line variables

[BlackieHamel]

I am able to run a Shell command to run a batch file from within a VBA macro, but I'm failing when I attempt to add a command-line argument. Can you tell me what is wrong with this?

Sub MassageData()

  Dim PathCrnt As String
  Dim sString As String

    sString = InputBox("Enter Search String")
    PathCrnt = ActiveWorkbook.Path
    Call Shell(PathCrnt & "\TCwithvar.bat" & sString & PathCrnt)
  
  MsgBox ("Results copied to Clipboard!")
  

End Sub

Thanks.

Blackie

 

[mjbeam]

Try:

Call Shell(PathCrnt & "\TCwithvar.bat " & PathCrnt & "\" & sString)

 

[BlackieHamel]

Thanks for that syntax correction. Unfortunately, it still doesn't work, because I guess I have two problems.

I found the "call shell" command here and repeated it without understanding why the second PathCrnt was there, so from your message I guess I misplaced it.

But it still doesn't work, and the execution of the batch file (which works at the cmd prompt) is not visible when the macro runs. Is there a way I can see what's happening at the cmd level and perhaps see any error messages?

 

[mjbeam]

I would pause execution at the line that shells to the batch file. Then open the Immediate window in the Visual Basic Editor and in that window, type this (and press enter):

Print PathCrnt & "\TCwithvar.bat " & PathCrnt & "\" & sString

...and see if the result looks correct. If it does, you should be able to copy and paste it into a Command window and it should work. If not, then we need to fix the format in the Shell command in the code.

 

[BlackieHamel]

Thanks. I didn't know about the Immediate window (I'm very new at this), so I appreciate the tip!

And it did seem to reveal the problem. My sString variables need to include periods (an example would be "...ABC..*" without the quotes), and when viewed in the Immediate window, the periods have been passed through as commas. I presume that it's using a different encoding. Any way to overcome that?

Blackie

 

[mjbeam]

I ran your code and when I enter "...ABC..*" and print sString in the immediate window they are not converted to commas. I have no idea why that would happen on your system.

 

[BlackieHamel]

Hmm. Problem Exists Between Keyboard And Chair. I guess I typed those commas -- hey, the key is right next to the period -- because it didn't happen a second time. Thanks for all your quick responses.

My batch file still doesn't work, though, and it does work both at the command line and through VBA without arguments. The last part of the batch file (not the command-line argument) is a "|clip" to copy results to the clipboard. Is there a way I can handle an error by reporting that the clipboard is empty?

 

[mjbeam]

This link will show you how to access the clipboard in VBA:

How to read or access the clipboard with Excel VBA? | Excel Help HQ - Helping you Solve Excel Problems

Plus, you could probably search the forums here and find what you need.