Setting a Workbook Variable's Value from Another Variable

[AnyOldName]

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]

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

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.

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

Dave

 

[AnyOldName]

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]

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")

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/201...er-worksheets-or-workbooks-in-an-excel-macro/

Dave