Solved

Error exporting to Excel spreadsheet with Excel 2007 installed

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

919 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

19 Experts available now in Live!

Get 1:1 Help Now