Setting a Workbook Variable's Value from Another Variable

AnyOldName

New Member
Joined
May 18, 2019
Messages
8
Is there a way toset the value of a workbook variable from another variable that holds a string?

I already have oneaction that does this by using ActiveWorkbook ,as shown below, but I have twoother workbooks that is are data templates, one of which will be opened up bythe macro.

Public Var_Code_WorkbookName As Workbook


Set Var_Code_WorkbookName = ActiveWorkbook
Debug.Print "DRPS Code Workbook is: " & Var_DRPS_Code_WorkbookName.FullName

Is there a way to do the same thing but referencing the value of a string rather than using ActiveWorkbook?



Public Var_Data_Template1 As Workbook
Public Var_Template_Type As String 'This variable holds the file path & filename of the data template workbook, those values are picked up from elsewhere in the code
'eg C:\User\VBA Code\ Excel File.xlsx


Set Var_Data_Template1 = Var_Template_Type

I can't figure out a method or the syntax for this last line, can anyone advise if its possible? Thankyou
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,159
Office Version
2013
Platform
Windows
Hi,
welcome to forum

you use Set to assign an object reference to your variable in this case, the workbooks object.

If your string variable contains the path & filename and the workbook is not open you would do something like this

Code:
Var_Template_Type = "C:\User\VBA Code\Excel File.xlsx"
Set Var_Data_Template1 = Workbooks.Open(Var_Template_Type)
If the workbook is already open you just need to include the workbook name in your string variable.

Code:
Var_Template_Type = "Excel File.xlsx"
Set Var_Data_Template1 = Workbooks(Var_Template_Type)
Dave
 

AnyOldName

New Member
Joined
May 18, 2019
Messages
8
Thanks for the welcome and the reply, that little section is working perfectly now. I'm pretty sure I'd already tried it this way as I had that exact line of code for setting the variable in my sub but set as a comment and indented several times, which is how I keep old code that hasn't worked while trying to problem solve but nevermind, its working now.

Two following questions from this are:

Q1) How do I use the the workbook variable I've just defined to drive the code to name a variable for a worksheet within in that same workbook?

Currently I am doing this:

Public Var_Template_Setup_Sheet as Worksheet

Set Var_Template_Setup_Sheet = ActiveWorkbook.Worksheets("Set Up -DO NOT RENAME")

But I would prefer to do it with variables so that I'm not reliant on having the correct workbook / worksheet active. eg, I want to to something like this:

Set Var_Template_Setup_Sheet = Workbooks(Var_Data_Template1).Worksheets("Set Up -DO NOT RENAME")


Q2) Can anyone recommend a decent reference to help me learn the finer points of this type of stuff? So far I'm self taught from Walkenbach's Excel VBA Programming for Dummies and looking around forums such as this but my core understanding of the method and syntax isn't as strong as I'd like it to be.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,159
Office Version
2013
Platform
Windows
But I would prefer to do it with variables so that I'm not reliant on having the correct workbook / worksheet active. eg, I want to to something like this:

Set Var_Template_Setup_Sheet = Workbooks(Var_Data_Template1).Worksheets("Set Up -DO NOT RENAME")
Code:
Set Var_Data_Template1 = ActiveWorkbook


Set Var_Template_Setup_Sheet = Var_Data_Template1.Worksheets("Set Up -DO NOT RENAME")


Q2) Can anyone recommend a decent reference to help me learn the finer points of this type of stuff? So far I'm self taught from Walkenbach's Excel VBA Programming for Dummies and looking around forums such as this but my core understanding of the method and syntax isn't as strong as I'd like it to be.
Just do a search, plenty of information out there like this: http://www.cfo.com/spreadsheets/2011/08/referring-to-other-worksheets-or-workbooks-in-an-excel-macro/

Dave
 

Forum statistics

Threads
1,078,515
Messages
5,340,861
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top