import xls data into datagridview on form

I'm attempting to allow users to either manually enter data or import an XLS file to fill a datagridview. I'm having dificulty getting the formatting to correct fill the DGV.

the project file and sample files are at this link

https://filedb.experts-exchange.com/incoming/ee-stuff/6738-Avid-File-Renamer.zip 
Judson HallSystems AdminAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Craig WagnerConnect With a Mentor Software ArchitectCommented:
You'll need to make some adjustments for the following code to work.

Declare a private member on the class to hold the name of the Excel file.

private XLSPath as string

Add a first row to the spreadsheet for column names.

Make sure the DataGrid columns map to the names you give the columns in Excel.
        Dim connectionStringTemplate As String = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" + _
    "Data Source={0};" + _
    "Extended Properties=""Excel 12.0;HDR=Yes"""
 
        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 System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)
        excelAdapter.Fill(workbook)
 
        Dim worksheet As DataTable = workbook.Tables(0)
 
        DataGridView1.DataSource = worksheet

Open in new window

0
 
Judson HallSystems AdminAuthor Commented:
I'm getting an error at the call for "XLSPath" (line 6) and at line 13
0
 
Craig WagnerSoftware ArchitectCommented:
Did you declare the variable?
0
 
Judson HallSystems AdminAuthor Commented:
I thought I did in the snippet right above this section.
'let user select xls file to process
    Private Sub ImportXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportXLS.Click
        If OpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim XLSPath As String = OpenFileDialog.FileName
        End If
    End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.