Error getting data from Excel to VB.NET form

Posted on 2012-08-29
Last Modified: 2012-09-01
I'm trying to pupulate a few text fields in my form from excel but I'm getting the following error message:

System.Runtime.InteropServices.COMException was unhandled
  Message=Exception from HRESULT: 0x800A03EC
       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()

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))


        '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


        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

            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 = Nothing
    End Sub

It will be great if somebody could help.

Thank you in advance
Question by:stepcounter
    LVL 35

    Accepted Solution

    Shouldn't this
    xlWorkSheet.Cells.Range(16, 3).Value

    Open in new window

    xlWorkSheet.Cells(16, 3).Value

    Open in new window

    ? (3 times)

    Author Closing Comment

    Thank you. It's so silly of me. I just coulodn't see the Forest from the

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now