I have a save macro that saves the file and uses a value in a cell for the filename. Sometimes this value may have a / in it.
How can I modify this to prevent error? The solution I thought of is to have another cell = this particular cell and replace /'s with spaces...and then use that value for saving instead..but there may be an in vba code solution I am not aware of.
Sub SaveCopy()
Dim sFileName As String
Dim sDateTime As String
Dim pNum As String
If Dir(Environ("UserProfile") & "\Documents\New Part Setups", vbDirectory) = "" Then
MkDir Path:=Environ("UserProfile") & "\Documents\New Part Setups"
Else
End If
With ThisWorkbook
pNum1 = Range("C11").Value
pNum2 = Range("C30").Value
pString = "New Part Setup"
If pNum2 = "" Then
sDateTime = pString & " - " & pNum1 & " - " & Format(Now, "mm-dd-yy") & ".xlsx"
Else
sDateTime = pString & " - " & pNum1 & " and " & pNum2 & " - " & Format(Now, "mm-dd-yy") & ".xlsx"
End If
sFileName = Environ("UserProfile") & "\Documents\New Part Setups\" & sDateTime
'.SaveCopyAs sFileName
Application.DisplayAlerts = False
ThisWorkbook.CheckCompatibility = False
ThisWorkbook.SaveAs FileName:=sFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "Your New Part Setup for " & pNum & " has been saved to " & (Environ("UserProfile") & "\Documents\New Part Setups\")
End With
End Sub
For reference C11 and C30 are part numbers that are generally numbers with dashes, but occasionally have a / in them which is what of course throws the error.
Thanks in advance, also, Happy Thanksgiving (I know it's over now but still)
Bookmarks