Solved

Error exporting to Excel spreadsheet with Excel 2007 installed

Posted on 2007-11-29
2
154 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
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now