VBA Command to Query SqlExpress

[habman]

The following query statement works

sqlstring = "Select DocHeader, DocID from DocDetail where DocHeader IN ('15100','17200','18100')"


When I execute the command it populates my Excel Spreadsheet with all the DocHeader's and DocID's where the DocHeader's appear in the array ('15100','17200','18100')

The output will look something like this:

DocHeader DocID
15100 12348668
15100 12223886
15100 12268468
17200 56584562
18100 45846852
18100 55864868



I would like to run a similar command except that instead of identifying the array in the query I would like to select a range of cells from my spreadsheet.

I know the following isn't correct but it might give you some idea of what I want


sqlstring = "Select DocHeader, DocID from DocDetail where DocHeader IN (" & Range("A1:A1000") & ")"


Column A Rows 1 to 1000 lists all the DocHeader's that I want to have listed when the query is executed?

I am not sure how to write the query so that I can define my range from the excel sheet.

Hopefully someone can help.

Thanks.

 

[tusharm]

SQL doesn't understand Excel ranges. You have to use a loop to convert each of the range values into a string delimited by single quotes. Something along the lines of the untested

sqlstring = "Select DocHeader, DocID from DocDetail where DocHeader IN ("
dim aCell as range
for each acell in range("A1:A1000"")
    sqlstring=sqlstring & "'" & acell.value & "',"
    next acell
sqlstring=left(sqlstring,len(sqlstring)-1) & ")"

I don't know if there is a limit on how long a string SQL will accept. You will have to try it out and see.

The following query statement works

sqlstring = "Select DocHeader, DocID from DocDetail where DocHeader IN ('15100','17200','18100')"


When I execute the command it populates my Excel Spreadsheet with all the DocHeader's and DocID's where the DocHeader's appear in the array ('15100','17200','18100')

The output will look something like this:

DocHeader DocID
15100 12348668
15100 12223886
15100 12268468
17200 56584562
18100 45846852
18100 55864868



I would like to run a similar command except that instead of identifying the array in the query I would like to select a range of cells from my spreadsheet.

I know the following isn't correct but it might give you some idea of what I want


sqlstring = "Select DocHeader, DocID from DocDetail where DocHeader IN (" & Range("A1:A1000") & ")"


Column A Rows 1 to 1000 lists all the DocHeader's that I want to have listed when the query is executed?

I am not sure how to write the query so that I can define my range from the excel sheet.

Hopefully someone can help.

Thanks.