We help IT Professionals succeed at work.

Populating Excel Columns and rows from VB.net

Hello,
I am trying to populate an the following columns in an excel spreadsheet from vb.net
Budget Year,
Policy Case,
Budget Case,
Version
I am deriving these value via a split function applied to the dataversionid column.  When I run the program, I keep getting the following error.  I'm not sure what is causing it.  Thanks

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message="Exception from HRESULT: 0x800A03EC"
  Source="Microsoft.Office.Interop.Excel"
  StackTrace:
       at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
       at BADS.frmTechnologyBreakdown.btnExportToExcel_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmTechnologyBreakdown.vb:line 374
       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(Int32 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 System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at BADS.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

intRangeLine = 4
                    'Iterate through the datasource
                    For Each r As DataRow In myDataTable.Rows
                        If intRangeLine > 4 Then
                           
                            For c As Integer = 0 To 5
                                'Assign the value from the datasource to the range
                                If c = 0 Then
                                    strDVID = r(c).ToString
                                    arrDVID = Split(strDVID, "-")

                                    Dim b As New Budget
                                    strBudgetYear = "FY" & (arrDVID(0).ToString).Substring(2)
                                    strPolicyCase = arrDVID(3).ToString
                                    strBudgetCase = arrDVID(4).ToString
                                    b = BudgetDB.GetSelectedBudgetName(strBudgetCase)
                                    strBudgetCase = b.Description.ToString
                                    intVersion = Convert.ToInt32(arrDVID(5))
                                    Continue For
                                End If
                                Select Case c
                                    Case 1
                                        'aWorkSheet.Cells.Range(intRangeLine, c + 1).Value = strBudgetYear
                                        aWorkSheet.Range(intRangeLine, c + 1).Value = strBudgetYear
                                End Select
                                'aRange.Cells(intRangeLine, c + 1) = r(c).ToString
                            Next
                        Else
                            intRangeLine += 1
                        End If
                        intRangeLine += 1
                    Next

Open in new window

SampleOutput.xlsx
Comment
Watch Question

check if the range is available within worksheet. it could be a range issue. put some debug.writeline statement.. that should point to root cause.