[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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)
            Dim worksheet As DataTable = workbook.Tables(0)
            DataGridView.DataSource = worksheet
        End Try
    End Sub

Open in new window

Judson Hall
Judson Hall
  • 4
  • 3
1 Solution
What's the error you receive?  And correct me if I'm wrong but Excel 12 is Excel 2007 correct?
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).
What's the version of Excel you're importing into?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.
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.
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""

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.
I'm all for saving the points but you have to admit, I did kind of point you in the right direction right?  :)

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now