Copying Worksheets using Excel and VB .net?

Hello everyone, I have read up on this and I cannot get it working. I think i have spent over 15 hours trying to get it to copy a worksheet from one book to another. here is my code. Maybe you guys could tell me what is wrong with it. I am very frustrated, and it doesn't seem that difficult. Any help is appreciated. Thanks.

I would like to copy

Code:

pathTosheet is the path to the workbook. Everything open and closes fine.
When I run it though, it gives me some fatal errror. I am copying the first sheet of the tempSheet to the obook.
...
...
...

tempBook = tempExcel.Workbooks.Open(pathToSheet)
tempSheet = tempBook.Sheets(1)
osheet = obook.Sheets("sheet2")
tempSheet.Copy(after:=osheet)
tempbook.Close()

Hi Sleeper, welcome to the Forum. :)

We'll have to see more of your code I'm afraid. In particular, we would need to see how your variables are declared. For example is oSheet declared 'As Worksheet' I guess? Also, how is oBook first created?

You should also strongly consider using 'Option Strict On'. It will force you to utilize CType() at a number of locations and generally clarify which Type is in force as you make your code.

Lastly, we need to see how you are closing down your variables. I see 'tempbook.Close()', but are you also setting it and the other objects = Nothing?

Show us a bit more and hopefully we can get you going...

-- Mike

Sure thing, Thanks. I only pasted what I thought was important, but here is the entire thing just in case.


The general concept is that I have a master workbook, and many other workbooks. I want to copy all the other worksheets from the other workbooks onto this master sheet. tempbook is the other workbook, and obook is the master workbook.

so I am trying to copy all the worksheets from the many different workbooks, into "obook" which is the master workbook.


' Create temp workbook
Dim tempExcel As Excel.Application
Dim tempBook As Excel.Workbook
Dim tempSheet As Excel.Worksheet
tempExcel = New Excel.Application

' Create main workbook
' I create obook and osheet the same exact way as tempbook and tempsheet, but in another module
oExcel = New Excel.Application

' Hide alerts from both excel objects
oExcel.DisplayAlerts = False
tempExcel.DisplayAlerts = False

Dim MasterID, SheetID, counter As Integer
Dim pathToMaster, pathToSheet As String

MasterID = SheetNodeFunc.getIDFromName("Master")
pathToMaster = SheetNodeFunc.getPathFromID(MasterID) & "\" & SheetNodeFunc.getfilenameFromID(MasterID)
obook = oExcel.Workbooks.Open(pathToMaster)

For counter = 0 To (ArraySheetInfo.GetLength(0) - 1) Step 1
If counter = MasterID Then
counter = counter + 1
End If

pathToSheet = SheetNodeFunc.getPathFromID(counter) & "\" & SheetNodeFunc.getfilenameFromID(counter)
tempBook = tempExcel.Workbooks.Open(pathToSheet)
tempSheet = tempBook.Sheets(1)

osheet = obook.Sheets(ArraySheetInfo(counter).nameOfSheet)

tempSheet.Copy(after:=osheet)

tempExcel.Workbooks.Close()
mainFunc.UpdateProgressBar("Copying " & ArraySheetInfo(counter).nameOfSheet & " to Master")
Next counter

tempSheet = Nothing
tempBook = Nothing
tempExcel.Quit()
tempExcel = Nothing

osheet = Nothing
obook = Nothing
oExcel.Quit()
oExcel = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()

Ok, you cannot pass Objects like Worksheets or Ranges from one Excel Application Instance to another like this or you'll get Automation errors. In short, have only one instance, either 'oExcel' or 'tempExcel', but not both. This one instance (whichever you choose) can still have as many workbooks open as it wishes and accomplish all the operations that you need...

Start with correcting that and see if that does the trick? Otherwise, come on back. :)

Thanks Mike for your input. How would I only make one instance? How would I reference each workbook?

Ok, if you havn't given the Automating Office Programs with VB.Net (http://www.xtremevbtalk.com/showthread.php?t=160433) tutorial
a read, I would. It's not as intimidating as it is long. (That is, I hope it's clear.)

But something like this comes to mind:Dim xlApp as Excel.Application

Dim TempWB as Excel.Workbook
Dim TempWS as Excel.Worksheet

Dim MasterWB as Excel.Workbook
Dim MasterWS as Excel.Worksheet After this, then simply call 'xlApp = New Excel.Application' and take it from there...

But again, give the tutorial a read if you haven't yet.

-- Mike

Thanks for the help Mike, your the man. I guess that was my problem. I had two instances of Excel. It also made the program run faster too

-sleeper

Ok, if you havn't given the Automating Office Programs with VB.Net (http://www.xtremevbtalk.com/showthread.php?t=160433) tutorial
a read, I would. It's not as intimidating as it is long. (That is, I hope it's clear.)

But something like this comes to mind:Dim xlApp as Excel.Application

Dim TempWB as Excel.Workbook
Dim TempWS as Excel.Worksheet

Dim MasterWB as Excel.Workbook
Dim MasterWS as Excel.Worksheet After this, then simply call 'xlApp = New Excel.Application' and take it from there...

But again, give the tutorial a read if you haven't yet.

-- Mike

Ahhh... very nice to see. :)

I was fearful that there were more problems on the way... Glad this fixed it! :cool:

One more quick question though, the program still runs a bit slow, and i think that it is because throughout the program, I keep opening and closing the excel instance. I think my best bet would be to only open it once throughout the entire project, the only problem is that if the program crashes, it will leave a hanging instance of Excel.

Is there a way to make excel act like a child instance of my program so that way if my program crashes in any way possible, the excel program associated with mine will close as well?

I have tried to cover all loose ends by using numerous try and catch statements, but still, what if.. you know?

Thanks

Yes, I agree with you: keep one Instance open the whole time.

This does mean, however, that Crashes result in a Hanging Excel Instance that requires the use of the Task Manager to get rid of. There is no easy way around this. :(

I still think it's worth the trade-off though. And over time, your debugging will get better and so the risk of an unhandled exception will become smaller and smaller over time...

Sorry there's no easy way though.

alrighty, thanks for the help again

Sure... good luck! :)