Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Solved
Error adding worksheet to workbook
Posted on 2010-09-08
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)
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
2
10 Comments
Active 4 days ago
That's not how add works. You need to copy the created sheet to the other workbook:
ws.copy wb.Worksheets(1)
for example.
ws.copy wb.Worksheets(1)
for example.
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.Workshe
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.Workshe
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:
Active today
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.
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.
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
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
Active today
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.
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.
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
The new question is posted at
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_26463193.html
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_26463193.html
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.
Suggested Courses
Course of the Month11 days, 12 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.