Add worksheet to workbook of calling procedure

[Alan Beban]

In a general module of Workbook1 I am calling a Sub procedure in
WorkbookAddIn. I want the WorkbookAddIn Sub to add a worksheet to
Workbook1. Can that be done?

Thanks,
Alan Beban

 

[Bob Phillips]

Workbooks("Workbook1_Name").Worksheets.Add

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tim Zych]

This worked for me:

In the addin:
Function AddSheet(ByVal wkbToAddTo As Workbook) As Worksheet
Set AddSheet = wkbToAddTo.Worksheets.Add
End Function

In the caller:
Sub Test()
Dim wks As Worksheet
Set wks = Application.Run("Addin.xla!AddSheet", ThisWorkbook)
MsgBox "Sheet added is " & wks.Name & " to " & wks.Parent.Name
End Sub

 

[Alan Beban]

Workbooks("Workbook1_Name").Worksheets.Add

Thanks, Bob. But the Add-In Sub doesn't know the name of the workbook of
the calling Sub.

Alan

 

[Alan Beban]

This worked for me:

In the addin:
Function AddSheet(ByVal wkbToAddTo As Workbook) As Worksheet
Set AddSheet = wkbToAddTo.Worksheets.Add
End Function

In the caller:
Sub Test()
Dim wks As Worksheet
Set wks = Application.Run("Addin.xla!AddSheet", ThisWorkbook)
MsgBox "Sheet added is " & wks.Name & " to " & wks.Parent.Name
End Sub

Thanks, Tim. But like Bob Phillips' suggestion, this requires the
calling procedure to supply the code for adding the worksheet, and the
user won't know that this is required when calling the Add-In.

Since I posted I played with it some more, and the solution makes me
feel somewhat silly for posting. In the AddiIn Sub, simply

Worksheets.Add with no qualifiers seems to work fine to add the
worksheet in the calling Sub's workbook. I had coded
ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
Add-In workbook.

Thanks for responding.

Alan Beban

 

[Jim May]

In the Active Code (WB1) you can refer to WB1 as Thisworkbook.

So your addin procedure should refer (in some way) to the phrase:

Thisworkbook.Worksheets.Add

HTH

 

[Tim Zych]

I don't understand..why not just encapsulate the complexity. ThisWorkbook is
always the caller, and that macro can be inserted into the wrapper that
accesses the addin, so there's nothing special for the caller / user to do
then.

Worksheets.Add with no qualifiers seems to work fine to add the worksheet
in the calling Sub's workbook. I had coded
ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
Add-In workbook.

Now I'm confused. Maybe at this point I have no clue about what you are
trying to do. When I do either Worksheets.Add or
ActiveWorkbook.Worksheets.Add, it always adds it to the active workbook.
What if the caller is hidden or inactive? My approach is more robust and
exact. I'm very nitpicky about unqualified references.

 

[Bob Phillips]

Alan,

Can you use

Activeworkbook.Worksheets.Add

If not, somewhere along the line you will have to capture the workbook and
save it in a variable.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan Beban]

Alan,

Can you use

Activeworkbook.Worksheets.Add

If not, somewhere along the line you will have to capture the workbook and
save it in a variable.

That's what I started with, and it added the worksheet to the Add-In
workbook rather than the workbook of the calling procedure. I have since
posted saying that if I use

Worksheets.Add, without any qualifier

in the Add-In Sub, it adds the worksheet to the workbook of the calling
Sub, which solves my problem. But I still need to get my head around Tim
Zych's most recent post in this thread.

Thanks for responding,
Alan

 

[Alan Beban]

I don't understand..why not just encapsulate the complexity. ThisWorkbook is
always the caller, and that macro can be inserted into the wrapper that
accesses the addin, so there's nothing special for the caller / user to do
then.




Now I'm confused. Maybe at this point I have no clue about what you are
trying to do. When I do either Worksheets.Add or
ActiveWorkbook.Worksheets.Add, it always adds it to the active workbook. . . .

???What is the active workbook in your statement above? The workbook of
the calling Sub or of the Add-In Sub?

Conceptualize a 3D array as a rectangular solid resting on the xy-plane
(analogous to the rows columns plane of a worksheet), with the third
dimension projecting toward the viewer)

I have a Sub procedure in an Add-In workbook named "ArrayFunctions". The
sub is Sub Save3DInWorksheet(inputArray, Optional ByVal Orientation As
String = "XY). (To ease this presentation, I will ignore the fact that
the orientation of the output might by "XZ" or "YZ"; i.e., planes
orthogonal to the xy-plane). Assuming, for illustration, an array that
has been declared with

ReDim arr(1 to 2, 1 to 3, 1 to 4) and loaded,

the Add-In Sub deposits the 24 elements of arr onto a worksheet in the
following form, with the index numbers of arr representing the elements:

1,1,1 1,2,1 1,3,1
2,1,1 2,2,1 2,3,1

1,1,2 1,2,2 1,3,2
2,1,2 2,2,2 2,3,2

1,1,3 1,2,3 1,3,3
2,1,3 2,2,3 2,3,3

1,1,4 1,2,4 1,3,4
2,1,4 2,2,4 2,3,4

The calling Sub, in a workbook named "test23D", will be, as an illustration

