add-in macro strange errors--method sheets of object workbook fail

[Guest]

Help! I'm a macro newbie and this is driving me nuts!

I've tried everything, but I still get erratic behavior from the macro
below. The macro copies a worksheet from a file and inserts as the first
worksheet in the active workbook. I want to distribute the macro to other
Excel users at work, so I'd like to make it as bullet-proof as I can.

If I open Excel and immediately run the macro I get this error on the third
line from the bottom of macro:

Run-time error '-2147221080(800401a8)' method 'sheets' of object '_workbook'
failed.

If I open a previously saved file then run the macro it works fine. It
seemed to run fine when I tested it before saving it as an .xla file.

Any suggestions? There must be some way to avoid this error, or at least
trap it. I've been working on this a few days now with no success, and it's
driving me crazy!

Thanks in advance for the help!

================================
Sub AddDocumentation()

Dim wbDOC As Workbook
Dim CurrentWb As Workbook

Set CurrentWb = ActiveWorkbook


Set wbDOC = Workbooks.Open(Filename:="C:\NRGSCExcelUtil\Documentation
Template v7 10 27 04.xls")
wbDOC.Sheets("General Documentation").Copy Before:=CurrentWb.Sheets(1)
ActiveSheet.Name = "General Documentation"
wbDOC.Close SaveChanges:=False
End Sub

==========================================

 

[E Oveson]

Hi David,
It looks like the error is happening because workbooks.open() method closes
the default open workbook (unless it has been dirtied and/or saved), and so
when you reference currentWb later in your code it is null.

Rather than do some kludge like save that default workbook first or dirty it
some way, you could try this: save the "General Documentation" sheet in a
new workbook, and then in the codebehind page for "thisworkbook" put in
something like:

Private Sub Workbook_Open()
Workbooks.Add
Set wkbk2 = ActiveWorkbook

ThisWorkbook.Sheets("General Documentation").Copy
before:=wkbk2.Sheets(1)
wkbk2.ActiveSheet.Name = "General Documentation"
End Sub

And then save that workbook as your add-in (xla). After installing that
add-in, any time you boot XL, the documentation sheet will be added to the
default workbook. Or you could change it so that it's not in the
workbook_open() event to fit your needs.

-Erik

 

[Guest]

Hi Erik,

Thanks, that fits that workbooks.open() closes the default workbook. Instead
of automatically adding the general documentation sheet when the user opens
excel, I'd rather just exit the macro without doing anything if no default
worksheet is open, but I don't how to test for that.

Any thoughts?

Thanks much!

David

 

[E Oveson]

So you basically want to add the documentation sheet to any new workbook a
user opens (but if they happen to open a previously saved workbook you would
not want to add the documentation)? If that's the case, you might want to
make a default Excel template (Book.xlt placed in C:\Program Files\Microsoft
Office\OFFICE(version)\XLSTART) with the documentation sheet. Then any new
workbook the user adds will come with that documentation sheet (and any
other customizations/preferences you wanted to have for it). You could also
put whatever other macros you might have into that default template
Book.xlt. If I'm understanding your scenario correctly, this sounds like it
might be a good option.

-Erik