How to evaluate an workbook object by its name in code?

[prabha_friend]

Declaration:
Private wbAcctFees As Workbook

Public Function mac()
Set wbAcctFees = Workbooks.Open(frmPart1.txtAcctFees.Value)
End Function

Formula in a Cell:
=VLOOKUP(A2,INDIRECT("["&GetFileNamefromCode("wbAcctFees")&".xlsx]gen_rpt'!$A:$K"),11,)

Public Function GetFileNamefromCode(filevariable As String) As String
GetFileNamefromCode = Evaluate(filevariable).Name
End Function

Why its not working? and Whats the solution for this? Thanks in advance.

 

[Norie]

Try removing the "".

GetFileNamefromCode(wbAcctFees)

 

[RoryA]

You need something like:

Private wbAcctFees As Workbook

Public Function mac()
Set wbAcctFees = Workbooks.Open(frmPart1.txtAcctFees.Value)
End Function
Public Function GetFileNamefromCode() As String
GetFileNamefromCode = wbAcctFees.Name
End Function

Formula in a Cell:
=VLOOKUP(A2,INDIRECT("["&GetFileNamefromCode()&".xlsx]gen_rpt'!$A:$K"),11,)

You can't evaluate a variable name like that. If you need to be able to use the function with different workbooks, you'd need to load them into a Collection or Dictionary.

 

[Norie]

Oops, didn't notice the function.

 

[prabha_friend]

Try removing the "".

GetFileNamefromCode(wbAcctFees)

Already set the reference manually using Tools->References.

 

[prabha_friend]

Any reason why

Evaluate(filevariable).Name

is not working?

 

[RoryA]

Yes - you can't evaluate a VBA variable that way.

 

[prabha_friend]

Any other way to achieve that? Members kindly help, work at stake. Thank you.

 

[RoryA]

As I already said, you could load the workbooks into a Collection or Dictionary. You could also use defined names that you set when you open the workbooks. Without knowing what you are doing and why it's hard to say what your best option would be.

Edit: now cross-posted here, so good luck.

 

[prabha_friend]

Without knowing what you are doing and why it's hard to say what your best option would be.

Edit: now cross-posted here, so good luck.

I want to be able to pass in a variable name as a string (not the variable or its value) and then have the code take that variable name and then determine the value of that variable dynamically. Possible?