I have a workbook(A) that on a daily basis gets populated with data from
another spreadsheet(Bx). I have got the following formula that looks at the
date on spreadsheet A to determine which file to open (Bx - named 1 to 31
within a folder named by month): =CONCATENATE("C:\My Documents\Control
F1\Steady
State\2005\",LEFT(""&TEXT(A399,"mmmm-dd"),3),"\",RIGHT(""&TEXT(A399,"mmm-dd"),2),".xls")
How can I set the product as a variable that can be read in the
Workbooks.Open ( ) code within a macro??

First, this

LEFT(""&TEXT(A399,"mmmm-dd"),3),"\",RIGHT(""&TEXT(A399,"mmm-dd"),2)

can be reduced to

TEXT(A399,"mmm\\dd")

To answer the question

myFile = "C:\My Documents\Control F1\Steady State\2005\" &
Format(A399,"mmm\\dd"),3) & ".xls")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"April" wrote in message
...
I have a workbook(A) that on a daily basis gets populated with data from
another spreadsheet(Bx). I have got the following formula that looks at
the
date on spreadsheet A to determine which file to open (Bx - named 1 to 31
within a folder named by month): =CONCATENATE("C:\My Documents\Control
F1\Steady

State\2005\",LEFT(""&TEXT(A399,"mmmm-dd"),3),"\",RIGHT(""&TEXT(A399,"mmm-dd"
),2),".xls")
How can I set the product as a variable that can be read in the
Workbooks.Open ( ) code within a macro??

Dim fileName As String

fileName = Range("A1") ' or whatever cell your data is in

Workbooks.Open(fileName)

that should do the trick... be sure to make certain it won't ever tr
to open non-existant files.

if len(Dir(fileName)) = 0 then
MsgBox("File does not exist")
else
Workbooks.Open(fileName)
End If

or something like tha

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...fo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=39055