Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 connection via oledbconnection
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 connection via oledbconnection
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..... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 connection via oledbconnection
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 :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 data connection | Excel Discussion (Misc queries) | |||
Data Connection between Excel 2007 and SharePoint 2007 | Excel Discussion (Misc queries) | |||
Data Connection Security in Excel 2007 | Setting up and Configuration of Excel | |||
Problem opening Excel 2007 file from OleDbConnection | Excel Programming | |||
Excel 2007 - The connection for this PivotTable has been deleted | Excel Discussion (Misc queries) |