daniels012
03-26-2008, 06:54 AM
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
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