Solved

Error exporting to Excel spreadsheet with Excel 2007 installed

Posted on 2007-11-29
2
164 Views
Last Modified: 2010-04-24
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.Application")
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.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;Extended Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbConnection(xlsConnectionString)
objConn.Open()
Dim objCmd As New System.Data.OleDb.OleDbCommand()
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.

Open in new window

0
Comment
Question by:TSFLLC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 20382424
Yes.

Bob
0
 

Author Comment

by:TSFLLC
ID: 20382732
Bob,

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
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question