[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error getting data from Excel to VB.NET form

Posted on 2012-08-29
2
Medium Priority
?
685 Views
Last Modified: 2012-09-01
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
0
Comment
Question by:stepcounter
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 1500 total points
ID: 38344574
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
 

Author Closing Comment

by:stepcounter
ID: 38358273
Thank you. It's so silly of me. I just coulodn't see the Forest from the
Tree.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

872 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