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\"
    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

[vba]

ThisWorkbook.Worksheets("the sheet name").Range("G68") = myFileName
[/vba]

[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\" 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

[vba]

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
[/vba]

[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

[vba]

fname = Dir(strPath2 & Application.PathSeparator & Range("B11") & "*.xls")
[/vba]

[daniels012]

Nope,
Still get Error 52

[daniels012]

Nope,
Still get Error 52