duhamp duhamp - 4 months ago 25
Bash Question

Shell command excel vba

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.

Answer

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
Comments