navigating workbooks, accessing add-ins

[mattrumik]

question about navigating workbooks / add-ins:
we have a couple of subs distributing a 6000-line list (input-sheet) from one worksheet onto about 30 new sheets (output-sheets), which will be created in this process according to another worksheet (reference-sheet). The input-sheet and the newly created output-sheets are within the same workbook (WB1). During development the Modules and the reference sheet was also within the same workbook (WB1).
Making the thing an "add-in" and installing the add-in (creating a toolbar and a button) the modules and the reference-sheet became a workbook of their own (WB2). Loading the input-sheet creates a workbook of their own (WB1).

Now, VB help says, that you can NOT reference a workbook from within an add-in; we verified this: workbooks.itemX and workbooks.application... work within all the "normal" workbooks to reference each other, but from an add-in workbooks.XXXX is -at least- unreliable (=does not work).
For those who are interested: all the output-sheets are created in the "add-in" workbook (WB2), not in the "input-workbook" (WB1).

Ok, here is the million $ question:
how to get the add-in and the input-worksheet to be within one and the same workbook (WB1) so all output worksheets are created within the same workbook,
OR
is there any way to do a cross workbook-referencing between add-in and "normal" workbook (which VB help says: no).

System info:
VB-Office 97 and Office XP, input-data are Office 95
conversion to Office 97 is possible, Office 2000/XP is not.

Thank You (for reading )
matt

 

[bryanbayfield]

Not sure where that info comes from.

You shouldn't have any problem accessing other workbooks from an add-in. The only thing you must remember to do is refer to the workbook that will become an add-in as ThisWorkbook.

Having re-read your query I'm not completely sure what your problem is. You have your add-in created from workbook WB1, you want it to take a 6,000 line list and stick it into 30 different worksheets.

That's not a problem. Make sure that when your macro is utilising the spreadhseet with the 6,000 lines it references ActiveWorkbook. Then add another workbook (this will become the active one, add enough sheets so that you have 30, stick your info in there according to your criteria. Bob's your uncle - what's the problem?

 

[mattrumik]

Thank You! fast response.

Your line of thought was exactly what we tried: ActiveWorkbook, ThisWorkbook and such, but:
VB-Help, Index, "project" select one of the first lines which read something like "add-in ....." and there it is: "you can not reference other workbooks from an add-in ..."

we tried ActiveWorkbook and ThisWorkbook in various ways and found that in the beginning everythign is well defined, but as soon as it starts looping it jumps between workbooks and what has been "ActiveWorkbook" before has been changed.
By "observing" those expressions one can see how the content (Name, Item ...) changes :-( actually during debuggin it sometimes seems to work, but as an add-in it behaves the way described: the new worksheets are created in the reference worksheets workbook (WB2) and not in the data-workbook (WB1). Should I poste the code ?

We checked the content of "Workbooks" which contains actually "ThisWorkbook" and "ActiveWorkbook" and it seems
it is in fact a problem .....

Well, one thing: using vars, set at the very beginning to ActiveWorkbook and ThisWorkbook might work; will check this.

Thank You!
matt

 

[bryanbayfield]

Yeah, setting variables equal to the workbooks you create means you can reference them later on and may reduce confusion as you're coding. You shouldn't need to set a variable equal to ThisWorkbook though as this will never change.

Give that a go before posting the code.

That help file sounds nonsensical to me!

 

[mattrumik]

well, more testing, all said above again verified:
Application.ActiveWorkbook
Application.ThisWorkbook
Applicatio.Workbooks....
etc. all change around while switching between XLA and XLS.

Workaround:
Dim AWB as Workbook 'ActiveWorkbook = Data
Dim TWB as Workbook 'ThisWorkbook = Reference

set AWB=ActiveWorkbook
set TWB=ThisWorkbook

and using AWB and TWB throughout, results in the output-sheets being created in the Data-Workbook, as it should.

Any comment about a better way is wellcome. Thank you!

matt

 

[mattrumik]

Thank You bryanbayfield !
Using Vars is definitely theright way to go.

But, alas, about the "ThisWorkbook"-thingy:
We did tests for whatever I wrote before I wrote it.
I did testing again, and the result is still the same:
You can use
Application.Workbooks.....
Workbooks.....
or just ThisWorkbook and ActiveWorkbook

Whatever way You use, it never stays the same,
i.e. you can not use those Objects to reference
a XLA and a XLS reliable throughout the code:
------------------------------------------------
Public GwbkXLA As Workbook 'AddIn
Public GwbkXLS As Workbook 'Sun-Datei
Public GwksXLA As Worksheet 'ZerpflügTabelle
Public GwksXLS As Worksheet 'Sun-Preisliste
Public Sub Start()

Set GwbkXLA = ThisWorkbook
Set GwbkXLS = ActiveWorkbook
Set GwksXLA = GwbkXLA.Worksheets(1)
Set GwksXLS = GwbkXLS.Worksheets(1)

Private Sub AddNewWorkSheets()
'on error GoTo AddNewWorkSheetsError
Dim WKS As Worksheet
Dim wksActive As Worksheet
Dim booGefunden As Boolean
Dim L As Long '(Long is too long, but works)
Dim strBlattname As String

GwksXLA.Activate
For L = 1 To GwksXLA.Cells(2, 3) ' Cells(Reihe, Spalte)
strBlattname = GwksXLA.Cells(L + 1, 2).Value
' Precaution if New Worksheet allready exists
For Each WKS In GwbkXLS.Worksheets
If WKS.Name <> strBlattname Then
booGefunden = False
Else
booGefunden = True
Exit For
End If
Next
If booGefunden = False Then
' Add the New Worksheet
Set wksActive = GwbkXLS.Worksheets.Add(GwbkXLS.Worksheets(GwbkXLS.Worksheets.Count), , 1, xlWorksheet) ' sorry this line too long for this post
wksActive.Name = strBlattname
Else
'nothing
End If
Next
' Empty vars
Set WKS = Nothing
booGefunden = 0
L = 0
strBlattname = &quot;&quot;
AddNewWorkSheetsDone:
Exit Sub
AddNewWorkSheetsError:
MsgBox Err.Number & &quot;: &quot; & Err.Description, vbOKOnly
Resume AddNewWorkSheetsDone
End Sub
----------------------------------------------------
be free to replace the vars with the Expressions and watch
:-/

So I would say the help is correct. I would be very much pleased if somebody can post code which proves MS is wrong.

Thank You!
matt

 

[RobBroekhuis]

I agree with Bryan - I've used Add-ins in exactly the same way you're trying to use them many times, and have never had a problem. But you CANNOT activate a worksheet in the XLA (as you try to do in the above code sample) - the AddIn sheets are per definition hidden. You really shouldn't have to activate the sheets anyway, since you preface its properties (e.g., .cells) with the worksheet variable. You could just as easily preface them with the worksheet reference, e.g. thisworkbook.sheets(1).cells(...)
I can't tell from your code sample what is going wrong, but I'm convinced you can make things work without too much trouble. Tell us exactly what's happening.
Rob