How to use Range as table

[Raveendra Kumar G]

Hi Sir/Madam,

When we use excel as database
we use query strSQL = "Select Distinct [Product] From [data$] Order by [Product]"
Here "data" is sheet name.
But I want to use [dataA1:G10$] instead of [data$].
When I use as [dataA1:G10$] macro throwing error.
Can you help me out how can I use?

For your better uderstanding
https://chandoo.org/wp/using-excel-as-your-database/

 

[Hui]

Try:

strSQL = "Select Distinct [Product] From [Sheet1$A1:G10] Order by [Product]"
or
strSQL = "Select Distinct [Product] From [myRng] Order by [Product]"

where myRng is a named Range to Sheet1!A1:G10

 

[Raveendra Kumar G]

Hi Sir,
I tried with your code but it is not working
Itried with small change instead of Sheet1 I used as "Data". "Data" is a Sheet1 name
Now, I it is working

When I tried with Sheet1 I got below error.



My Code:
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
strSQL = "Select Distinct [Product] From [Data$A1:G10] Order by [Product] "
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

Thanks a Lot for your awesome response.