Excel.....

[RaceAap]

Hi,

I need to open an excel sheet with ole db.. but when the sheet has a comment on A1 I get an error that the object could not be found...

I use the following code to open the sheet:

strExcelString = "Data Source=" & strExcelFile & "; Extended Properties=Excel 8.0;"

Set conExcelFile = CreateObject("ADODB.Connection&quot
With conExcelFile
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = strExcelString
.CursorLocation = adUseClient
.Open
End With

Set rstExcel = CreateObject("ADODB.Recordset&quot

Set rsT = conExcelFile.OpenSchema(adSchemaTables)

intTblCnt = rsT.RecordCount
For t = 1 To intTblCnt

strTbl = rsT.Fields("TABLE_NAME&quot.Value
tbl(t) = Replace(strTbl, "'", "&quot
'Response.Write( &quot;Sheet: &quot; & strTbl & &quot;<br>&quot
rsT.MoveNext
Next

'Need to make the spreadsheet selection automatic.
strExcelStatement = &quot;select * from [&quot; & tbl(1) & &quot;] &quot;
'Response.Write(strExcelStatement)
With rstExcel
Set .ActiveConnection = conExcelFile
.Open strExcelStatement
End With

At .Open strExcelStatement I get the error...
Who can help me??

When I open the file and remove the comment from A1 I can read the file, but I need to be able to open the file whithout editing as it is an automated process....

Thanx
Lon

 

[browolf]

can u provide the exact error msg? is there a error code? ===============
Security Forums

http://www.security-forums.com

 

[RaceAap]

The error:

Microsoft JET Database Engine error '80040e37'

The Microsoft Jet database engine could not find the object ''200301240752 DailyReport$''. Make sure the object exists and that you spell its name and the path name correctly.

 

[browolf]

can u make it output the contents of
strExcelStatement b4 it does the .open

so we can see what it is. ===============
Security Forums

http://www.security-forums.com

 

[RaceAap]

strExcelStatement gives this output: &quot;select * from ['200301240752 DailyReport$']&quot;

 

[browolf]

in a normal sql statement you have to do
select * from source

source being the worksheet name in excel
i dont think you want the brackets either.

you need to look for code examples for asp to excel on google until it becomes obvious what you're doing wrong. i havent time, i'm supposed to making dinner ;-) ===============
Security Forums

http://www.security-forums.com

 

[RaceAap]

Problem is that this code is from a code example...

The workbooks are selected as if they are a table, the brackets are there for the spaces that can be in the name of the sheet ( Just as when you use MS Access as your database... ), but i can try to do it whitout them for the fun of it.

If I know the problem, I will post it here!!

 

[RaceAap]

Found the problem.....

When the excel sheet has NO comment on A1 the Workbook/Sheet name is whitout the ' around it, when there is a comment field, the sheet name has ' around it.. like:

No comment: 200301240752 DailyReport$
With comment: '200301240752 DailyReport$'

I just replace the ' for nothing and it works fine now.....