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.InteropServ ices.COMEx ception was unhandled
ErrorCode=-2146827284
Message=Exception from HRESULT: 0x800A03EC
Source=""
StackTrace:
at System.RuntimeType.Forward CallToInvo keMember(S tring memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.E xcel.Range .get_Range (Object Cell1, Object Cell2)
at AdmissionsAid.frmBase.btnO SHCfees_Cl ick(Object sender, EventArgs e) in E:\AdmissionsAid\Admission sAid\FormB ase.vb:lin e 13926
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnClick( EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at AdmissionsAid.My.MyApplica tion.Main( String[] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain._nExecute Assembly(R untimeAsse mbly assembly, String[] args)
at System.AppDomain.ExecuteAs sembly(Str ing assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly()
at System.Threading.ThreadHel per.Thread Start_Cont ext(Object state)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
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(txtV L_OSHC_sta rt.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\Work sheets\OSH C\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.Ra nge(16, 3).Value)
VLC_End = CDate(xlWorkSheet.Cells.Ra nge(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
System.Runtime.InteropServ
ErrorCode=-2146827284
Message=Exception from HRESULT: 0x800A03EC
Source=""
StackTrace:
at System.RuntimeType.Forward
at Microsoft.Office.Interop.E
at AdmissionsAid.frmBase.btnO
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at AdmissionsAid.My.MyApplica
at System.AppDomain._nExecute
at System.AppDomain.ExecuteAs
at Microsoft.VisualStudio.Hos
at System.Threading.ThreadHel
at System.Threading.Execution
at System.Threading.Execution
at System.Threading.ThreadHel
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(txtV
'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
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.Ra
VLC_End = CDate(xlWorkSheet.Cells.Ra
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(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tree.