Overwriting files when using SaveAs (VB)

[tombrown65]

I have some clunky code that I put together that includes saving an open excel workbook under a different name.

When I first set it up the GetSaveAsFilename part always put me into the Documents folder, by default. So I added the part to add the current pathname into the InitialFileName.

Prior to this change, the InitFileName variable was just set to "Output Report dd-mmm-yyy", and, if that file existed, I got a prompt warning me before saving it.

However when I made the changes the warning prompt stopped coming, and now this just overwrites any existing file with the name "Output Report dd-mmm-yyy"

How can I get that check & warning back in, and why did it disappear?

    'Find current folder path
    currentPathName = Application.ActiveWorkbook.Path
   
    'Create a default filename in current folder with today's date in title
    InitFileName = currentPathName & "\" & "Output Report " & Format(Now, "dd-mmm-yyyy") & ".xlsm"
    'Prompt for file name (allow overwriting of defualt)
    Do
        fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", Title:="Specify Output File Name for Report")
    Loop Until fileSaveName <> False
   
    'Save file under new filename
    ThisWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[ranman256]

Application.DisplayAlerts = true

 

[tombrown65]

Application.DisplayAlerts = true

Thanks but didnt work.

As I say this only started when I put in the full pathname, as part of the InitialFileName; when I had it just set to the file name, with no path, it wasn't an issue. I wonder if this is because the full path starts from a SharePoint (https) address - i.e. not C: ?

I've had problems, also, trying to use ChDir with that path ...

 

[severynm]

As I say this only started when I put in the full pathname, as part of the InitialFileName; when I had it just set to the file name, with no path, it wasn't an issue. I wonder if this is because the full path starts from a SharePoint (https) address - i.e. not C: ?

I've had problems, also, trying to use ChDir with that path ...

Yep. That might be it. Good to know..

I feel like you have a few options at this point:

1. Try using the full C: path of your file in the SharePoint folder. (Note because SharePoint is stored in local user directory you may have to make the path variable like here so different users can use it). I can confirm that at least for me, I get the "A file named ... already exists in this location" dialog when overwriting a file stored in SharePoint. However, if that fails,
2. You might be manually replicate the "file already exists. Are you sure?" process by checking if the file exists: If Len(Dir(FilePath)) > 0 and then processing whichever button the user presses in your message box. However, I've never really messed with the http: paths much at all so I really don't know how well that will work.