Excel 2007 connection via oledbconnection

  • Thread starter Thread starter vtwin
  • Start date Start date
V

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..
 
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.....
 
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 :(
 
Back
Top