Overwriting files when using SaveAs (VB)

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?


VBA Code:
    '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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,413
Messages
5,706,300
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top