Question about worksheets in an AddIn

[42N83W]

Can the worksheets in an AddIn be copied to another workbook? I'd test
this, but I really don't want to convert my .xls to an .xla just yet.

Windows XP Pro
Excel 2002

Thank!

-gk-

=================================================
The creative act is not the province of remote oracles or rarefied geniuses
but a transparent process that is open to everyone.
-Greg Kot in Wilco Learning How To Die-

 

[Jim Rech]

Can the worksheets in an AddIn be copied to another workbook?

Certainly. Just think of an add-in as a workbook you can't see. Btw, to
change a workbook to an add-in and back is just a matter of flipping its
IsAddin property (in the Properties windows in the VBE). You don't have to
do a Save As.

 

[42N83W]

Certainly. Just think of an add-in as a workbook you can't see. Btw, to
change a workbook to an add-in and back is just a matter of flipping its
IsAddin property (in the Properties windows in the VBE). You don't have
to do a Save As.

Follow up:

(1) How then would I copy a sheet from the AddIn to a newly created
workbook?

i.e. The user opens Excel and turns on the desired AddIn (if it is not
already on). This creates a new menu item. The user clicks on the new menu
and selects the first option. This will a) create a new workbook, and
should b) move four sheets from the AddIn to the newly created workbook.

(2) I am not seeing the IsAddin in the Properties window in the VBE. Do you
mean to change the property programatically? Where exactly is this
property?

Getting closer, but still a ways to go. Thanks!

-gk-

 

[Bob Phillips]

Dim oldWb As Workbook
Dim newWb As Workbook

Set oldWb = ThisWorkbook
oldWb.Worksheets("Sheet1").Copy
Set newWb = ActiveWorkbook
oldWb.Worksheets("Sheet2").Copy Before:=newWb.Worksheets(1)
oldWb.Worksheets("Sheet3").Copy Before:=newWb.Worksheets(1)
oldWb.Worksheets("Sheet4").Copy Before:=newWb.Worksheets(1)

The IsAddin property is a property of the workbook , so select the
ThisWorkbook module of your workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim wkbk As Workbook

Set wkbk = Workbooks.Add(1)

With ThisWorkbook
.IsAddin = False
.Worksheets("sheet1").Copy _
before:=wkbk.Worksheets(1)
.Worksheets("sheet2").Copy _
before:=wkbk.Worksheets(1)
.Worksheets("Sheet3").Copy _
before:=wkbk.Worksheets(1)
.IsAddin = True
End With
End Sub

You may want to consider creating a new workbook that's set up exactly the way
you want--save it as a nice template and distribute it with your addin. Then
you can just create a new workbook based on that template workbook.

Option Explicit
Sub testme()
Dim wkbk As Workbook
Set wkbk = Workbooks.Add(template:=ThisWorkbook.Path & "\" & "book2.xls")
End Sub