Replace Name of Source Workbook in Target Code

[YellowLabPro]

I am working w/ two files.
File 1 can be named a variation of this name: "TGSItemRecordCreatorMaster.xls". It is appended to something like this-
"TGSItemRecordCreatorMasterForum.xls", this is due to whatever company's info that is being processed at this point in time.

From workbook 1 I run a piece of code that opens a target workbook/sheet file which has a constant name of "TGSUpdater.xls". The data from the 1st file is copied into this file, "TGSUpdater.xls".

I have to constantly change the name in the program to reflect the 1st workbook's appended name:


Set ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Sheets("Record Creator")


I would like to replace the workbook name in the above line to whatever the name is of the workbook that the Open TGSUpdater code is run from.
So if it is run from "TGSItemRecordCreatorMasterForum.xls" workbook, that is the name that the above line would reflect.

This is the code that is run from workbook 1,

Option Explicit
Sub TGSUpdater()
Dim wb1 As Workbook
Dim iStatus As Long
Dim sPath As String
sPath = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\"
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
End Sub

[mdmackillop]

Is this not simply the ActiveWorkbook?
Set ws1 = Activeworkbook.Sheets("Record Creator")

[YellowLabPro]

No unfortunately,
The open procedure activates the new workbook, TGSUpdater.xls, which is now the ActiveWorkbook. From here I run the following procedure which copies data from Wbk 1 to TGSUpdater.xls.


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("TGSItemRecordCreatorMasteripath2.xls").Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Application.ScreenUpdating = False
Range("a1:v" & ActiveCell.SpecialCells(xlLastCell).Row).ClearContents
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Range("A1:V1") = Array("Item#", "Record Description", "Inv", "Tax", "Dept", "Cat", "Qty", "0", "0", "0", "Cost", "Price", "0", "0", "0", "Vend.Id", "Item#", "", "", "", "", "1")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
'ws2.UsedRange.Offset(1, 0).ClearContents

ws1.Range("w6:w" & LastRowSrc).Copy
ws2.Range("a2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("A2" & LastRowSrc - 1).Value = ws1.Range("U5:U" & LastRowSrc).Value
ws1.Range("y6:y" & LastRowSrc).Copy
ws2.Range("b2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("B2" & LastRowSrc - 1).Value = ws1.Range("W5:W" & LastRowSrc).Value
ws1.Range("ad6:ad" & LastRowSrc).Copy
ws2.Range("e2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("B2" & LastRowSrc - 1).Value = ws1.Range("W5:W" & LastRowSrc).Value
ws1.Range("ae6:ae" & LastRowSrc).Copy
ws2.Range("f2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("B2" & LastRowSrc - 1).Value = ws1.Range("W5:W" & LastRowSrc).Value
ws1.Range("ac6:ac" & LastRowSrc).Copy
ws2.Range("g2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("G2" & LastRowSrc - 1).Value = ws1.Range("AA5:AA" & LastRowSrc).Value
ws1.Range("z6:z" & LastRowSrc).Copy
ws2.Range("k2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("K2" & LastRowSrc - 1).Value = ws1.Range("X5:X" & LastRowSrc).Value
ws1.Range("ab6:ab" & LastRowSrc).Copy
ws2.Range("l2").PasteSpecial Paste:=xlPasteValues
'ws2.Range("L2" & LastRowSrc - 1).Value = ws1.Range("Z5:Z" & LastRowSrc).Value
ws1.Range("f6:f" & LastRowSrc).Copy
ws2.Range("p2").PasteSpecial Paste:=xlPasteValues
ws2.Columns("A").Replace What:=" ", Replacement:=""
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Range("A2:A" & LastRow).Copy Range("Q2")
ws2.Range(Cells(1, 3), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 3)) = "Y"
ws2.Range(Cells(1, 4), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 4)) = "N"
ws2.Range(Cells(1, 8), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 8)) = "0"
ws2.Range(Cells(1, 9), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 9)) = "0"
ws2.Range(Cells(1, 10), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 10)) = "0"
ws2.Range(Cells(1, 13), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 13)) = "0"
ws2.Range(Cells(1, 14), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 14)) = "0"
ws2.Range(Cells(1, 15), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 15)) = "0"
ws2.Range(Cells(1, 22), Cells(ws2.Cells(Rows.Count, 2).End(xlUp).Row, 22)) = "1"
' For Each C In ws2.Cells.SpecialCells(xlCellTypeConstants, 23)
' If Not IsNumeric(C.Value) Then C.Value = UCase(C.Value)
' Next C
ws2.Rows("1:1").HorizontalAlignment = xlCenter
ws2.Rows("1:1").Font.Bold = True
ws2.Columns("C:D").HorizontalAlignment = xlCenter
ws2.Cells.Columns.AutoFit
'Application.ScreenUpdating = True

[A1].Activate
End Sub

[mdmackillop]

You could store the original filename in a Public variable and refer to that in the update code
Option Explicit

Dim WB1 As Workbook


Sub test()
Set WB1 = ActiveWorkbook
Workbooks.Open ("TGSUpdater.xls")

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
Set ws1 = WB1.Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
End Sub

