SQL query using list of numbers in a sheet

[bogatl]

Good Afternoon,

I have a simple .xlsm book into which users can paste a list of file numbers and when run, the macro reads each file number, one at a time, and runs a query against a SQL DB and then pastes the results back into the book.

Is there a way to read all of the file numbers (sometimes in the tens of thousands) all at once, create a single record set and paste that record set back into the book in one pass?

I understand how to paste the query results into the sheet, but I can't find out how to run the query against all the file numbers at one time.

Thanks for any suggestions,

Brad

 

[Fazza]

Hi, Brad

It sounds likely. However I'm unclear on what is wanted. Can you explain a bit more about it, please?

It might help to have a sample of the SQL pasted?

regards, F

 

[bogatl]

Fazzo,

The macro I have now works by reading in a single file number from column A on sheet1, running a simple SELECT query using that file number and posting the results in column B, next to the file number. The macro loops through these steps, until it reaches the last file number in column A.

What I was wondering is if there is a way to take all the numbers from column A, at one time, run a query, and return the results of the query to column be at one time. Right now, the macro loops through each row and runs the query for as many file numbers that have been pasted in by the users, which can be slow for large amounts of file numbers.


Thanks again,

Brad

 

[Fazza]

Hi, Brad

Please post a sample of the SQL. Simplify it if you like, so long as the use of the file name is clear.

regards, F

 

[Fazza]

Hi, Brad. Likely are two choices. regards, F

SELECT T.field_names
FROM table T, Excel_list E
WHERE T.somefield = E.field
 
'or
 
SELECT field_names
FROM table
WHERE some_field IN ('file1name', 'file2name', 'file3name')

 

[bogatl]

Thank you, Fazza. In regard to your first example, how do I create or identify my column of file numbers as the 'Excel_list'?

SELECT T.field_names
FROM table T, Excel_list E
WHERE T.somefield = E.field
 
'or
 
SELECT field_names
FROM table
WHERE some_field IN ('file1name', 'file2name', 'file3name')

 

[Fazza]

Maybe like is shown http://www.mrexcel.com/forum/showthread.php?t=378209 in that the file properties for connecting to the workbook are set in the SQL. I can't test this as I don't have or use SQL DB. The idea is to have a connection to the database and in the SQL refer to the database table and also the workbook, with the workbook reference like in the above link.

For specifically referring to the worksheet data, refer http://support.microsoft.com/kb/257819 where the options are given - a worksheet, a defined name or a worksheet range. Note the defined name must not be dynamic as these are not detected in data connections.

If this approach proves difficult to implement, the other option should be straightforward as it is a text manipulation for the SQL - suggest using JOIN$(array_of_names,"'") - and connection solely to the database, not to both the database and Excel.

regards