MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formula to use a concatenated text as a file reference - INDIRECT?


Posted by Rachita on June 06, 2001 12:35 PM

I would like to write a formula that uses a concatenation of text fields as a filename. It did not work. Then, I tried "INDIRECT". The problem with this is that I have too many files to reference and cannot open all of them. I have heard that I can write code in VB and that would work. Please HELP!!!!


Posted by Barrie Davidson on June 06, 2001 12:41 PM

What are you trying to do with your formula?
Barrie

Posted by Sean on June 07, 2001 4:36 AM

===============
Hi Raitcha,

If you want some code paste the following into your VBE module (Alt+F11)

There are 2 scenarios I have given to help, choose the one that is closest to your requirements or get back to me with some more specifics if it doesn't quite fit the bill.

Sean
s-o-s@lineone.net

==================
Sub Open_a_File()
' This macro assumes you have selected cells in the order
' that the name is written
' ie If cell B2 contains the 1st part, D5 contains the 2nd part
' you select cells B2 (Hold CTRL) select D5 then run the macro

' please note you file name should EXCLUDE the .xls qualifier
' Please note the files should all be in the current directory


x = ""
For Each cell In Selection

x = x & cell.Value

Next

filname = x & ".xls"

Workbooks.Open FileName:=filname

End Sub

Sub Open_a_File2()

' this code assumes the first part of the file name is in cell
' b2 the second part is in cell C2

' please note you file name should EXCLUDE the .xls qualifier
' Please note the files should all be in the current directory

x = Range("B2").Value & Range("C2").Value

filname = x & ".xls"

Workbooks.Open FileName:=filname

End Sub