Solved: Pass a workbook and worksheet name

[YellowLabPro]

There are two procedures in this project, Sub TGSUpdater() and Sub Update().
Sub TGSUpdater() is stored in Book1; "TGSItemRecordCreatorMaster.xls" (this name changes regularly), and
Sub Update() is stored in Book2 "TGSUpdater.xls" and this name is static.

I want to store the workbook name , in this case "TGSItemRecordCreatorMaster.xls" and the activesheet name "RecordCreator", in a variable to which will be passed to the second sub, Sub Update().
This will then call the correct workbook and be flexible enough when the name of Book1 changes.


Sub TGSUpdater()
Dim wbTarget As Workbook
Dim iStatus As Long
Dim wbn As String, wPath As String
Call RecordLenOk
Call VerifyParentFind
Call VendorCheck

wPath = "C:\Documents and Settings\Doug\Desktop\TGS\"
wbn = "TGSUpdater.xls"

'Err.Clear
'On Error Resume Next
' Set wb1 = Workbooks("TGSUpdater.xls")
' Workbooks("TGSUpdater.xls").Activate
' iStatus = Err
' If Err <> 0 Then
' On Error GoTo 0
' Workbooks.Open sPath & "TGSUpdater.xls"
' End If
If IsWbOpen(wbn) Then
Set wbTarget = Workbooks(wbn)
Else
If Not (Dir(wPath & wbn) = "") Then
Set wbTarget = Workbooks.Open(wPath & wbn)
Else
MsgBox ("The Workbook """ & wbn & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If
Exit Sub
End Sub


Here in this sub, I want to store the names in the variable and remove the hardcoded names.

Sub Update()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range


Set ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Sheets("RecordCreator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")


I hope this is explained well.

[Bob Phillips]

Sub Update(wbName As String, wsName As String)
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range


Set ws1 = Workbooks(wbName).Sheets(wsName)
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")

[YellowLabPro]

Bob,
How does this line know the name, of the book and sheet of origination?

Sub Update(wbName As String, wsName As String)

In other word, the arguments; how do they pick up on the names of the two objects?

[Bob Phillips]

When you call Update, you use



Call Update("TGSItemRecordCreatorMaster.xls","RecordCreator")

[YellowLabPro]

Bob,
I do not follow you on this.
It seems to me there has to be some connection between the two, at this point- I dont see it.

I am posting code again, not for your need but mine- I have added two lines to the first sub, thinking this is what you meant. But it is wrong.
This might be of some use- Once the sheet is opened from workbook1, I run Sub Update() from a button on the sheet, not a Call inside the code anywhere.
This may or may not have anything to do w/ your answer.


wbName=ActiveWorkbook.name
wsName=ActivSheet.name




Sub TGSUpdater()
Dim wbTarget As Workbook
Dim iStatus As Long
Dim wbn As String, wPath As String, wbName As String, wsName As String
Call RecordLenOk
Call VerifyParentFind
Call VendorCheck

wPath = "C:\Documents and Settings\Doug\Desktop\TGS\"
wbn = "TGSUpdater.xls"
wbName = ActiveWorkbook.Name
wsName = ActiveSheet.Name
If IsWbOpen(wbn) Then
Set wbTarget = Workbooks(wbn)
Else
If Not (Dir(wPath & wbn) = "") Then
Set wbTarget = Workbooks.Open(wPath & wbn)
Else
MsgBox ("The Workbook """ & wbn & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If
Exit Sub
End Sub



Sub Update(wbName As String, wsName As String)
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range


'Set ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Sheets("RecordCreator")
Set ws1 = Workbooks(wbName).Sheets(wsName)
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Application.ScreenUpdating = False
Range("a1:v" & ActiveCell.SpecialCells(xlLastCell).Row).ClearContents

[Bob Phillips]

That is different. Somehow, you have to pass details of one workbook to another. Which sheet is this button on, the variable workbook one? And where is Update?

[YellowLabPro]

Sorry for the delay....
The button to launch the the workbook TGSUpdater is in Book1
The button to run Update is in Book2

[Bob Phillips]

I think a sequential list wopuld help. Firts there were no buttons, then there was one, now there are two. And what is Book1 and Book2 in relation to what you have said before?

[YellowLabPro]

Bob,
I did not know the buttons were relevant, will not make that error again, apologies. I referenced Book1 and Book2 in the original post, along w/ their current names. This was to show the first workbook's name can and will have different names.

[Bob Phillips]

I think I get it.

Try this (untested) code



Sub TGSUpdater()
Dim wbTarget As Workbook
Dim iStatus As Long
Dim wbn As String, wPath As String, wbName As String, wsName As String
Call RecordLenOk
Call VerifyParentFind
Call VendorCheck

wPath = "C:\Documents and Settings\Doug\Desktop\TGS\"
wbn = "TGSUpdater.xls"
wbName = ActiveWorkbook.Name
wsName = ActiveSheet.Name
If IsWbOpen(wbn) Then
Set wbTarget = Workbooks(wbn)
Else
If Not (Dir(wPath & wbn) = "") Then
Set wbTarget = Workbooks.Open(wPath & wbn)
Else
MsgBox ("The Workbook """ & wbn & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If
wbTarget.Names.Add Name:="wbName", RefersTo:=wbName
wbTarget.Names.Add Name:="wsName", RefersTo:=wsName
End Sub




Sub Update()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Dim wbName As String, wsName As String

wbName = Application.Evaluate(ThisWorkbook.Names("wbName").RefersTo)
wsName = Application.Evaluate(ThisWorkbook.Names("wsName").RefersTo)

'Set ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Sheets("RecordCreator")
Set ws1 = Workbooks(wbName).Sheets(wsName)
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Application.ScreenUpdating = False
Range("a1:v" & ActiveCell.SpecialCells(xlLastCell).Row).ClearContents

[YellowLabPro]

Bob,
You did it! You are a genius.
Not to look a gift horse in the mouth- but would you explain one thing to me regarding this plz?


wbTarget.Names.Add Name:="wbName", RefersTo:=wbName
wbTarget.Names.Add Name:="wsName", RefersTo:=wsName

You use wbTarget twice- is this get passed along okay due to the workbook on the first line is passing a name to "wbName" and we can use it again b/c it is being overwritten to "wsName"?

Thank you for solving. I have run into this situation on other occassions and have had to abandon. This will come in very handy in the future, thank you for working this out for me. And again sorry if I was incomplete in my original explanation.

[rory]

Doug,
wbTarget is just a workbook variable - you can refer to it as many times as you like, so I'm not sure what your question is?

[Bob Phillips]

What I am doing Doug is adding two names to the workbook that you are opening, one for the sourec workbook name, one for the source worksheet name. So, in adding those names, I add it to the same workbook, the one you had set up as wbTarget earlier in your code, so I make two code statements referring to that workbook.

[Bob Phillips]

Perhaps if I had differentiated the names from the variables it might have helped make it clearer, i.e.



wbTarget.Names.Add Name:="BookName", RefersTo:=wbName
wbTarget.Names.Add Name:="SheetName", RefersTo:=wsName


Of course, the Update button code would have to be changed to evaluate these different names.

[YellowLabPro]

No you were good there Bob,
it was here that I was trying to sort out:
wbTarget.Names
wbTarget.Names
b/c since it is dimmed as a workbook- I was thinking the second line might have needed to be:
wsTarget where wsTarget would have been dimmed as a worksheet.