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" wrote in message
...
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" wrote in message
...
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 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" wrote in message
...
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 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" wrote in message
...
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,