Creating Code To Populate Information From A Second Workbook With A Variable Name?

[xDear]

Hello,
I am new to VBA code and have a situation similar to one posted by DonnaJ yesterday</SPAN></SPAN> in her question "Multiple Workbooks - Can You Create Formulas Just Like When Using Mulitple Worksheets?"

Fishboy responded (in part) "...Can you create formulas for multiple workbooks like when using multiple sheets? If you mean you can already write formulas which check different sheets within your current workbook with formulas like this:

=IF(Sheet2!A1="Whatever","Do something","Do something else")

Then yes, this can be expanded to work with separate workBOOKS as well as workSHEETS as follows:

=IF('Other Workbook Name'Sheet2!A1="Whatever","Do something","Do something else")</SPAN>"

I realize these are formulas, not VBA,however,

my question is what if the 'Other Workbook Name' is always different because it is a new report each month-is there a way to grab the name of the newly opened document and assign it to a variable, i.e.,


Dim OWName as String
OWName = 'Other Workbook Name'</SPAN></SPAN>

-so it can then be used as a reference, i.e.,</SPAN></SPAN>

Dim ThisReview as String
ThisReview=[OWName]Sheet1!$B$5 ?


I have found a block of code that prompts the user to select a file from the directory, opens it and then stores the entire file path to a variant named 'FName', but trying to access information from the second workbook using 'FName' does not work either-maybe because of file type mismatch?


Any help would be greatly appreciated since I have now spent the better part of 4 days trying to google an answer.</SPAN></SPAN>
</SPAN></SPAN>