Dynamically Using Sheetname in Sql-VBA

rs.Open "SELECT [Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].ID IS NULL ", cn, adOpenKeyset, adLockReadOnly

Sheet1, i.e. the name of the sheet in my case is variable in my case and I want to repeat this query for 100 sheets, so how do I dynamically specify the name?

Thanks in advance.

Answer

First, you cannot call an entire worksheet in an Excel SQL ADO query but must specify ranges: SELECT [Sheet1$A1:D50].ID FROM [Sheet1$A1:D50] WHERE [Sheet1$A1:D50].ID IS NULL

And yes, since this is a string statement you can concatenate VBA variables within it.

firstsheetname = "Sheet1"

strSQL = "SELECT [" & firstsheetname & "$].ID FROM [" _
           & firstsheetname & "$] WHERE [" & firstsheetname & "$].ID IS NULL

rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly