We help IT Professionals succeed at work.
Get Started

Populating Excel Columns and rows from VB.net

677 Views
Last Modified: 2012-06-27
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
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE