Problem importing xls file to datagrid on older machines.
I have this handy little program that works terrific on my development machine. The problem only arises when I try to run the app on the production machines. I'm sure it have to do with the Microsoft version 12 OLEDB provider. I don't know how to either: A. re-write the program using an older provider (version 8?) or B. include the required components in the installer.
Private Sub XLSSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles XLSselect.Click
'filter all but .xls
OpenFileDialog.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
'pick file
If OpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim XLSPathx As String = OpenFileDialog.FileName
End If
'not sure why this works, but it does
Dim XLSPath As String = OpenFileDialog.FileName
'dim connector provider
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _"Data Source={0};" +_ "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
Dim connectionString As String = String.Format(connectionStringTemplate, XLSPath)
Dim sqlSelect As String = "SELECT * FROM [Sheet1$];"
' Load the Excel worksheet into a DataTable
Dim workbook As DataSet = New DataSet()
Dim excelAdapter As System.Data.Common.DataAdapter = New OleDbDataAdapter(sqlSelect, connectionString)
Try
excelAdapter.Fill(workbook)
Dim worksheet As DataTable = workbook.Tables(0)
DataGridView.DataSource = worksheet
Catch
End Try
End Sub
OverSeer
What's the error you receive? And correct me if I'm wrong but Excel 12 is Excel 2007 correct?
ASKER
right .....Excel 2007 .... it seems there is a 25meg download that has the database components for 2007 OLEDB. any idea if that entire package has to be installed on the client????
I think it would be better to re-write this for an older version of database components.
BTW, I brought the compiled program home. It opened on My Vista PC (with office 2007) but not on my XP Laptop (With office 2003).
I think it would be better to re-write this for an older version of database components.
BTW, I brought the compiled program home. It opened on My Vista PC (with office 2007) but not on my XP Laptop (With office 2003).
What's the version of Excel you're importing into?
Wait, are you importing into 2003 from 2007? That's the problem. Excel 2007 change the maxint a cell can hold (or something along those lines) since 2003. I can't remember the exact issue but we had come across it where I work. See if you can investigate that.
ASKER
Nope, its a 2003 XLS that I'm importing into a datagridview for user interaction (the user can append data to the data, not the excel. There is not on ecopy of Excel 2007 in the entire company.
ASKER
I changed the connectring template provider from:
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OL
To:
Dim connectionStringTemplate As String = "Provider=Microsoft.Jet.OL
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
and the rest of the thread worked as written.
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OL
To:
Dim connectionStringTemplate As String = "Provider=Microsoft.Jet.OL
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
and the rest of the thread worked as written.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.