Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'Sheet
I have found a problem while accessing Excel spreadsheets via OleDB (I'm not
sure if this is the correct newsgroup for this, but here goes...) The problem only occurs if I have used filtering in the spreadsheet. Could anyone tell me if this is a bug? Is there a workaround? I get the following error when calling connection.GetSchema("Columns"); - The Microsoft Jet database engine could not find the object 'Sheet1$_'. Make sure the object exists and that you spell its name and the path name correctly. Steps to reproduce: 1. Create a new spreadsheet in Execl 2007 (haven't tried earlier) 2. In A1 type a column heading, e.g. ID 3. In A2 type a value, e.g. 1 4. In A2, enable filtering (Ctrl+Shift+L) 5. Save the file in Excel 97-2003 compatible format. 6. Using .NET 2.0, attempt to get the metadata from the workbook using the following code: string ctnStr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + excelFile + "; Extended Properties=\"Excel 8.0;IMEX=1\";"; using (OleDbConnection excelCtn = new OleDbConnection(ctnStr)) { excelCtn.Open(); inputTableInfo = excelCtn.GetSchema("Tables"); inputColumnInfo = excelCtn.GetSchema("Columns"); // fails here } The code fails with the following exception when trying to access the column metadata: Type : System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : The Microsoft Jet database engine could not find the object 'Sheet1$_'. Make sure the object exists and that you spell its name and the path name correctly. Source : Microsoft JET Database Engine ErrorCode : -2147467259 Errors : System.Data.OleDb.OleDbErrorCollection Data : System.Collections.ListDictionaryInternal TargetSite : System.Data.DataTable GetSchemaRowset(System.Guid, System.Object[]) Stack Trace : at System.Data.OleDb.OleDbConnectionInternal.GetSchem aRowset(Guid schema, Object[] restrictions) at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTa ble(Guid schema, Object[] restrictions) at System.Data.OleDb.OleDbMetaDataFactory.PrepareColl ection(String collectionName, String[] restrictions, DbConnection connection) at System.Data.ProviderBase.DbMetaDataFactory.GetSche ma(DbConnection connection, String collectionName, String[] restrictions) at System.Data.ProviderBase.DbConnectionInternal.GetS chema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions) at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues) at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName) It seems that by enabling filtering, a hidden worksheet is created using the name of the filtered worksheet followed by a trailing underscore. This worksheet is listed as one of the "tables" when calling connection.GetSchema("Tables") but it makes the call to GetSchema("Columns") fall over. The main problem I am having is that disabling the filter does not remove this hidden worksheet. So once filtering is enabled the workbook is useless to me. Any help would be appreciated. Thanks Stu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'Sheet
Hi Stu,
I reproduce this issue. I am performing some research on it. I appreciate your patience. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'S
I don't quite understand the question.
"Wei Lu [MSFT]" wrote: Hello Stu, I would like to know whether you open that excel, did this issue still occured? Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'S
Actually, I understand now. The problem doesn't occur if the spreadsheet is
open in Excel. However, the spreadsheet won't be open in production. At the moment I am using a work around, which is to filter out any table names ending in $_ and to request column info for each of the remaining tables individulally, using the "restrictions" overload, e.g. DataTable tempTable = excelCtn.GetSchema("Columns", new string[] { null, null, row["TABLE_NAME"].ToString(), null }); This works fine. But I still think it is a bug that the call to GetSchema("Columns") fails if you've used filtering in the spreadsheet (even if you've subsequently turned filtering off). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'S
Hello Stu,
I am still researching this issue. If any discover, I will let you know. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'S
Hi Stu,
I'm the community lead of managed newsgroup support team. I'd like to check the status on this issue, have you got any further progress? Wei has discussed this issue with me and we agree that this should be a problem with the OLEdb provider which connectiong the excel and pull out the sheet's schema info. Since we think this a serious problem and may affect your application developing, if you're still monitoring on this issue and want to continue look for some further solution on this, we'd like to leverage some further research to help you. You can contact us through email (in our signature and remove "online") so that we can communicate with you more efficiently on this. Please feel free to let me know if there is anything need help. Thank you for your posting. Sincerely, Steven Cheng Microsoft MSDN Online Support Lead This posting is provided "AS IS" with no warranties, and confers no rights. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
OleDbConnection.GetSchema() and Excel
If needed, I am experiencing this sort of problem and I would be happy to work with support to provide information which might lead to an understanding or a resolution of this problem.
Briefly, I am using C# and ADO.NET to read an Excel workbook with multiple worksheets. When I use the code below, I get multiple entries for each sheet. For example, I have a sheet named "Plate_2". When I do the GetSchema call, I see entries for "Plate_2" and one for "Plate_2$". More confusing, for the sheet named "Plate_3", I have "Plate_3", "Plate_3$", and "Plate_3$_". Which of these is the worksheet? Why are there threee different entries? What do the differences indicate? Are there any other funky things like "Print_Area" which I should expect to see? Thanks, David [code] public static string LoadWorkbookSchema(string workbookPath, ref DataTable dtWorksheets) { try { using (OleDbConnection connection = GetWorkbookConnection(workbookPath)) { dtWorksheets = connection.GetSchema("Tables"); } foreach (DataRow dr in dtWorksheets.Rows) { if (dr["TABLE_NAME"].ToString().EndsWith("Print_Area")) dr.Delete(); } } catch (Exception exception) { return exception.Message; } return ""; } public static OleDbConnection GetWorkbookConnection(string WorkBookPath) { // Note that IMEX=1 was added to eliminate problems with data in the "9th-plus line" // having a different data format than the first 8. dwr string connectionString = ;D ata Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""", WorkBookPath); OleDbConnection connection = new OleDbConnection(connectionString); connection.Open(); return connection; } EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Microsoft Jet database engine could not find the object 'Sheet
Hi Can any one help me out? I have an upload program (excel file will be uploaded) written in C# (ASP.Net). The program uses OLEDB connection to query the file For most of the files the upload is working. But however, only for one file am receiving below error. The Microsoft Jet database engine could not find the object 'Project'. Make sure the object exists and that you spell its name and the path name correctly. I Query based on the named ranges from the EXCEL. Any idea of what would be the problem? Thanks in advance -- kumaresh_81 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The Microsoft Jet database engine cannot open the file ". | Excel Discussion (Misc queries) | |||
Pass4sure Microsoft 70-623 Exam Test Engine | Excel Discussion (Misc queries) | |||
How to make customer database with search engine by addy or name | Excel Discussion (Misc queries) | |||
MS JET database engine error: workgroup information file is missing or opened exclusively by another user | Excel Programming | |||
Where is Microsoft Map, I can't find it in the Insert Object menu | Excel Discussion (Misc queries) |