TSFLLC
asked on
Error exporting to Excel spreadsheet with Excel 2007 installed
I have several users in a company who have upgraded their Office 2003 to Office 2007. They are getting errors which I would assume have to do with the 'Extended Properties' setting in the connection string.
I was previously using the code to export to an Excel spreadsheet using 'Extended Properties = Excel 8.0' within my connection string. One of the other users has not upgraded to Excel 2007 and the export to Excel works without any issues. The complete process is included below.
'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Applic ation")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Transfer the field names to Row 1 of the worksheet:
Dim n As Int32
For n = 1 To dt.Columns.Count
oSheet.Cells(1, n).Value = dt.Columns(n - 1).ColumnName
Next
'Save the workbook and quit Excel.
oBook.SaveAs(C:\Test.xls)
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
'Establish a connection to the data source.
Dim xlsConnectionString As String
xlsConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=C:\Test.xls;Extende d Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbCon nection(xl sConnectio nString)
objConn.Open()
Dim objCmd As New System.Data.OleDb.OleDbCom mand()
objCmd.Connection = objConn
'Set up Fields and Headers for entry into spreadsheet using For/Next loop
objCmd.CommandText = "INSERT INTO [Sheet1$] ( " & ColumnHeaders & " ) SELECT " & xlsFields & ";"
objCmd.ExecuteNonQuery()
'Close the connection.
objConn.Close()
Do I need to change Properties from '8.0' to '12.0' ?? If so, I've tried this and I am continuing to get an error.
I was previously using the code to export to an Excel spreadsheet using 'Extended Properties = Excel 8.0' within my connection string. One of the other users has not upgraded to Excel 2007 and the export to Excel works without any issues. The complete process is included below.
'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Applic
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Transfer the field names to Row 1 of the worksheet:
Dim n As Int32
For n = 1 To dt.Columns.Count
oSheet.Cells(1, n).Value = dt.Columns(n - 1).ColumnName
Next
'Save the workbook and quit Excel.
oBook.SaveAs(C:\Test.xls)
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
'Establish a connection to the data source.
Dim xlsConnectionString As String
xlsConnectionString = "Provider=Microsoft.Jet.OL
"Data Source=C:\Test.xls;Extende
Dim objConn As New System.Data.OleDb.OleDbCon
objConn.Open()
Dim objCmd As New System.Data.OleDb.OleDbCom
objCmd.Connection = objConn
'Set up Fields and Headers for entry into spreadsheet using For/Next loop
objCmd.CommandText = "INSERT INTO [Sheet1$] ( " & ColumnHeaders & " ) SELECT " & xlsFields & ";"
objCmd.ExecuteNonQuery()
'Close the connection.
objConn.Close()
Do I need to change Properties from '8.0' to '12.0' ?? If so, I've tried this and I am continuing to get an error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Minutes ago I found the link below that told me what I needed to know.
http://msdn2.microsoft.com/en-us/library/ms139836.aspx
However, it requires that Provider to be changed to the following:
Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider
before it would work for me.
Thanks!
Phil