[rory]

If the code is in workbook 1, then you want ThisWorkbook rather than ActiveWorkbook.

[YellowLabPro]

I have set this up according to both Mdmackillop and Rory.
The first Sub resides in the first workbook and the second resides in the second.
Running the first code opens "TGSUpdater.xls", the second workbook, and then the second code, inside TGSUpdater.xls is suppose to do its thing.
However, this line in the second Sub is erroring- telling me Variable is not defined.
Set ws1 = Workbooks(TWB).Worksheets("Record Creator")
I declared it as a Public variable in the first Sub, but there is something not right.

Option Explicit
Dim TWB As Workbook

Sub TGSUpdater()
Dim WB1 As Workbook
Dim iStatus As Long
Dim sPath As String
Set TWB = ThisWorkbook
sPath = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\"
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
End Sub





Option Explicit
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("Record Creator")
Set ws1 = Workbooks(TWB).Worksheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Application.ScreenUpdating = False
Range("a1:v" & ActiveCell.SpecialCells(xlLastCell).Row).ClearContents

[mdmackillop]

Hi Doug,
Having tested this with your code, I don't think the sheet can be passed in this fashion. An alternative is to write it to the opened document and retrieve it in the second code.

[YellowLabPro]

Md,
It seems to me, after watching the Locals Window, my supposed public variable does not seem so Public. It does not appear down in the Locals Window. That is what I thought was the problem.

[YellowLabPro]

MD,
I am not not going your route, but I am just exploring this a little further.
I think my understanding of the Public variable was a bit incorrect- according to Walkenbach- "To make a variable available to all the procedures in all the VBA modules in a project,"
This variable needs to travel to another Project, and in Walkenbach's description he discusses within a project and I think that is the fault- going to another project.
If I am misunderstood something, please correct me.

[YellowLabPro]

I am thinking about your suggestion to write to the open document, but it is not clicking yet.
I am pondering how this will work.... doing some searching on it...

[YellowLabPro]

I have located an interesting article, it may be a solution. It is beyond my experience level, as I have not begun to study ByVal or ByRef.
http://www.codeproject.com/useritems/UB_Pointers_In_VB.asp

[mdmackillop]

How about SaveSettings (http://vbaexpress.com/kb/getarticle.php?kb_id=208), which is a recognised way of storing values.

[YellowLabPro]

M-
I downloaded the file, but the button does not do anything that I can see.

[rory]

Public variables are public to all routines in a project, not across projects. (you are also trying to use a Workbook object as a string variable)
How are you actually triggering the routine in the second workbook? If you call it from the first, you can pass an argument to it which can be either the name of the first workbook, or an object variable set to the actual Workbook object.

[YellowLabPro]

Hello Rory,

How are you actually triggering the routine in the second workbook?
From the sub in Post# 3, this code resides in the second workbook. There is a button on the worksheet to run it.


If you call it from the first, you can pass an argument to it which can be either the name of the first workbook, or an object variable set to the actual Workbook object.
When you say "call it from the first" do you mean if the code resides in the same workbook?

The point of passing an argument is still a mystery, I don't understand how code and arguments fit together yet. I understand arguments w/ regards to Formulas, but not code, yet....

If my the above answer, answers your question would you mind cobbling up how you see it to work together. I am hoping by going through it, I will start to follow the notion of passing arguments. For example, ByVal or ByRef, since I have not begun using this technique is just gibberish right now.

BTW: Post# 1 has the block of code that calls to open the second workbook where workbook# 2 is where all the code is stored and run and the data copied to.

[rory]

If you run it from a button, my suggestion won't help. It's probably easiest to have the first routine open workbook 2 and then put the name of workbook 1 either in a cell in workbook2 or into a defined name.
Arguments to formulas work much the same way as arguments to subroutines or functions: the code within those routines can then use or act on the arguments that you pass to them. So if you have an update routine, you could have it accept a workbook object as an argument and then run the update code on that particular workbook. Broadly speaking, the difference between ByVal and ByRef is this:
If you pass by reference (ByRef) then what is passed is actually the memory address of the variable. This means that the called procedure can modify the variable directly and, when control returns to the calling procedure, the variable is modified there too. If you pass by value (ByVal), you pass a copy of the variable, so the called procedure can do whatever it likes, but when control returns to the calling procedure, its variable is unchanged. There is more to know, such as that arrays are always passed ByRef, but that's the gist.

[rory]

And here's a very simplistic demonstration of ByRef versus ByVal:
Sub testmain()
Dim x As Long
x = 2
tester1 x
MsgBox x
tester2 x
MsgBox x
End Sub
Sub tester1(ByVal lngInput As Long)
lngInput = 3
End Sub
Sub tester2(ByRef lngInput As Long)
lngInput = 3
End Sub

[YellowLabPro]

Thanks Rory-- you are up late.
I will give this a go in the morning.

[YellowLabPro]

I am working on slightly different approach.
I am going to start a new thread. I will cross-reference it to one another.
This will maintain the integrity of the ideas/posts here and not muddy up.

http://vbaexpress.com/forum/showthread.php?p=111080#post111080