Using String as filename in Save As not recognised by Dir

[NoCanDo]

Hi folks

I'm a newbie to the forum, new to VBA but not to Excel.

I've written a macro to change details in workbook containing 3 worksheets. Included in this macro is a very simple 'save as' procedure Sub SaveAs() which saves the file with a different name based on a defined string with date (sFileName) - it might look over complicated, but I know what it does and where and it ensures file naming conventions are not compromised when the file is saved. This works without any glitches providing you only run it once each day.

However, if you run it a second time it will produce a message box saying the file exists and would I like to overwite the file. This is correct, but often I need to run it more than once each day and would like the macro to see if the file has already been created and then save as with another name based on the same string but with an additional name part e.g. adding "-01" or "-02".

I've tried using the Sub Exists(), but it does not work correctly, i.e it does not seem to see the file name it's looking for as "sFileName" using Dir. I've intentionally left the parameters out so that it will only look in the default folder where the original source file and the saved as files are.

I'm sure it's something verrrrry simple - but I'm struggling - so can you please help to explain where Sub Exists() is going wrong.

Kind regards and look forward to hearing from you.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Rick_Stanich]

Try this.

Please Login or Register  to view this content.

I used an "InputBox" for naming the new file, all the user has to do is add a suffix like "01" or "A" etc.

[NoCanDo]

Hi Rick

Many thanks for the code - looks a little daunting to me ... but I will rise to the challenge and check it out.

I had already found a couple of errors in my own code and got it to work after a fashion, see below, but many thanks for your help.

Will leave the thread open in case I get probs with putting your code into practice.

Many thanks and kind regards

NoCanDo

Sub SaveAsNew()

Dim Today
Today = Format(Now, "yyyymmdd")
Dim sFileName As String
sFileName = "RTT Wait PTL " & Today
Dim sFileName2 As String
sFileName2 = sFileName & " - 01"
Dim Mess1 As String
Mess1 = "Your file exists"
Dim Mess2 As String
Mess2 = "Your file has been saved as '" & sFileName & "'" & Chr(13) & Chr(13) & "You can now print the PTL."
Dim Mess3 As String
Mess3 = "Your file has been saved as '" & sFileName2 & "'" & Chr(13) & Chr(13) & "You can now print the PTL."
Dim Mess4 As String
Mess4 = "Please save file using traditional 'SaveAs' method." & Chr(13) & Chr(13) & "Please make a note of the file name to use:" & Chr(13) & Chr(13) & sFileName & " 'And add your initials'" & Chr(13) & Chr(13) & "e.g. " & sFileName & " - PaDa"

' Does sFileName2 exist? If it does, save file as sFileName and add initials
If Dir(sFileName2 & ".xls") = sFileName2 & ".xls" Then
MsgBox (Mess4)
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Your file has been saved"
Else

' Otherwise does sFileName exist? If it does, save file as sFileName2
If Dir(sFileName & ".xls") = sFileName & ".xls" Then

' The file exists, show message , click OK and save as "sFileName2"
MsgBox (sFileName & Chr(13) & Mess1)

ThisWorkbook.SaveAs (sFileName2)
Filename = sFileName2

'MessageBox to tell the user it worked ok and the name of the new file
MsgBox (Mess3)

Else

' The file does not exist, show message and save as "sFileName"
ThisWorkbook.SaveAs (sFileName)
Filename = sFileName

'MessageBox to tell the user it worked ok and the name of the new file
MsgBox (Mess2)
End If
End If
End Sub

[NoCanDo]

Hi again Rick

Superb - had to change one line:

If Len(Dir("U:\My Documents\" & sFileName)) < 0 Then
to
If Dir(sFileName & ".xls") <> sFileName & ".xls" Then

and it worked a treat - fab!

Reason for this is, it needed to see if the file wasn't there then create it normally (hence the <>), else it was there, then create it with the additional extension.

Full code below.

Many many thanks.

Hope you have a brill day - I will!



Sub SaveAs()

'Dim Filename 'missing declaration

Application.DisplayAlerts = False
' Define the format of today's date
Dim Today
Today = Format(Now, "yyyymmdd")

' Define the file name structure
Dim sFileName As String
sFileName = "RTT Wait PTL " & Today

' Save the workbook
On Error GoTo 0

If Dir(sFileName & ".xls") <> sFileName & ".xls" Then

'change drive\path above to your designation
ThisWorkbook.SaveAs (sFileName)
Filename = sFileName

' Define the MessageBox to tell the user it worked ok and the name of the new file
Dim Message As String

' Defines "Message" as the string for the MsgBox command
' Note: strFileName is the filename and Chr(13) is a paragraph character
Message = "Your file has been saved as '" & sFileName & "'" & Chr(13) & Chr(13) & "You can now print the PTL."

' Display MessageBox
MsgBox (Message)
Exit Sub
Else
On Error GoTo 0
sFileName = InputBox("Please enter new file name!", "New File Name", "RTT Wait PTL " & Today & "-")
ThisWorkbook.SaveAs (sFileName)
Filename = sFileName

' Defines "Message" as the string for the MsgBox command
' Note: strFileName is the filename and Chr(13) is a paragraph character
Message = "Your file has been saved as '" & sFileName & "'" & Chr(13) & Chr(13) & "You can now print the PTL."

' Display MessageBox
MsgBox (Message)
End If
Application.DisplayAlerts = True
End Sub

[Rick_Stanich]

Glad to hear it worked out!