Error getting data from Excel to VB.NET form

I'm trying to pupulate a few text fields in my vb.net form from excel but I'm getting the following error message:

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message=Exception from HRESULT: 0x800A03EC
  Source=""
  StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.get_Range(Object Cell1, Object Cell2)
       at AdmissionsAid.frmBase.btnOSHCfees_Click(Object sender, EventArgs e) in E:\AdmissionsAid\AdmissionsAid\FormBase.vb:line 13926
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at AdmissionsAid.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


Here is the code I'm using:

Private Sub btnOSHCfees_Click(sender As System.Object, e As System.EventArgs) Handles btnOSHCfees.Click

        'Dim premiumSingle As Double = 33.0
        'Dim premiumDualFamily As Double = 83.0
        'Dim premiumMultiFamily As Double = 165.0
        'Dim amountToPay As Double
        'Dim addToPremium As Double

        'Dim m As Integer = MonthDifference(CDate(txtVL_OSHC_start.Text), CDate(txtVL_OSHC_end.Text))

        'MessageBox.Show(m)

        'If rbSingle.Checked = True Then

        '    amountToPay = m * premiumSingle

        '    txtOSHCfee.Text = Format("AUD$ " & amountToPay.ToString)
        'End If

        'If rbDual.Checked = True Then
        '    amountToPay = m * premiumDualFamily

        '    txtOSHCfee.Text = Format("AUD$ " & amountToPay.ToString)
        'End If

        'If rbFamily.Checked = True Then
        '    amountToPay = m * premiumMultiFamily

        '    txtOSHCfee.Text = Format("AUD$ " & amountToPay.ToString)
        'End If

        'NEW APPROACH

        Dim xlApp As excel.Application
        Dim xlWorkBook As excel.Workbook
        Dim xlWorkSheet As excel.Worksheet

       

        xlApp = New excel.Application


        'variables fro the data from excel

        Dim VLC_Start As Date
        Dim VLC_End As Date
        Dim strVLC_End As String
        Dim strVLC_Start As String

        Dim sngle As String
        Dim dual As String
        Dim family As String
        Dim amount As Double


        '-----------------------------------------------------------------------------------------------
        'Let the application find the file directly in the directory using a full path
        '-----------------------------------------------------------------------------------------------
        Try

            xlWorkBook = xlApp.Workbooks.Open("E:\A_Work\Worksheets\OSHC\OSHC VLC Calculator - Essentials Cover 24 April 2012 (1).xlsm", Nothing, False)
            xlWorkSheet = xlWorkBook.Sheets("OSHC Visa Length Cover")
        Catch ex As Exception
            MessageBox.Show("The VL_OSHC Calculator not found.")
        End Try
        xlApp.Visible = True


        xlWorkSheet.Cells(4, 3) = txtGU_Program_SD.Text
        xlWorkSheet.Cells(4, 4) = txtGU_Program_FD.Text

        VLC_Start = CDate(xlWorkSheet.Cells.Range(16, 3).Value)
        VLC_End = CDate(xlWorkSheet.Cells.Range(16, 4).Value)

        strVLC_Start = CStr(VLC_Start)
        strVLC_End = CStr(VLC_End)

        txtVL_OSHC_start.Text = strVLC_Start
        txtVL_OSHC_end.Text = strVLC_End


        If rbSingle.Checked = True Then
            sngle = InputBox("Enter the amount for Single OSHC Policy.")
            amount = xlWorkSheet.Cells().Range(25, 2).Value
            txtOSHCfee.Text = CStr(amount)
            'txtOSHCfee.Text = "AUD$" & sngle & ".00"
        ElseIf rbDual.Checked = True Then
            dual = InputBox("Enter the amount for Dual OSHC Policy.")

            txtOSHCfee.Text = "AUD$" & dual & ".00"
        ElseIf rbFamily.Checked = True Then
            family = InputBox("Enter the amount for Multiple Family OSHC Policy.")
            txtOSHCfee.Text = "AUD$" & family & ".00"
        End If



        xlWorkSheet = Nothing
        xlWorkBook = Nothing
        xlApp.Quit()
        xlApp = Nothing
    End Sub

It will be great if somebody could help.

Thank you in advance
stepcounterAsked:
Who is Participating?
 
Robert SchuttSoftware EngineerCommented:
Shouldn't this
xlWorkSheet.Cells.Range(16, 3).Value

Open in new window

be:
xlWorkSheet.Cells(16, 3).Value

Open in new window

? (3 times)
0
 
stepcounterAuthor Commented:
Thank you. It's so silly of me. I just coulodn't see the Forest from the
Tree.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.