Solved

Error adding worksheet to workbook

Posted on 2010-09-08
10
537 Views
Last Modified: 2012-05-10
Hello,
I am trying to create function that will return a worksheet and add it to a newly created workbooks.  In the following code, I loop through a listview and populate a list object which I subsequentlly pass through Function Generate Worksheet.  When I run the code, I see the new sheet being populated but I am getting a Exception from HRESULT: 0x800A03EC error on line wb.Worksheets.Add(ws)
Dim dpl As New List(Of DatasetPair)



        'Try

        Me.Cursor = Cursors.WaitCursor

        Dim intSheetCount As Integer = 0

        For i As Integer = 0 To lvMatchedDvid.Items.Count - 1

            Dim dp As New DatasetPair

            dp.DataVersionIdOne = lvMatchedDvid.Items(i).Text.ToString

            dp.DataVersionIdTwo = lvMatchedDvid.Items(i).SubItems(1).Text.ToString

            intSheetCount += 1

            dp.SheetId = intSheetCount

            dpl.Add(dp)

        Next





        Dim wb As Excel.Workbook = Utilities.CreateWorkbook()





        For Each dp As DatasetPair In dpl

            Dim ws As New Excel.Worksheet

            ws = GenerateWorksheet(dp)

            MessageBox.Show(ws.Rows.Count.ToString)

            wb.Worksheets.Add(ws)

        Next

Open in new window

0
Comment
Question by:chtullu135
  • 5
  • 3
  • 2
10 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33634622
That's not how add works. You need to copy the created sheet to the other workbook:
ws.copy wb.Worksheets(1)
for example.
0
 

Author Comment

by:chtullu135
ID: 33637428
Hello,
I've been trying the following code, specifically the copy code but I keep getting an error on the following lind.  I've included the error message details
ws.Copy(Before:=wb.Worksheets(1))

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.Copy(Object Before, Object After)

       at BADS.frmMultplePBTMR.btnGenerateReport_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmMultplePBTMR.vb:line 254

       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.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)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: 

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33637497
I have no idea what that means, I'm afraid, as I don't know a lot about .Net.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33639219
If you notice there are 2 arguments for the Copy method - Before and After, you only seem to be supplying the Before.

I don't know how VB.NET handles missing arguments, but I know C#.NET can be a bit picky - in earlier versions you have to provide every argument or use Missing.

That's about 13 (or is it 15) arguments for something as 'simple' as opening a workbook.

Also Before:= is a named argument, again I don't know how VB.NET (or C#.NET) handles them.

The first thing I would try would be to remove 'Before:='.

Then I would try removing all the argument(s) leaving just (), not quite sure how that would work - in VBA if you don't have any arguments then a new workbook is created with just the copied sheet in it.

Another thing I would try would be to supply both the before and after arguments but I don't quite know how that would work - might be tricky telling the code you want to copy before one worksheet and after another.

Anyway, try those - in no particular order mind you.

I'll check it out myself later.
0
 

Author Comment

by:chtullu135
ID: 33641707
I was able to get rid of the error by opening both workbooks into the same instance of Excel.  However I am getting a cast error at  ws.Copy(wbkDest.Worksheets(wbkDest.Worksheets.Count - 1)).  I will post a new question regarding this error

 For Each dp As DatasetPair In dpl

            Dim excelApp As New Excel.Application

            Dim ws As New Excel.Worksheet

            ws = GenerateWorksheet(dp)

            ws.SaveAs(wb.Path.ToString)





            Dim strSourceFilePath As String

            strSourceFilePath = wb.Path.ToString & "\" & wb.Name.ToString

            MessageBox.Show(strSourceFilePath)



            'wb.Close()

            Dim wbkDest As Excel.Workbook = excelApp.Workbooks.Open(strFilePath)





            Dim wbkSource As Excel.Workbook = excelApp.Workbooks.Open(strSourceFilePath)

            'ws.Copy(Before:=wbkDest.Worksheets.Count - 1)

            ws.Copy(wbkDest.Worksheets(wbkDest.Worksheets.Count - 1))



           

        Next

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:Norie
ID: 33641843
Well it might have helped to know there was multiple instances of Excel kicking around.:)

Why do you have more than 1 in the first place?

I can't see anything in the posted code suggesting that would be needed.

Even if you do need more than one instance, are you sure you need one for every iteration of the loop?

Mind you I am just going by what code you've posted - there seems to be a few classes/functions being called/whatever.

That might even be where the cast error is originating, though I can't seem to see any casting in the code.
0
 

Author Comment

by:chtullu135
ID: 33642279
I removed the mutliple instances of excel after doing some research.  As far as the cast error is concerned,  all I am attempting to do is copy a worksheet to the location defined in the new workbook as worksheets.count - 1
0
 

Author Comment

by:chtullu135
ID: 33642396
0
 
LVL 33

Expert Comment

by:Norie
ID: 33642677
What you are trying to do isn't quite relevant to the cast thing as far as I know, it's how you are trying to do it.

Anyway I'll take a look at the other thread.
0
 

Author Comment

by:chtullu135
ID: 33642758
Since the question had already been closed, I wanted to be able to award points
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now