Not good enough with VBA to understand this error.

[Brassaxe]

This code allows you to simply click a button, open a dialog to define the save location and hit OK. Then it pulls the text from a defined cell and saves the file.

This all works fine unless there is a File will the same name in the same location. Then it gives runtime error "1004", and cannot access "file name".

I would like it to either overwrite the file or Add a sequential number to the end of the file name and then save. Is this possible?

Sub SaveFile()
    Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & Sheets("Sheet1").Range("L26") & ".xlsm", FileFormat:=52
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Brassaxe]

Yeah it has me stumped.. Would it be easier to just add a sequential document number at the end of the name if a file already exists with a the specified name? Like Microsoft does for files.

 

[RoryA]

Yes, you could do something like this:

Sub SaveFile()
    Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            spath = .SelectedItems(1) & Application.PathSeparator & Sheets("Sheet1").Range("L26") & ".xlsm"
            Do While Dir(spath) <> ""
               Dim n As Long
               n = n + 1
               spath = .SelectedItems(1) & Application.PathSeparator & Sheets("Sheet1").Range("L26") & "_" & n & ".xlsm"
            Loop
            ActiveWorkbook.SaveAs Filename:=spath, FileFormat:=52
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Brassaxe]

Holy cow. Thank you so much! I've been losing my mind just trying to get ANYTHING to work.. But this worked! Thank you!

 

[RoryA]

Glad we could help.