Copy a Sheet to an AddIn

[Neal Zimm]

Hi All,
I've been building an addin for a while that will have multiple users.
The user's workbook is referenced to the addin.

I'm far from an expert on the addin object but with Walkenbach's book
I've been exectuting code from the addin successfully.

I'm trying to have the addin hold 'template' sheets that can be added
to a user's workbook.

I know I can copy a range, and formats, but I ALSO want the template
to hold the vba code for selected sheet events like change and activa-
tion. This has worked for me when copying from another workbook (not an
addin workbook).

Example: (The called proc is in the addin.)
Private Sub Worksheet_Activate()
Call Mgr_Activate 'this is the line I want in the addin template sheet.
End Sub

The proc below shows my work to date for a development tool to get the
sheets into the addin. It works fine until the 'final' copy statements.

Is it possible to do what I want without using the VBE objects?
I'm nervous about trying to update a VBE module after looking
at the objects involved, especially what's probably a class
object for the sheet. I've next to no experience with classes.

My fallback position is to have a regular workbook from which to
copy if that is what's needed. I suppose I could also bring rm.xla
back to rm.xls status, and then re-install the addin but I'd prefer
not to do that time and again as new templates are needed.

Thanks,
Neal Z.


Sub A__Copy_SHEET_TO_XLA()
Const Title = "Copy WrkSht ** TO ** RM.XLA, "
Const Cr = vbCr
Const Cr2 = Cr & Cr
Const Tb = vbTab
Dim AIwbk As Workbook
Dim SourceWs As Worksheet
Dim Ix As Long
Dim AfterWs As Worksheet

'mainline start

' This loop did not show the name of my addin, why not?
' The addin was open at the time. See Set AIwbk below.
' this was an experiment, not part of my main problem.
' For Ix = 1 To AddIns.Count
' If vbYes = MsgBox("Quit ?", vbYesNo + vbDefaultButton2, _
' "Na: " & AddIns(Ix).Name Then
' Exit For
' End If
' Next Ix
' MsgBox "AddIn index " & Ix, , "AddIns Count " & AddIns.Count

Set SourceWs = ActiveSheet
Set AIwbk = Workbooks("rm.xla")

If bWsExistF(SourceWs.Name, AIwbk) Then
If vbNo = MsgBox(SourceWs.Name & " exists in " & AIwbk.Name _
& Cr2 & "Confirm Sheet Deletion, Click NO to Quit Copy", _
vbYesNo + vbDefaultButton2, Title & SourceWs.Name) Then

Exit Sub
End If 'the actual delete has yet to be implemented.
End If

Set AfterWs = AIwbk.Sheets(AIwbk.Sheets.Count)

If vbNo = MsgBox("Confirm Copy ?? " & SourceWs.Name _
& Cr2 & "FROM:" & Tb & SourceWs.Parent.Name _
& Cr & "TO:" & Tb & AIwbk.Name & " After: " & AfterWs.Name _
& Cr2 & "Click NO to Quit Copy", _
vbYesNo + vbDefaultButton2, Title & SourceWs.Name) Then

Exit Sub
End If

'SourceWs.Copy After:=AfterWs 'this line crapped out

AIwbk.Sheets.Add After:=AfterWs 'Sheet was added

'line below crapped out. I know I can copy cells from the source to
'to the addin, but that doesn't get me the 'copies' of the event calls
'for the template sheet.

'thought this might equate to a copy, guess not.

Set AIwbk.Sheets(AIwbk.Sheets.Count) = SourceWs

Exit Sub
'mainline end
End Sub

 

[Dave Peterson]

I removed all the stuff I didn't care about <vbg> and this worked ok:

Option Explicit
Sub A__Copy_SHEET_TO_XLA()

Dim AIwbk As Workbook
Dim SourceWs As Worksheet
Dim AfterWs As Worksheet

Set SourceWs = ActiveSheet
Set AIwbk = Workbooks("personal.xla")

Set AfterWs = AIwbk.Sheets(AIwbk.Sheets.Count)

AIwbk.IsAddin = False
SourceWs.Copy After:=AfterWs
AIwbk.IsAddin = True

End Sub

 

[Neal Zimm]

Dear Dave,
God damn amazing, all you have to do is say that the file is NOT an AddIn
??
Thanks, I'll try it very very soon.

Reminds me of the joke about 3 professors who wash up on an island after
a shipwreck.
3 cans of beer wash up with them.
The physicist takes a rock, smashes the top, loses half, and starts
drinking.
The engineer takes precise measurements and with a flick of his
fingernail opens his can.
They look over to the mathematician who just assumed the can was open,
drank it, and was already finished. Ba Da BOOM.

thanks again.
Neal