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

 

[RoryA]

Try 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"
            If Dir(sPath) <> "" then Kill sPath
            ActiveWorkbook.SaveAs Filename:=sPath , FileFormat:=52
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Brassaxe]

Hmmm. You're getting me further along. Now it's giving me runtime error 70, permission denied on the Kill sPath.

 

[RoryA]

That would suggest that you can't delete or overwrite the specified file for some reason (e.g. incorrect permissions, or it's open somewhere).

 

[Brassaxe]

Yes. I want it to be able to save itself. So do I need it to first check if the same file exists, and if so, save, and it not, save as (specified name)?

 

[RoryA]

Is the currently specified name (that is causing the permissions error) the same as the current name of the file that is running the code? That would explain the error.

 

[Brassaxe]

Yes. Is there a way around the error. IE, simply save and not save as?

 

[RoryA]

Yep - try 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"
            If lcase$(activeworkbook.fullname) = LCase$(spath) then
                activeworkbook.save
            else
                If Dir(sPath) <> "" then Kill sPath
                ActiveWorkbook.SaveAs Filename:=sPath , FileFormat:=52
             end if
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Brassaxe]

Same error as above. When saving the open file, and a file of the same name exists is doesn't just save it gives runtime error 70, permission denied on the Kill sPath.

Is the only way to get around this to kill the open file? Why can it not save as a normal file would save?

 

[RoryA]

It's not killing the open file (unless you also happen to have another workbook open that has the name of the file you are trying to save to). It should be killing the existing file on disk, but it sounds like you don't have the correct permissions to do that for some reason. I can't replicate your error.