Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Workbooks("Workbook1_Name").Worksheets.Add
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan Beban" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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 -- Tim Zych SF, CA "Alan Beban" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Bob Phillips wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Tim Zych wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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 "Alan Beban" wrote: Bob Phillips wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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. -- Tim Zych SF, CA "Alan Beban" wrote in message ... Tim Zych wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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" wrote in message ... Bob Phillips wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Bob Phillips wrote:
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Tim Zych wrote:
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 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
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 -- Tim Zych SF, CA "Alan Beban" wrote in message ... Tim Zych wrote: 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 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet to workbook of calling procedure
Tim Zych wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling a procedure from another workbook | Excel Programming | |||
Calling workbook and worksheet | Excel Programming | |||
Run procedure in Personal.xls which needs name of calling workbook | Excel Programming | |||
Call a procedure in the workbook from a worksheet | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming |