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
