Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro in one workbook that refers to variable in another
There are 2 macros below:
The first one defines a user's workbook name (we will not know that) and jockeys back and forth with a workbook in which we do know the name. THe problem arises when the first macro calls the second macro. THe second macro resides in the known named workbook and it needs to jockey back and forth with the 1st workbook. The problem is the second macro does not recognize the variable name that represents the first workbook. I know this sounds confusing. I hope the code below explains some of the problem: Real question is, How can we refer to the 1st worksheet when we won't know the name. We do paste its name into a cell in the 2nd file. Can we somehow use that cell data to refer to the 1st workbook? Thanks so much. Sub RunUpdate() Dim updfile As String Dim usrfile As Workbook Dim backname As String Dim p As String p = ActiveWorkbook.Path updfile = p & "\" & "fxRM_Update.xls" Set usrfile = ActiveWorkbook backname = p & "\" & "Backup" & usrfile.Name currentuserfile = p & "\" & usrfile.Name 'Insert Filename in Filename cell Sheets("Lookup").Select usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="Filename" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="UserFilename" ActiveSheet.Paste 'Copy Current Version to Update file, Old Version cell usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="CurrentVersion" Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="OldVersion" ActiveSheet.Paste Application.Run ("fxRM_Update.xls!update3") ' CALLS MACRO FROM KNOWN-NAMED FILE--fxRM_Update.xls 'End Sub MACRO 2 sub Update3 () usrfile.Activate ' THIS VARIABE NOT RECOGNIZED With Worksheets("lookup") Range("A40").Select ActiveCell.FormulaR1C1 = "BOO!" End With Windows("fxRM_Update.xls").Activate With Worksheets("lookup") Range("A40").Select ActiveCell.FormulaR1C1 = "BOOHOO!" End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro in one workbook that refers to variable in another
On Jan 19, 2:20 pm, Andyjim wrote:
There are 2 macros below: The first one defines a user's workbook name (we will not know that) and jockeys back and forth with a workbook in which we do know the name. THe problem arises when the first macro calls the second macro. THe second macro resides in the known named workbook and it needs to jockey back and forth with the 1st workbook. The problem is the second macro does not recognize the variable name that represents the first workbook. I know this sounds confusing. I hope the code below explains some of the problem: Real question is, How can we refer to the 1st worksheet when we won't know the name. We do paste its name into a cell in the 2nd file. Can we somehow use that cell data to refer to the 1st workbook? Thanks so much. Sub RunUpdate() Dim updfile As String Dim usrfile As Workbook Dim backname As String Dim p As String p = ActiveWorkbook.Path updfile = p & "\" & "fxRM_Update.xls" Set usrfile = ActiveWorkbook backname = p & "\" & "Backup" & usrfile.Name currentuserfile = p & "\" & usrfile.Name 'Insert Filename in Filename cell Sheets("Lookup").Select usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="Filename" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="UserFilename" ActiveSheet.Paste 'Copy Current Version to Update file, Old Version cell usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="CurrentVersion" Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="OldVersion" ActiveSheet.Paste Application.Run ("fxRM_Update.xls!update3") ' CALLS MACRO FROM KNOWN-NAMED FILE--fxRM_Update.xls 'End Sub MACRO 2 sub Update3 () usrfile.Activate ' THIS VARIABE NOT RECOGNIZED With Worksheets("lookup") Range("A40").Select ActiveCell.FormulaR1C1 = "BOO!" End With Windows("fxRM_Update.xls").Activate With Worksheets("lookup") Range("A40").Select ActiveCell.FormulaR1C1 = "BOOHOO!" End With End Sub Hello Andyjim, Your variable "usrfile" goes out scope between macro calls. You need to declare "usrfile" in the same VBA Module as your macros like this: Declare userfile As Workbook This will make the variable available to all procedures in all modules in your project as long as the VBA project is running. Sincerely, Leith Ross |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro in one workbook that refers to variable in anoth
I must be missing something, Leith. I placed in the second workbook module this declaration: Public usrfile As Workbook (in the same module as UPdate3). However, when I run the macro from the 1st workbook I get: Object variable or with block variable not set. I don't know how to use the SET statement in the second module, because I don't know the name of the 1st workbook from which I initiated the 1st macro. I also tried placing that same Public usrfile as workbook in the 1st workbook, but that didnt seem to help. Any help would be greatly appreciated. Thanks Andy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro in one workbook that refers to variable in anoth
On Jan 20, 10:34 am, Andyjim
wrote: I must be missing something, Leith. I placed in the second workbook module this declaration: Public usrfile As Workbook (in the same module as UPdate3). However, when I run the macro from the 1st workbook I get: Object variable or with block variable not set. I don't know how to use the SET statement in the second module, because I don't know the name of the 1st workbook from which I initiated the 1st macro. I also tried placing that same Public usrfile as workbook in the 1st workbook, but that didnt seem to help. Any help would be greatly appreciated. Thanks Andy Hello Andy, If you like, I can take a look at your workbooks if you can send them to me. It would make correcting the code easier. My email is . Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need variable that refers to same row different column | Excel Discussion (Misc queries) | |||
Variable in one sub which refers to an input box in another sub. | Excel Programming | |||
Running a variable macro when any value is entered into a variable cell | Excel Programming | |||
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ?? | Excel Programming | |||
macro button refers to prev. workbook | Excel Programming |