Link to home
Start Free TrialLog in
Avatar of stepcounter
stepcounter

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stepcounter
stepcounter

ASKER

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