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
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
'Save the workbook and quit Excel.
oSheet = Nothing
oBook = Nothing
oExcel = Nothing
'Establish a connection to the data source.
Dim xlsConnectionString As String
xlsConnectionString = "Provider=Microsoft.Jet.OL
EDB.4.0;" & _
d Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbCon
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 & ";"
'Close the connection.
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.