changing file names with macros

Can anyone help me with this code

What i'm trying to do is get the last 3 letter of the file name ie AWE to be replaced with
what is in a selected cell. In this case A32
please help

Sub update()

Range("A32").Select
franking = ActiveCell.FormulaR1C1

'ActiveCell.FormulaR1C1 = _
"='[Reuters Contribution fields_AWE.xls]Estimates'!R29C2"

End Sub

Maybe you could look for .xls] and back off from there?

Option Explicit

Sub update()
Dim Franking As String
Dim NewFranking As String
Dim NewText As String
Dim DotXLSPos As Long

Franking = Range("a32").FormulaR1C1
NewText = Range("a1").Value

''' Franking = "='[Reuters Contribution fields_AWE.xls]Estimates'!R29C2"

DotXLSPos = InStr(1, Franking, ".xls]", vbTextCompare)

If DotXLSPos = 0 Then
MsgBox "not found"
Else
NewFranking = Left(Franking, DotXLSPos - 5 + 1) _
& NewText & Mid(Franking, DotXLSPos)
MsgBox NewFranking
End If

End Sub

An alternative...

You may want to record a macro when you change links
edit|links
and use that technique instead.

"trevor@OML" wrote:
>
> Can anyone help me with this code
>
> What i'm trying to do is get the last 3 letter of the file name ie AWE
> to be replaced with
> what is in a selected cell. In this case A32
> please help
>
> Sub update()
>
> Range("A32").Select
> franking = ActiveCell.FormulaR1C1
>
> 'ActiveCell.FormulaR1C1 = _
> "='[Reuters Contribution fields_AWE.xls]Estimates'!R29C2"
>
> End Sub
>
> --
> trevor@OML
> ------------------------------------------------------------------------
> trevor@OML's Profile: http://www.excelforum.com/member.php...o&userid=29503
> View this thread: http://www.excelforum.com/showthread...hreadid=492083

--

Dave Peterson