SQL statement in .sql file through VBA

  • I'm trying to run a SQL statement through VBA code. I don't want to put the string in the VBA code itself as the sql statement is complex (difficult if you want to amend the SQL statement later in VBA).


    I based myself on existing code and I believe I'm almost there. However, I get the error message: "Command text was not set for the command object." I googled the error message but no solution found.


    Here is the code I'm trying to run. The code bugs on the last line.


  • Re: SQL statement in .sql file through VBA


    Why not just put the SQL statement in a hidden tab, one line per cell, and go get a range concatenation function and concatenate the range and put it into a string variable before you pass it with cnn.execute sqlstatement?


    Range concatenation functions are easy to find online. There is probably one on this formula.


    Put the query in the spreadsheet and then either
    1) concatenate it in your macro and put it into strSQL (that's what I am calling your SQL statement in VB)
    2) put your query starting in A2 in a sheet and then in the top cell do your concatenate range function. then call that cell with the concatenated query.


    Simple as pie :)

  • Re: SQL statement in .sql file through VBA


    Thanks but it's not the cleanest (copy-pasting every line, and what if you significantly change the query afterwards?). There must be a way to read a text file, no?

  • Re: SQL statement in .sql file through VBA


    I think you misunderstood my answer. You can put your query in a spreadsheet just like a text file. You can have as large of SQL statement as you'd like on each line and the concatenation of those lines at the top of the spreadsheet in A1. You can set the format of your spreadsheet (excluding the concatenation line) to text and edit it just like a text file. Since you can fit 65536 lines or 100k+ (if you have Excel 2007+) complexity shouldn't be an issue.



    This solution isn't messy at all. I have used it hundreds of times at my job and is the primary method of running queries off our database and returning to Excel for analysis.

  • Re: SQL statement in .sql file through VBA


    You are almost there, you've got past there and gone too far ;)


  • Re: SQL statement in .sql file through VBA


    Kyle,



    I'm terribly sorry but for some reason I only saw your reply now. This is awesome, many thanks for your help!


    Is it possible to pass a variable/parameter (e.g. a date) from VBA to that file?

  • Re: SQL statement in .sql file through VBA


    The contents of the file is read into a variable so you can simply do a REPLACE replacing placeholders in the string with actual values. For example, if the file contains


    [COLOR="#000080"]Select * From TableName Where FieldName = {HOLDER1}[/COLOR]


    you can just do

    Code
    SQLStatement  = Replace(SQLStatement, "{HOLDER1}", 3)


    after reading the file into the variable.


    That is a very simple example replacing with a value, you'll have to code for strings and dates making sure the resulting SQL statement is valid.

  • Re: SQL statement in .sql file through VBA


    But of course! Thanks a lot cytop.


    What I did:


    Code
    SqlStatement = Replace(SqlStatement, "ReportingDateFor", ReportingDateFor)


    I first thought I had to use " & ReportingDateFor & " to indicate it's variable but that's not the case.


    So in my SQL statement there is this line:

    SQL
    SELECT @DATE = 'ReportingDateFor'


    and with the replace function I replace 'ReportingDateFor' with the actual value of the variable 'ReportingDateFor' in VBA, i.e. 2013-05-07


    This results in this amended SQL statement:

    SQL
    SELECT @DATE = '2013-05-07'


    Many thanks for your help all. This is much more user friendly. Now I no longer need a long list of strSQL = strSQL & strSQL " ... " but just have it stored in a sql file that is easy to manage in SQL Server Management Studio.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!