Excel API Sheets.Move trashes workbooks

Any ideas on how to fix this or make it work correctly?

I have 2 workbooks.

1 is the workbook that counts many worksheets

the other is a workbook with 1 worksheet.

I want to move the workbook that contains 1 worksheet into the other workbook which has alot of worksheets, but when i do this it seems to trash the workbook that contains many sheets, in that i cannot access any properties of the workbook instance, it gives me COMException, and thats it.

Before I move it, its fine but after, it gives me COMException when accessing the application property or any other properties but DOES succeed in moving....

You shouldnt be using the child application property of Excel. Also, your terminology is confused with workbook and worksheet. A workbook contains one or more worksheets. A worksheet can only be a child/contained in a workbook.

So it sounds like you want to move a sheet from one workbook into another?

Thanks, sorry, it's been a long hard day

I think i fixed it, instead of MOVE, did a COPY and that seems to fix the problem. I can get hold of the InputWorkbook (the workbook that contains many worksheets) after doing a copy, but not after a move.

Oh also, if your sheet to be "moved" is the only sheet in the workbook then it will fail too because you always need at least one worksheet in a workbook at all times.

Thanks. thats probably why.

a quick one....

how can I find a worksheet thats been copied from a workbook to another by the name of that worksheet?

Yes, you can use the Sheets collection by simply referencing the name of the sheet.

oBook.Sheets("My sheet").Activate

or such. If you want to determine if it exists first before that you can loop through the Sheets collection looking for a match.

If oBook.Sheets(x).Name = "My Sheet" Then
'Found
Exit For
End If

Thanks

I did try this but this did not work. It just threw a COM Exception. I will try again later but wondering what else to do.

Whats the exception state?

Exception state:

{"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"}

Exception state:

{"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"}

But I have seen it copy the worksheet into the main workbook and can see the name of that worksheet being the exact same one as I extracted from its original workbook.

I see that the sheets collection remain the same of the "master" workbook even after copying over the sheet from the other workbook

fixed the problem. such a headache.

seemed that it was copying the main workbook to the other workbook, so it was doing it the other way around.

Doh! Well at least its working now ;)