trying to add the new name of a file in a cell

[daniels012]

How do I add the new numbered file name to the "Estimating" worksheet in cell G68?

Here is the code:

Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
Dim strPath As String, fname As String, i As Integer
If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets("ESTIMATING").Select
ActiveWorkbook.Save
strPath2 = "\\5.111.222.646\Estimating\ (file://\\5.111.222.646\Estimating\)"
fname = Dir(strPath2 & Range("B11") & "*.xls")
If fname = "" Then
myFileName = Range("b11").Value & "1.xls"
Else
Do While fname <> ""
myVal = Val(Replace(Replace(fname, ".xls", ""), Range("b11").Value, ""))
myMax = WorksheetFunction.Max(myMax, myVal)
fname = Dir()
Loop
myFileName = Range("b11").Value & myMax + 1 & ".xls"
End If
If Dir(strPath2, vbDirectory) <> "" Then

ThisWorkbook.SaveCopyAs strPath2 & myFileName
Else
MsgBox "The directory to save the file cannot be found. So your data will not be sent."
Exit Sub
End If
End Sub

would this work?
Maybe after line:
If Dir(strPath2, vbDirectory) <> "" Then
add:
ThisWorkbook.ActiveSheet.Range("G68") = myFileName

[Bob Phillips]

You should explicitly name the sheet



ThisWorkbook.Worksheets("the sheet name").Range("G68") = myFileName

[daniels012]

xld,
thank you that seems to work!

May I also ask, about some error trapping?
I need to have a message box come up that makes sense.
For example:
If Error 52 comes up because the directory strPath2 = "\\5.111.222.646\Estimating\ (file://5.111.222.646/Estimating)" is not found then
msgbox "make sure your network connection is turned on then try again"
End Sub

How/where can I enter code?
Thank you again,
Michael

[Bob Phillips]

Not tested, but you could try this



Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
Dim strPath2 As String, fname As String, i As Integer

If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
'Sheets("ESTIMATING").Select
'ActiveWorkbook.Save
On Error Resume Next
strPath2 = Dir("\\5.111.222.646\Estimating\", vbDirectory)
On Error GoTo 0
If strPath2 = "" Then


MsgBox "make sure your network connection is turned on then try again"
Exit Sub
End If

fname = Dir(strPath2 & Range("B11") & "*.xls")
If fname = "" Then
myFileName = Range("b11").Value & "1.xls"
Else
Do While fname <> ""
myVal = Val(Replace(Replace(fname, ".xls", ""), Range("b11").Value, ""))
myMax = WorksheetFunction.Max(myMax, myVal)
fname = Dir()
Loop
myFileName = Range("b11").Value & myMax + 1 & ".xls"
End If
ThisWorkbook.SaveCopyAs strPath2 & Application.PathSeparator & myFileName

[daniels012]

For what ever reason I get the error 52 on this line:
fname = Dir(strPath2 & Range("B11") & "*.xls")

Thank You
Michael

[Bob Phillips]

Try this



fname = Dir(strPath2 & Application.PathSeparator & Range("B11") & "*.xls")

[daniels012]

Nope,
Still get Error 52

[daniels012]

Nope,
Still get Error 52