Excel 2007 connection via oledbconnection

[vtwin]

I'm trying to open a connection to an excel 2007 file ( xlsx ) with
OleDbConnection.
The excel file in question contains a couple of worksheets, some of them
have graphs/charts on it and no data.

This is the connection string I use:
string con = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " + fileName
+ ";Extended Properties =\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";

OleDbConnection DBCon = new OleDbConnection(con);
DBCon.Open(); // failed

It failed with an "External table is not in the expected format" exception.
Two points I found:
1. It works fine if I have the excel file open
2. It works fine if I remove all the worksheets with the graphs/charts in it

My question is if there is any option to include in the connection string to
ignore the graph/chart in the excel file? Just so that the connection to the
file can be opened..

any help will be much appreciated..

 

[arjen van...]

It depends on what you're trying to do. If you're querying data from the
workbook, you can specify the worksheet in your query string.

An example I've used:

Sub QueryExcel()

'create the connection string
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=K:\ADO\DataSheet.xls;" & _
"Extended Properties=Excel 8.0;"

'create the sql query
Dim MyQuery As String

'select the Today sheet
MyQuery = "SELECT * " & _
"FROM [Today$] "

'create the recordset
Dim MyRS As ADODB.Recordset
Set MyRS = New ADODB.Recordset

'open the recordset
MyRS.Open MyQuery, ConnectionString,adOpenStatic,adLockReadOnly, _
adCmdText

Sheets("xl data").Activate
ActiveSheet.Range("A1").CopyFromRecordset MyRS

MyRS.Close
Set MyRS = Nothing

End Sub

I'm not sure if you're trying to do the same type of thing.....

 

[vtwin]

I was trying to get the worksheet names out of the workbook using
OleDBConnection. So unfortunately won't be able to specify the worksheet
name beforehand