Question Import data from an excel file

S

sweit

Member
Joined
Mar 30, 2016
Messages
9
Programming Experience
10+
Hello,

I'm writing a program that will save data from a few variable into cells of an excel file. I also would like to load the data from a saved excel file into the variables. I have a working sub that exports the data to an excel file. I'm not sure how to load the data from an excel file back into the variables.

Thank you,
Steve
 
Sort by date Sort by votes
jmcilhinney said:
When you say "Excel file", do you mean CSV, XLS, XLSX or something else?
Click to expand...

XLS file. I'm saving variable values into cells on a spreadsheet. I would like to be able to load the values from those cells back into the variables at a later date when a user needs to load a specific data set.
 
Upvote 0 Downvote
The two most common ways to read data from an XLS file are ADO.NET, which works in almost the same way as reading data from an Access database, and Office Automation, which is more complex and requires Excel to be installed but is also more reliable if the data is a bit dodgy.
 
Upvote 0 Downvote
jmcilhinney said:
The two most common ways to read data from an XLS file are ADO.NET, which works in almost the same way as reading data from an Access database, and Office Automation, which is more complex and requires Excel to be installed but is also more reliable if the data is a bit dodgy.
Click to expand...


Here is my code to save the data into the spreadsheet.

VB.NET: 
  Private Sub btnCalCert_Click(sender As System.Object, e As System.EventArgs) Handles btnCalCert.Click, SaveToolStripMenuItem.Click
        ' Load data into the MAC125 Calibration excel spread sheet.

        'Path and excelFile variables are used for relative addressing the excel template file
        Dim path As String = My.Application.Info.DirectoryPath
        Dim excelFile As String = IO.Path.Combine(path, "MAC125CalDataTemplate.xls")

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlWorkSheet2 As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        ' Open the MAC125 Calibration data template file
        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(excelFile)
        xlWorkSheet1 = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
        xlWorkSheet2 = CType(xlWorkBook.Sheets("sheet2"), Microsoft.Office.Interop.Excel.Worksheet)

        ' Populate the template file with MAC125 data

        xlWorkSheet1.Cells(4, 10) = pressure

        xlWorkSheet1.Cells(4, 4) = lblRange.Text

        xlWorkSheet1.Cells(9, 4) = txtRoom.Text
        xlWorkSheet1.Cells(9, 5) = lblPercent3.Text
        xlWorkSheet1.Cells(9, 6) = lblPercent4.Text
        xlWorkSheet1.Cells(9, 7) = lblPercent5.Text
        xlWorkSheet1.Cells(9, 8) = lblPercent6.Text
        xlWorkSheet1.Cells(9, 9) = lblPercent7.Text
        xlWorkSheet1.Cells(9, 10) = lblPercent8.Text

        xlWorkSheet1.Cells(10, 3) = txtWatlow1.Text
        xlWorkSheet1.Cells(10, 4) = txtWatlow2.Text
        xlWorkSheet1.Cells(10, 5) = txtWatlow3.Text
        xlWorkSheet1.Cells(10, 6) = txtWatlow4.Text
        xlWorkSheet1.Cells(10, 7) = txtWatlow5.Text
        xlWorkSheet1.Cells(10, 8) = txtWatlow6.Text
        xlWorkSheet1.Cells(10, 9) = txtWatlow7.Text
        xlWorkSheet1.Cells(10, 10) = txtWatlow8.Text

        xlWorkSheet1.Cells(11, 3) = txtDisplay1.Text
        xlWorkSheet1.Cells(11, 4) = txtDisplay2.Text
        xlWorkSheet1.Cells(11, 5) = txtDisplay3.Text
        xlWorkSheet1.Cells(11, 6) = txtDisplay4.Text
        xlWorkSheet1.Cells(11, 7) = txtDisplay5.Text
        xlWorkSheet1.Cells(11, 8) = txtDisplay6.Text
        xlWorkSheet1.Cells(11, 9) = txtDisplay7.Text
        xlWorkSheet1.Cells(11, 10) = txtDisplay8.Text

        xlWorkSheet1.Cells(14, 3) = btnRange0R0.Text
        xlWorkSheet1.Cells(14, 4) = btnRange2R0.Text
        xlWorkSheet1.Cells(14, 5) = btnRange4R0.Text
        xlWorkSheet1.Cells(14, 6) = btnRange6R0.Text
        xlWorkSheet1.Cells(14, 7) = btnRange8R0.Text
        xlWorkSheet1.Cells(14, 8) = btnRange10R0.Text
        xlWorkSheet1.Cells(14, 9) = btnRange12R0.Text
        xlWorkSheet1.Cells(14, 10) = btnRange14R0.Text

        xlWorkSheet1.Cells(15, 1) = lblEquation.Text

        xlWorkSheet1.Cells(15, 3) = btnRange0R1.Text
        xlWorkSheet1.Cells(15, 4) = btnRange2R1.Text
        xlWorkSheet1.Cells(15, 5) = btnRange4R1.Text
        xlWorkSheet1.Cells(15, 6) = btnRange6R1.Text
        xlWorkSheet1.Cells(15, 7) = btnRange8R1.Text
        xlWorkSheet1.Cells(15, 8) = btnRange10R1.Text
        xlWorkSheet1.Cells(15, 9) = btnRange12R1.Text
        xlWorkSheet1.Cells(15, 10) = btnRange14R1.Text

        xlWorkSheet1.Cells(25, 6) = mac125Voltage

        xlWorkSheet1.Cells(27, 2) = mac125Model

        xlWorkSheet1.Cells(36, 2) = mac125Status

        xlWorkSheet1.Cells(21, 8) = filterBlowBack

        xlWorkSheet1.Cells(23, 8) = caseCool

        xlWorkSheet1.Cells(25, 8) = caseHeater

        xlWorkSheet1.Cells(27, 8) = digitalDisplay

        xlWorkSheet1.Cells(28, 6) = mac125SerialNumber.ToString
        xlWorkSheet1.Cells(30, 6) = mac125HtProbeSerialNumber.ToString
        xlWorkSheet1.Cells(32, 6) = rangeModuleSerialNumber.ToString
        xlWorkSheet1.Cells(34, 6) = rmaNumber.ToString
        xlWorkSheet1.Cells(36, 6) = purchaseOrderNumber.ToString

        xlWorkSheet1.Cells(33, 10) = dateTested.ToString

        xlWorkSheet1.Cells(36, 10) = testedBy.ToString

        ' Populate sheet 2 of template which is the Calibration certificate
        xlWorkSheet2.Cells(9, 5) = mac125SerialNumber.ToString
        xlWorkSheet2.Cells(11, 5) = rangeModuleSerialNumber.ToString
        Select Case mac125Model
            Case 1
                xlWorkSheet2.Cells(13, 5) = "MAC125"

            Case 2
                xlWorkSheet2.Cells(13, 5) = "MAC125-HT"

            Case 3
                xlWorkSheet2.Cells(13, 5) = "MAC125-ES"
        End Select

        xlWorkSheet2.Cells(15, 5) = lblRange.Text

        xlWorkSheet2.Cells(17, 5) = strOutput.ToString
        xlWorkSheet2.Cells(19, 5) = purchaseOrderNumber.ToString

        xlWorkSheet2.Cells(45, 4) = dateTested.ToString
        xlWorkSheet2.Cells(47, 4) = testedBy.ToString

        If HumidityRatioFlag Then
            ' Fill in humidity ration calibration data
            xlWorkSheet2.Cells(24, 6) = txtRoom.Text
            xlWorkSheet2.Cells(25, 6) = lblPercent3.Text
            xlWorkSheet2.Cells(26, 6) = lblPercent4.Text
            xlWorkSheet2.Cells(27, 6) = lblPercent5.Text
            xlWorkSheet2.Cells(28, 6) = lblPercent6.Text
            xlWorkSheet2.Cells(29, 6) = lblPercent7.Text

            xlWorkSheet2.Cells(24, 7) = txtWatlow2.Text
            xlWorkSheet2.Cells(25, 7) = txtWatlow3.Text
            xlWorkSheet2.Cells(26, 7) = txtWatlow4.Text
            xlWorkSheet2.Cells(27, 7) = txtWatlow5.Text
            xlWorkSheet2.Cells(28, 7) = txtWatlow6.Text
            xlWorkSheet2.Cells(29, 7) = txtWatlow7.Text
        Else
            ' Fill in %MBV Data
            Try
                xlWorkSheet2.Cells(24, 3) = (Convert.ToSingle(txtRoom.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(24, 3) = 0
            End Try



            xlWorkSheet2.Cells(25, 3) = lblPercent3.Text
            xlWorkSheet2.Cells(26, 3) = lblPercent4.Text
            xlWorkSheet2.Cells(27, 3) = lblPercent5.Text
            xlWorkSheet2.Cells(28, 3) = lblPercent6.Text
            xlWorkSheet2.Cells(29, 3) = lblPercent7.Text

            Try
                xlWorkSheet2.Cells(24, 4) = (Convert.ToSingle(txtWatlow2.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(24, 4) = 0
            End Try

            Try
                xlWorkSheet2.Cells(25, 4) = (Convert.ToSingle(txtWatlow3.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(25, 4) = 0
            End Try

            Try
                xlWorkSheet2.Cells(26, 4) = (Convert.ToSingle(txtWatlow4.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(26, 4) = 0
            End Try

            Try
                xlWorkSheet2.Cells(27, 4) = (Convert.ToSingle(txtWatlow5.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(27, 4) = 0
            End Try

            Try
                xlWorkSheet2.Cells(28, 4) = (Convert.ToSingle(txtWatlow6.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(28, 4) = 0
            End Try

            Try
                xlWorkSheet2.Cells(29, 4) = (Convert.ToSingle(txtWatlow7.Text) / 100)
            Catch ex As Exception
                xlWorkSheet2.Cells(29, 4) = 0
            End Try

        End If

        ' Open save file dialog to save Cal data excel file 
        Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "Excel files (*.xls)|*.xls"
        saveFileDialog1.Title = "Save Calibration Data to Excel file"

        saveFileDialog1.ShowDialog()

        If saveFileDialog1.FileName <> "" Then
            xlWorkBook.SaveAs(saveFileDialog1.FileName.ToString)
        End If

        Process.Start("EXCEL.EXE", saveFileDialog1.FileName.ToString)

        xlWorkBook.Close()
        xlApp.Quit()

        xlWorkBook = Nothing
        xlWorkSheet1 = Nothing
        xlWorkSheet2 = Nothing

    End Sub


Below is the code I tried to load the data from an excel file into the VB program. But when I run it I get the following error:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Conversion from type 'Range' to type 'Integer' is not valid.


VB.NET: 
 Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click

        Dim strFileName As String = ""

        ' Open file dialog to open Cal data excel file 
        Dim OpenFileDialog1 As New OpenFileDialog()
        OpenFileDialog1.Filter = "Excel files (*.xls)|*.xls"
        OpenFileDialog1.Title = "Select Excel file to load data from"

        ' If the user clicks the open file button, load the file
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            Try
                'Save the file path and name
                strFileName = OpenFileDialog1.FileName

            Catch ex As Exception
                MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try
        End If

        'Load Cell data from opened EXCEL file into cal program variables

        'Path and excelFile variables are used for relative addressing the excel template file
        Dim path As String = My.Application.Info.DirectoryPath
        Dim excelFile As String = IO.Path.Combine(path, strFileName)

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlWorkSheet2 As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        ' Open the MAC125 Calibration data template file
        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(excelFile)
        xlWorkSheet1 = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
        xlWorkSheet2 = CType(xlWorkBook.Sheets("sheet2"), Microsoft.Office.Interop.Excel.Worksheet)

        ' Populate the template file with MAC125 data

        pressure = CInt(xlWorkSheet1.Cells(4, 10))

        lblRange.Text = CType((xlWorkSheet1.Cells(4, 4)), String)

        txtRoom.Text = CType((xlWorkSheet1.Cells(9, 4)), String)
        lblPercent3.Text = CType((xlWorkSheet1.Cells(9, 5)), String)
        lblPercent4.Text = CType((xlWorkSheet1.Cells(9, 6)), String)
        lblPercent5.Text = CType((xlWorkSheet1.Cells(9, 7)), String)
        lblPercent6.Text = CType((xlWorkSheet1.Cells(9, 8)), String)
        lblPercent7.Text = CType((xlWorkSheet1.Cells(9, 9)), String)
        lblPercent8.Text = CType((xlWorkSheet1.Cells(9, 10)), String)

        txtWatlow1.Text = CType((xlWorkSheet1.Cells(10, 3)), String)
        txtWatlow2.Text = CType((xlWorkSheet1.Cells(10, 4)), String)
        txtWatlow3.Text = CType((xlWorkSheet1.Cells(10, 5)), String)
        txtWatlow4.Text = CType((xlWorkSheet1.Cells(10, 6)), String)
        txtWatlow5.Text = CType((xlWorkSheet1.Cells(10, 7)), String)
        txtWatlow6.Text = CType((xlWorkSheet1.Cells(10, 8)), String)
        txtWatlow7.Text = CType((xlWorkSheet1.Cells(10, 9)), String)
        txtWatlow8.Text = CType((xlWorkSheet1.Cells(10, 10)), String)

        txtDisplay1.Text = CType((xlWorkSheet1.Cells(11, 3)), String)
        txtDisplay2.Text = CType((xlWorkSheet1.Cells(11, 4)), String)
        txtDisplay3.Text = CType((xlWorkSheet1.Cells(11, 5)), String)
        txtDisplay4.Text = CType((xlWorkSheet1.Cells(11, 6)), String)
        txtDisplay5.Text = CType((xlWorkSheet1.Cells(11, 7)), String)
        txtDisplay6.Text = CType((xlWorkSheet1.Cells(11, 8)), String)
        txtDisplay7.Text = CType((xlWorkSheet1.Cells(11, 9)), String)
        txtDisplay8.Text = CType((xlWorkSheet1.Cells(11, 10)), String)

        btnRange0R0.Text = CType((xlWorkSheet1.Cells(14, 3)), String)
        btnRange2R0.Text = CType((xlWorkSheet1.Cells(14, 4)), String)
        btnRange4R0.Text = CType((xlWorkSheet1.Cells(14, 5)), String)
        btnRange6R0.Text = CType((xlWorkSheet1.Cells(14, 6)), String)
        btnRange8R0.Text = CType((xlWorkSheet1.Cells(14, 7)), String)
        btnRange10R0.Text = CType((xlWorkSheet1.Cells(14, 8)), String)
        btnRange12R0.Text = CType((xlWorkSheet1.Cells(14, 9)), String)
        btnRange14R0.Text = CType((xlWorkSheet1.Cells(14, 10)), String)

        lblEquation.Text = CType((xlWorkSheet1.Cells(15, 1)), String)

        btnRange0R1.Text = CType((xlWorkSheet1.Cells(15, 3)), String)
        btnRange2R1.Text = CType((xlWorkSheet1.Cells(15, 4)), String)
        btnRange4R1.Text = CType((xlWorkSheet1.Cells(15, 5)), String)
        btnRange6R1.Text = CType((xlWorkSheet1.Cells(15, 6)), String)
        btnRange8R1.Text = CType((xlWorkSheet1.Cells(15, 7)), String)
        btnRange10R1.Text = CType((xlWorkSheet1.Cells(15, 8)), String)
        btnRange12R1.Text = CType((xlWorkSheet1.Cells(15, 9)), String)
        btnRange14R1.Text = CType((xlWorkSheet1.Cells(15, 10)), String)

        mac125Voltage = CByte(xlWorkSheet1.Cells(25, 6))

        mac125Model = CByte(xlWorkSheet1.Cells(27, 2))

        mac125Status = CByte(xlWorkSheet1.Cells(36, 2))

        filterBlowBack = CBool(xlWorkSheet1.Cells(21, 8))

        caseCool = CBool(xlWorkSheet1.Cells(23, 8))

        caseHeater = CBool(xlWorkSheet1.Cells(25, 8))

        digitalDisplay = CBool(xlWorkSheet1.Cells(27, 8))

        mac125SerialNumber = CType(xlWorkSheet1.Cells(28, 6), String)
        mac125HtProbeSerialNumber = CType(xlWorkSheet1.Cells(30, 6), String)
        rangeModuleSerialNumber = CType(xlWorkSheet1.Cells(32, 6), String)
        rmaNumber = CType(xlWorkSheet1.Cells(34, 6), String)
        purchaseOrderNumber = CType(xlWorkSheet1.Cells(36, 6), String)

        dateTested = CType(xlWorkSheet1.Cells(33, 10), String)

        xlWorkSheet1.Cells(36, 10) = testedBy = CType(xlWorkSheet1.Cells(36, 10), String)


        xlWorkBook.Close()
        xlApp.Quit()

        xlWorkBook = Nothing
        xlWorkSheet1 = Nothing
        xlWorkSheet2 = Nothing

    End Sub
 
Upvote 0 Downvote
Cells property returns a Range object, from that you can get the Value property.
 
Upvote 0 Downvote

Similar threads

H
  • Question
Question Data Export - advice needed
Replies
1
Views
510
jdelano
J
A
  • Question
How to Remove an SQLData Object From Memory
Replies
11
Views
671
Alan Liddle
A
A
  • Question
Subtract the quantity of partsDGV <- bound from DGV3 <- unbound
Replies
3
Views
366
jdelano
J
L
  • Question
Problems with Filestream
Replies
3
Views
535
raulrabbit56
R
saichinnu1852
  • Question
Oracle CPQ to snowflake
Replies
0
Views
278
saichinnu1852
saichinnu1852

Share this page

Latest posts

Back
Top