Shell command excel vba

Question

I'm having some problems executing the following in Excel VBA. The goal is to run an .sql file - the issue is with the Execute Shell sub I think.

I run:

Sub RunFile()
    Call ExecuteShell("C:\","LAPBTN1749","filename.sql")
End Sub

Sub ExecuteShell(path As String, hostname As String, file As String)
    Dim retval
    retval = Shell("SQLCMD -E -S " & hostname & "\SQLEXPRESS -i " & path & file, vbMinimizedFocus)
End Sub

It doesn't run, probably due to the quotes. If it is the quote, can someone explain how they work or tell me where I can find out because I've never properly understood this.

Just that - it didn't appear to do anything. Adding the quotes in helped though as I had expected - many thanks. — ruskind
– ruskind, Commented Jul 31, 2015 at 8:20

J. Chomel · Accepted Answer · 2016-07-25 15:15:27Z

1

I agree with @TimWilliams. I prefer to append Chr$(34) to a string because it means I don't have to count the number of quotes that I'm using. The code looks like:

Sub RunFile()
    Call ExecuteShell("C:\", "LAPBTN1749", "filename.sql")
End Sub

Sub ExecuteShell(path As String, hostname As String, file As String)
    Dim retval
    retval = Shell("SQLCMD -E -S " & Chr$(34) & hostname & "\SQLEXPRESS" & Chr$(34) & " -i " _
        & Chr$(34) & path & file & Chr$(34), vbMinimizedFocus)
End Sub

edited Jul 25, 2016 at 15:15

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

answered Jul 30, 2015 at 19:26

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bond · Accepted Answer · 2015-07-30 18:35:45Z

1

If any of the passed parameters contain spaces then likely they need to be quoted in the call to Shell. Quotes in VBA are escaped by doubling them up:

Sub RunFile()
    Call ExecuteShell("C:\","LAPBTN1749","filename.sql")
End Sub

Sub ExecuteShell(path As String, hostname As String, file As String)
    Dim retval
    retval = Shell("SQLCMD -E -S """ & hostname & "\SQLEXPRESS"" -i """ & _
                   path & file & """", vbMinimizedFocus)
End Sub

If you're still having problems then try Debug.Printing the first Shell argument and running it "manually" at the command prompt.

edited Jul 30, 2015 at 18:35

Bond

16.3k6 gold badges34 silver badges56 bronze badges

answered Jul 30, 2015 at 17:19

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Shell command excel vba

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related