rs.Open "SELECT * FROM [sheet1$]" 
Author Message
 rs.Open "SELECT * FROM [sheet1$]"

Hello,
    Suppose the following code.  How can I open a worksheet if I do not know
in advance what is the name of that worksheet?  Can [sheet1$]  be replaced
by something generic?
      ..............
      sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
      rs.Open "SELECT * FROM [sheet1$]", sconn
      Set Read_Excel = rs
      .............

Thank you

Learner



Fri, 03 Sep 2004 04:52:17 GMT  
 rs.Open "SELECT * FROM [sheet1$]"
Hi,

What you can do is to build your SQL statement dynamicalyy

Dim lcWSName as string

'get worksheet nbame here and assign it to variable
lcWSName= "name of worksheet here"

rs.Open "SELECT * FROM [" & lcWSName "]", sconn

I think you can get list of Excel worksheets using ADOX
library or OpenSchema method of ADO Connection

Val

Quote:
>-----Original Message-----
>Hello,
>    Suppose the following code.  How can I open a

worksheet if I do not know
Quote:
>in advance what is the name of that worksheet?  Can

[sheet1$]  be replaced
Quote:
>by something generic?
>      ..............
>      sconn = "DRIVER=Microsoft Excel Driver (*.xls);"
& "DBQ=" & sFile
>      rs.Open "SELECT * FROM [sheet1$]", sconn
>      Set Read_Excel = rs
>      .............

>Thank you

>Learner

>.



Fri, 03 Sep 2004 20:57:06 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. rs.MaxRecords vs "SELECT TOP NN"

2. using rs.Fields("somedate") with excel

3. RAM and "set rs = nothing" (DAO)

4. Conn.Execute "Insert..." vs rs.update

5. How to code for rs("field")

6. How to code for rs("field")

7. "Open/Open With..." from Win Explorer

8. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

9. Trapping "Expand", "Open", "Explore" in Windows Explorer?

10. open dialog box "save as" or "open"

11. Select every "n"th record looped from user input

12. Referencing the "Selected" mail item

 

 
Powered by phpBB® Forum Software