changing file names with macros

[trevor@OML]

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

 

[Dave Peterson]

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.

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