Sub test1()
Dim w
ReDim w(1 To 2, 1 To 3, 1 To 4)
For i = 1 To 2: For j = 1 To 3: For k = 1 To 4
w(i, j, k) = i + 2 * j + 3 * k
Next: Next: Next
Save3DInWorksheet w
End Sub

In the Add-In Sub is included the following snippet to provide the
destination for the output; i.e., the elements of the 3D array:

ReDim sName(1 To 3)
sName(1) = "XY"
sName(2) = "XZ"
sName(3) = "YZ"
On Error Resume Next
For q = 1 To 3
Set wSheet = ActiveWorkbook.Sheets(sName(q))
If Not Err = 0 Then
Worksheets.Add
ActiveSheet.Name = sName(q)
Err = 0
End If
Next

That snippet checks the Add-In Sub and, if the worksheets don't exist in
it, adds them to the Add-In Sub. I'm using xl2002. The result is the
same if I substitute ThisWorkbook for ActiveWorkbook.

But if I omit ActiveWorkbook in the above snippet, it checks for the
existence of the sheets in the workbook of the calling Sub, i.e.,
test23D, and if they don't exist there, it adds them to test23D, which
is the desired result.

Thanks again for spending time on this,
Alan Beban

 

[Tim Zych]

You lost me when you said:

"planes orthogonal to the xy-plane"

but that's ok..the environment here is more relevant to me.

Worksheets.Add
ActiveSheet.Name = sName(q)

That snippet checks the Add-In Sub and, if the worksheets don't exist in
it, adds them to the Add-In Sub. I'm using xl2002. The result is the same
if I substitute ThisWorkbook for ActiveWorkbook.

But if I omit ActiveWorkbook in the above snippet, it checks for the
existence of the sheets in the workbook of the calling Sub, i.e., test23D,
and if they don't exist there, it adds them to test23D, which is the
desired result.

Wow. For me

Worksheets.Add
or
ActiveWorkbook.Worksheets.Add

add new sheet(s) to the active workbook, and never to the Addin.xla,
assuming the addin has the IsAddin property set to True or is hidden.
ThisWorkbook.Worksheets.Add adds the sheets only to the Addin.xla. Just to
confirm, you have different results?

Here is how I imagine it. Am I missing something about your configuration?

Sub WksAddTest()
Dim ai As Workbook
Set ai = Workbooks.Add(1)
ai.IsAddin = True
ai.SaveAs "C:\FriTest.xla"

Dim wkb As Workbook
Set wkb = Workbooks.Add(1)
wkb.SaveAs "C:\test23D_999.xls"

Worksheets.Add ' Adds to test23D_999.xls
ActiveWorkbook.Worksheets.Add ' Adds to test23D_999.xls
ThisWorkbook.Worksheets.Add ' Adds to the WksAddTest() workbook
End Sub

Granted your code is in the actual addin, but I get similar results while
trying this in an addin or a regular workbook.

As for the rest, I better understand what you are trying to do. Your first
post and your first reply to me left open in my interpretation the
possibility that you were in the process of creating a multi workbook
solution and could add a parameter, while this detail makes it much clearer
to me (except for the orthogonal part) that your existing structure is
better left unmodified.

However, I have corrected macros such as what follows, well not exactly like
it, but similar in approach, which is what prompted me to go on about my
fully qualified sheet/explicit book reference diatribe.

Basically, the macro emulates several steps that would be performed in
different pieces, manually or programmatically, and ends up deleting the
important workbook rather than the disposable one. Call me a defensive
developer, but stuff like this exists out there (!).

Sub WorstCasePossibility()

Dim wkb As Workbook

' Create a temporary workbook

Set wkb = Workbooks.Add(1)
wkb.SaveAs "C:\MyTempWkb.xls"

' Somehow, this gets set, either
' by a macro or advanced user...

ActiveWindow.Visible = False

' ...and changes are saved

wkb.Save
wkb.Close False
Set wkb = Nothing

' Now let's create my big important
' workbook and save it

Set wkb = Workbooks.Add(1)
wkb.Worksheets(1).Name = "My real important data."
wkb.SaveAs "C:\MyImportantWkb.xls"
Set wkb = Nothing

' Months go by, and my real important workbook
' now has all of my important data in it

' Let's open the temp workbook and do some
' additional data import into my important workbook

Workbooks.Open "C:\MyTempWkb.xls"

' Now my temp workbook is open, so
' programmatically import data from it into
' my big important workbook

' The macro is done with the temp.xls, so let's get rid of it

' Let's just use the ActiveWorkbook because we just
' opened it, so we know which one it is

ActiveWorkbook.ChangeFileAccess (xlReadOnly)
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

For Each wkb In Application.Workbooks
Debug.Print wkb.Name
Next

End Sub

 

[Alan Beban]

You lost me when you said:

"planes orthogonal to the xy-plane"

OK--planes perpendicular to the xy-plane

Wow. For me

Worksheets.Add
or
ActiveWorkbook.Worksheets.Add

add new sheet(s) to the active workbook, and never to the Addin.xla,
assuming the addin has the IsAddin property set to True or is hidden.
ThisWorkbook.Worksheets.Add adds the sheets only to the Addin.xla. Just to
confirm, you have different results?

No, I don't (although I said 3 times that I do!). Turns out that prior
to running the calling Sub I had manually activated the worksheet in the
Add-In and never reactivated a worksheet in my testing workbook before
running the code. So---Never miiind

Thanks for spending time on my pointless posting; sorry.

Regards,
Alan