Referring to fields with "." from XLS file

[guitarzan]

I'm trying to read data from an Excel spreadsheet (from a VBScript/ASP application), which has a header row.
The following code works fine, even for fields whose name includes spaces... except, there are fields that include a period which dont seem to work. The bolded line gives me the following error:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

Dim Conn, rs
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open _
	"Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=" & Server.MapPath("/") & "\..\data\CustomerList.xls" & _
	";Persist Security Info=False;Extended Properties='Excel 8.0'"
Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM [Sheet1$] WHERE [Account No.] = '1'", Conn

Dim sAddress, sCLID
If rs.EOF Then
	sAddress = "No Address"
Else
	sAddress = rs("Bill To 1") & "<BR>" & rs("Bill To 2")
	[b][COLOR=red]sCLID = rs("Account No.")[/color][/b]
End If

rs.Close
Conn.Close

The field is spelled correctly. I also tried sCLID = rs("[Account No.]"), but get the same error.

Also, when I include this field in a WHERE statement, as in:
rs.Open "SELECT * FROM [Sheet1$] WHERE [Account No.] = '1'", Conn

I get:
Microsoft JET Database Engine error '80004005'
'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Is there a way I can reference this field?

 

[dhookom]

Apparently the field names are coming from an Excel worksheet? You can try replace the period with an underscore.

Dim Conn, rs
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("/") & "\..\data\CustomerList.xls" & _
    ";Persist Security Info=False;Extended Properties='Excel 8.0'"
Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM [Sheet1$] WHERE [Account No_] = '1'", Conn

Dim sAddress, sCLID
If rs.EOF Then
    sAddress = "No Address"
Else
    sAddress = rs("Bill To 1") & "<BR>" & rs("Bill To 2")
    sCLID = rs("Account No_")
End If

rs.Close
Conn.Close

Duane
Hook'D on Access
MS Access MVP

 

[guitarzan]

Thanks for the response. Yes, they are from an excel workbook... and yes, changing "Account No." to "Account No_" or even "Account No" works fine.

The xls file is genereated from a Quickbooks Customer list export, and I think "Account No." is a standard Quickbooks field, so I don't think it can be renamed or mapped to a different field name (not easily, anyway). I'd prefer to use the spreadsheet as Quickbooks creates it, but I may be at a dead-end here.