Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error exporting to Excel spreadsheet with Excel 2007 installed

Posted on 2007-11-29
2
Medium Priority
?
166 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 1500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

877 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