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

Open in new window

Judson HallSystems AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OverSeerCommented:
What's the error you receive?  And correct me if I'm wrong but Excel 12 is Excel 2007 correct?
0
Judson HallSystems AdminAuthor Commented:
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).
0
OverSeerCommented:
What's the version of Excel you're importing into?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

OverSeerCommented:
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.
0
Judson HallSystems AdminAuthor Commented:
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.
0
Judson HallSystems AdminAuthor Commented:
I changed the connectring template provider from:

Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""
       
To:

Dim connectionStringTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + _
        "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

and the rest of the thread worked as written.
0
OverSeerCommented:
I'm all for saving the points but you have to admit, I did kind of point you in the right direction right?  :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.