This has got me stumped display filename in a cell

[Kpm51]

Hi Folks, (Windows xp & Excel 2010)
This one has me stumped. Not sure why this is happening or how to fix it.
I'm using this code in my NewClient Workbook Template

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","") which displays

\\GOFLEX_HOME\GoFlex Home Public\NEW-CLIENT-RECORD.xltm

Then I run this code

Sub SaveFileAs()
'
'
'
Dim WSName As String, CName As String, Directory As String, savename As String
''''''''''''''''''''CHANGE THE NEXT 3 LINES TO FIT YOUR NEEDS'''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WSName = "JOB SHEET"
'change "Sheet1" to sheet tab name containing cell reference
CName = "B59"
'change "A1" to the cell with your date
Directory = "\\GOFLEX_HOME\GoFlex Home Public\CLIENT ARCHIVES\CURRENT-CLIENTS 2012-2013\"
'directory you want to save to--(make sure string ends with forward slash \)
'...to save to default directory change to "" (Null)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
savename = Sheets(WSName).Range(CName)
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
'
'

Call FormEditJobDetailsOffice
Call AddNewClientToBooks4
Call PrintCurrentWorkSheet
'
'
Exit Sub
'
'
errorsub:
Beep
MsgBox "Changes not saved!", vbExclamation, Title:=savename & ".xlsm"


End Sub

Which clearly saves the new client in this directory

"\\GOFLEX_HOME\GoFlex Home Public\CLIENT ARCHIVES\CURRENT-CLIENTS 2012-2013\"


But then when this code Call AddNewClientToBooks4 which equals the following code


Sub AddNewClientToBooks4()
Dim wbMaster As Workbook

On Error Resume Next
Set wbMaster = Workbooks("4TH QUARTER 2012-2013.xlsm")
On Error GoTo 0
If wbMaster Is Nothing Then
Set wbMaster = Workbooks.Open(Filename:="\\GOFLEX_HOME\GoFlex Home Public\WORKING-FILES\4TH QUARTER 2012-2013.xlsm", UpdateLinks:=False)
End If

With wbMaster.Sheets("CLIENT RECORDS")
.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
.Range("A2:AZ2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'!R3C"
' Copy-Paste formats only
ThisWorkbook.Sheets("Data Storage").Range("A3:AZ3").Copy
.Range("A2").PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
Application.CutCopyMode = True
End With
wbMaster.Close savechanges:=True
Sheets("CREATE NEW CLIENT").Visible = False
End Sub


Which simply adds information of my client to a master sheet but for some reason it leaves my clientworkbook directory address as

\\GOFLEX_HOME\GoFlex Home Public\WORKING-FILES\4TH QUARTER 2012-2013.xlsm

When the client workbook is actually saved here

"\\GOFLEX_HOME\GoFlex Home Public\CLIENT ARCHIVES\CURRENT-CLIENTS 2012-2013\"

I need to run this code so that when it finishes my master book references a hyperlink to the client workbook

"\\GOFLEX_HOME\GoFlex Home Public\CLIENT ARCHIVES\CURRENT-CLIENTS 2012-2013\"

Any input on this would be greatly appreciated.

Many thanks
Kirk
Kirk
Kpm51










[TABLE="width: 167"]
<TBODY>[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[AlphaFrog]

Not sure I follow what you're doing, but consider this. The CELL function does not recalculate immediately after you save the file. So after you SaveAs the template, force the CELL formula to recalculate with something like...
Sheets("Sheet1").Range("A1").Calculate
Change the sheet name and range address to suit.

Alternatively, since you're using a macro to SaveAs, you could have the macro put the path and filename in the cell instead of using a formula.

 

[Kpm51]

Hi AlphaFrog

Thank you for your reply.
The best solution for my project would be to be able to display the url address from my save as macro as an text output into the worksheet cell .
Are you able to show me the code I need to edit so that I can achieve this.

The goal being that when I add the clients info to my master records and then close the client book the master record saved url is the same as in the save as script.

Hope that makes sense.
Thanks again
Kirk
Kmp51

 

[AlphaFrog]

Hi AlphaFrog

Thank you for your reply.
The best solution for my project would be to be able to display the url address from my save as macro as an text output into the worksheet cell .
Are you able to show me the code I need to edit so that I can achieve this.

The goal being that when I add the clients info to my master records and then close the client book the master record saved url is the same as in the save as script.

Hope that makes sense.
Thanks again
Kirk
Kmp51

Change the sheet name and cell reference to suit.

On Error GoTo errorsub:
[B]Sheets("Sheet1").Range("A1").Value = Directory & savename & ".xlsm"[/B]
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False