Problem with Excel Sheet name when importing using oledb
-
Hi Using VS2005 VB I am having Problems when importing data from an Excel sheet using oledb The sheets being opened as always use the following naming convention P_W03-29M40MH-S16A-1235296-XFA- The problem seems to be the dash at the end. If I rename the sheet and replace the dash with an underscore, it works fine I dont want to have to open the book first an rename the sheets every time. The code below throws an error when cmd.Execute reader executes. The error states: System.Data.OleDb.OleDbException = {"The Microsoft Jet database engine could not find the object 'P_W03-29M40MH-S16A-1235296-XFA-$.A:A'. Make sure the object exists and that you spell its name and the path name correctly."} This is the code I am using:
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\myFile.xls;" & _ "Extended Properties=""Excel 8.0;HDR=NO""" Dim conn As New System.Data.OleDb.OleDbConnection(strConn) conn.Open() Dim strSheet As String = "P_W03-29M40MH-S16A-1235296-XFA-" Dim cmd As New System.Data.OleDb.OleDbCommand("Select * from [" & strSheet & "$A:A]", conn) Dim rdr As OleDbDataReader = cmd.ExecuteReader -
Hi Using VS2005 VB I am having Problems when importing data from an Excel sheet using oledb The sheets being opened as always use the following naming convention P_W03-29M40MH-S16A-1235296-XFA- The problem seems to be the dash at the end. If I rename the sheet and replace the dash with an underscore, it works fine I dont want to have to open the book first an rename the sheets every time. The code below throws an error when cmd.Execute reader executes. The error states: System.Data.OleDb.OleDbException = {"The Microsoft Jet database engine could not find the object 'P_W03-29M40MH-S16A-1235296-XFA-$.A:A'. Make sure the object exists and that you spell its name and the path name correctly."} This is the code I am using:
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\myFile.xls;" & _ "Extended Properties=""Excel 8.0;HDR=NO""" Dim conn As New System.Data.OleDb.OleDbConnection(strConn) conn.Open() Dim strSheet As String = "P_W03-29M40MH-S16A-1235296-XFA-" Dim cmd As New System.Data.OleDb.OleDbCommand("Select * from [" & strSheet & "$A:A]", conn) Dim rdr As OleDbDataReader = cmd.ExecuteReaderJust off the top of my head: have you tried concatenating a ! in between the sheet name and the range?
"Select * from [" & strSheet & "!$A:A]"or you could try one of the following alternative syntaxes:"Select * from ['" & strSheet & "$A:A]"'""Select * from ['" & strSheet & "'$A:A]""Select * from ['" & strSheet & "'!$A:A]"'"My advice is free, and you may get what you paid for.
-
Just off the top of my head: have you tried concatenating a ! in between the sheet name and the range?
"Select * from [" & strSheet & "!$A:A]"or you could try one of the following alternative syntaxes:"Select * from ['" & strSheet & "$A:A]"'""Select * from ['" & strSheet & "'$A:A]""Select * from ['" & strSheet & "'!$A:A]"'"My advice is free, and you may get what you paid for.
Thanks, tried them but no luck. The '$' sign is eqivalent to the '!' usually used to indicate a sheet in an Excel Formula. I tried the same query in MS Query from Excel, and it gave the similar syntax and it works with the '-' at the end. The only difference was it the Filed name was as follows SELECT * FROM mysheetName$.F1 Do you know how to use an escape character for '-'. Mabee I can get the sheet name, strip off the dash, and insert a dash with an escape character sequence?
-
Thanks, tried them but no luck. The '$' sign is eqivalent to the '!' usually used to indicate a sheet in an Excel Formula. I tried the same query in MS Query from Excel, and it gave the similar syntax and it works with the '-' at the end. The only difference was it the Filed name was as follows SELECT * FROM mysheetName$.F1 Do you know how to use an escape character for '-'. Mabee I can get the sheet name, strip off the dash, and insert a dash with an escape character sequence?
The problem with Excel programming (in my experience) is that any code is rather sensitive to (MS Excel) version differences. I don't know if there is any kind of special escape character you could use, but one thing you can try is the "add quotes till it works" method. Something like this: "SELECT * FROM ['" & mysheetName- & "'$.F1]" next "SELECT * FROM [" & "'" & " & mysheetName- & " & "'" & "$.F1]" or "SELECT * FROM [''" & mysheetName- "''$.F1]" next "SELECT * FROM ['''" & mysheetName- "'''$.F1]" etc. (and you can try variations of the location of the quotes) One other option that came to mind was to try and use the sheets index instead of its name. I found this article that may be helpful: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm[^] and a third option that came to mind is to first programmatically change the sheet name before you run the query against it (a bit clumsy, but once it runs, who cares right?). And in conjunction to this idea: the sheet (name) seems to also get created by some sort of automatic process. Do you have access to this process?
My advice is free, and you may get what you paid for.
-
The problem with Excel programming (in my experience) is that any code is rather sensitive to (MS Excel) version differences. I don't know if there is any kind of special escape character you could use, but one thing you can try is the "add quotes till it works" method. Something like this: "SELECT * FROM ['" & mysheetName- & "'$.F1]" next "SELECT * FROM [" & "'" & " & mysheetName- & " & "'" & "$.F1]" or "SELECT * FROM [''" & mysheetName- "''$.F1]" next "SELECT * FROM ['''" & mysheetName- "'''$.F1]" etc. (and you can try variations of the location of the quotes) One other option that came to mind was to try and use the sheets index instead of its name. I found this article that may be helpful: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm[^] and a third option that came to mind is to first programmatically change the sheet name before you run the query against it (a bit clumsy, but once it runs, who cares right?). And in conjunction to this idea: the sheet (name) seems to also get created by some sort of automatic process. Do you have access to this process?
My advice is free, and you may get what you paid for.
Hi I have tried various options with the quotes with no sucess. Once I have selected the workbook from which I want to import the sheet, I load the sheets in the workbook into a combo. The user can then select which sheet they want to import. I do this as follows:
For Each sheet As Excel.Worksheet In xlPacklistBook.Sheets Me.cboPackListSheetSelect.Items.Add(sheet.Name) NextRegarding the second option, I did try using the sheet index, but that did not work. I suspect it is because in the following code, the command data type is a string:Dim cmd As New System.Data.OleDb.OleDbCommand("Select * from ['" & strSheet & "$.A:A]'", conn)In Excel VBA if you use Sheet(1) as a ref to a sheet, the one is an int value, not a string as in Sheet("mySheet") I'm trying th e third option at the moment, but having hassles - I'll let you know if I get it right..