I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error. Thanks
so much in advance,

With the activecell as the cell with the formula

='C:\[MTG Source File 1.xls]Sheet1'!$A$1

Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub

Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....

HTH,
Bernie
MS Excel MVP

"Carl" <(E-Mail Removed)> wrote in message
news94B028D-6329-4F3B-915C-(E-Mail Removed)...
>I need the formula to change the name of the source file based on a logic
> test. For examle, how would I change the following so that it references a
> different file name based on the day of the month?
> ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
> I know how to get the day of the month, just haven't been able to get the
> name of the file to change in the formula without a reference error.
> Thanks
> so much in advance,
>

Thanks, Bernie,
I think you are very close but I got a syntax error on the following steps
(turned red) and could not figure out how to correct it:

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))

Thanks again, Carl

"Bernie Deitrick" wrote:

> With the activecell as the cell with the formula
>
> ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
>
> Sub ChangeFileNameTest()
> Dim myFormula As String
> Dim myFName As String
> myFormula = ActiveCell.Formula
> myFName = "MTG Source File " & Day(Date) & ".xls"
> myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
> Mid(myFormula, InStr(1, myFormula, "]"))
> MsgBox "Here's the new formula: " & myFormula
> ActiveCell.Formula = myFormula
> End Sub
>
> Of course, the logic I used for the myFName wasn't based on anything other
> than a wild guess....
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Carl" <(E-Mail Removed)> wrote in message
> news94B028D-6329-4F3B-915C-(E-Mail Removed)...
> >I need the formula to change the name of the source file based on a logic
> > test. For examle, how would I change the following so that it references a
> > different file name based on the day of the month?
> > ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
> > I know how to get the day of the month, just haven't been able to get the
> > name of the file to change in the formula without a reference error.
> > Thanks
> > so much in advance,
> >
>
>
>

Carl

Those two lines are actually all one line.

Enter a line-continuation character and try again..

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName & _
Mid(myFormula, InStr(1, myFormula, "]"))



Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 13:20:01 -0700, Carl <(E-Mail Removed)> wrote:

>Thanks, Bernie,
>I think you are very close but I got a syntax error on the following steps
>(turned red) and could not figure out how to correct it:
>
>myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
>Mid(myFormula, InStr(1, myFormula, "]"))
>
>Thanks again, Carl
>
>"Bernie Deitrick" wrote:
>
>> With the activecell as the cell with the formula
>>
>> ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
>>
>> Sub ChangeFileNameTest()
>> Dim myFormula As String
>> Dim myFName As String
>> myFormula = ActiveCell.Formula
>> myFName = "MTG Source File " & Day(Date) & ".xls"
>> myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
>> Mid(myFormula, InStr(1, myFormula, "]"))
>> MsgBox "Here's the new formula: " & myFormula
>> ActiveCell.Formula = myFormula
>> End Sub
>>
>> Of course, the logic I used for the myFName wasn't based on anything other
>> than a wild guess....
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Carl" <(E-Mail Removed)> wrote in message
>> news94B028D-6329-4F3B-915C-(E-Mail Removed)...
>> >I need the formula to change the name of the source file based on a logic
>> > test. For examle, how would I change the following so that it references a
>> > different file name based on the day of the month?
>> > ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
>> > I know how to get the day of the month, just haven't been able to get the
>> > name of the file to change in the formula without a reference error.
>> > Thanks
>> > so much in advance,
>> >
>>
>>
>>

That took care of the problem. Thanks to both of you.

"Gord Dibben" wrote:

> Carl
>
> Those two lines are actually all one line.
>
> Enter a line-continuation character and try again..
>
> myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName & _
> Mid(myFormula, InStr(1, myFormula, "]"))
>
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 2 Jul 2008 13:20:01 -0700, Carl <(E-Mail Removed)> wrote:
>
> >Thanks, Bernie,
> >I think you are very close but I got a syntax error on the following steps
> >(turned red) and could not figure out how to correct it:
> >
> >myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
> >Mid(myFormula, InStr(1, myFormula, "]"))
> >
> >Thanks again, Carl
> >
> >"Bernie Deitrick" wrote:
> >
> >> With the activecell as the cell with the formula
> >>
> >> ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
> >>
> >> Sub ChangeFileNameTest()
> >> Dim myFormula As String
> >> Dim myFName As String
> >> myFormula = ActiveCell.Formula
> >> myFName = "MTG Source File " & Day(Date) & ".xls"
> >> myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
> >> Mid(myFormula, InStr(1, myFormula, "]"))
> >> MsgBox "Here's the new formula: " & myFormula
> >> ActiveCell.Formula = myFormula
> >> End Sub
> >>
> >> Of course, the logic I used for the myFName wasn't based on anything other
> >> than a wild guess....
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >> "Carl" <(E-Mail Removed)> wrote in message
> >> news94B028D-6329-4F3B-915C-(E-Mail Removed)...
> >> >I need the formula to change the name of the source file based on a logic
> >> > test. For examle, how would I change the following so that it references a
> >> > different file name based on the day of the month?
> >> > ='C:\[MTG Source File 1.xls]Sheet1'!$A$1
> >> > I know how to get the day of the month, just haven't been able to get the
> >> > name of the file to change in the formula without a reference error.
> >> > Thanks
> >> > so much in advance,
> >> >
> >>
> >>
> >>
>
>