Reference Excel fields over ADODB

[cthaxter]

I'm trying to connect to an Excel file through ADODB and manipulate it like any other database.

The following code from

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195951

works just fine:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "ADOExcel"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3                    
objRS.LockType = 2                     
objRS.Source = "Select * from myRange1"
objRS.Open

The source could have also been

objRS.Source = "Select * from [myWorksheet1$]"

I can't figure out how to specify specific fields, so that I can pass parameters or create a recordset with only one or two fields. Usually, you just say "Select Name, Number From myTable" but in this case, Excel doesn't understand that. The myTable becomes [myWorksheet$], but what do Name and Number become?

Christopher

 

[NiteCrawlr]

try this link:

http://www.asp101.com/samples/xl_data.asp

 

[cthaxter]

Thanks. Sorry, the problem I was having didn't have to do with naming, as it happens. I was just trying to use Options that weren't compatible with the type of recordset I was using 3 for Cursor, 4 for LockType, and 2 for Options, and I think the Options value was the problem. So, the following does work as the SQL for the Open method:

"Select Name, Number From [myWorksheet$]"