Suppress "Save As" prompt

Question

I looked this topic up and found some help but the suggestions do not seem to be working.

• I am opening a CSV file into EXCEL make some changes and then want to save the results back to the same file name and the CSV format.
• I want to do this without the prompt that I am getting to make sure I want to save the file.
• We are using a macro enabled excel file to import the data make changes and then save.
• This whole process with initiated by a batch file that will open the Excel application and the designated file at regular period of time so that is why we do not want the prompt to stop the process.

Here is the code I am using in VBA to do the work, as well as the other subs I found that were suppose to help me suppress the prompt.

This code is in the TheWorkbook of the file and not a module.

Am I missing something?

code

Sub fixfile()
    Const strFileName = "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv"
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wbkS = Workbooks.Open(Filename:=strFileName)
    Set wshS = wbkS.Worksheets(1)
    wshS.UsedRange.Copy Destination:=wshT.Range("A1")
    wbkS.Close SaveChanges:=False

    'This is the area of work that we doing to the data
    'Through here

    Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT

    ActiveWorkbook.SaveAs Filename:= _
        "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv", FileFormat _
        :=xlCSVMSDOS, CreateBackup:=False

    Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
    Application.Quit
End Sub

Private Sub Workbook_Open()
    fixfile
End Sub

Sub CloseandSave()
    ActiveWorkbook.Close SaveChanges:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
End Sub

Answer 1

1. I don't get why you are copying the CSV sheet into a new sheet in the macro enabled workbook. This is where your problem starts!

   • You should just be dealing with the data in wshS and saving wbkS instead.
     Done, no more problems.

2. When you call

   ActiveWorkbook.SaveAs Filename:= _
       "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv", 
       FileFormat:=xlCSVMSDOS, CreateBackup:=False`
   

   you're renaming the current macro enabled file within excel to the CSV file as far as Excel sees it.

3. When Application.Quit is called, it is going to call

   Private Sub Workbook_BeforeClose(Cancel As Boolean)
       ThisWorkbook.Save
   End Sub
   

   Which is where the prompt that you are complaining about is happening.

   • Even if you remove it, after Workbook_BeforeClose is called, Excel is still going to check all the open files' .Saved flag.
   • Excel will prompt you to save any files where .Saved = False

But if you set ThisWorkbook.Saved = True then Excel will close the file without asking to save.

Solution:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

Answer 2

Based on the comment in the answers that the reason for opening the file and immediately saving it with no other changes...

So we needed to do what we were doing to get the file edit date to change but not the actual file.

...this is a complete X-Y problem. If you need to change a file's modified time, just change the file's modified time instead of jumping through all of the opening and re-saving hoops:

Sub UpdateFileModifiedDate()
    Const filePath = "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv"

    Dim handle As Integer
    handle = FreeFile
    Open filePath For Binary As #handle
    'Read the first byte.
    Dim first As Byte
    Get #handle, 1, first
    'Write it back
    Put #handle, 1, first
    Close #handle
End Sub

This will be insanely faster than your current process, will only set the file modified date and time to the time that you run the Sub, and doesn't risk any of the other issues you can run into cycling a CSV file through Excel (date formats and locale issues, truncating decimals, conversions to exponential notation, etc., etc.).

Answer 3

since you're going to consciously overwrite an existing file you can just:

• first delete it with a Kill command

• then do the SaveAs

so change this code section:

'This is the area of work that we doing to the data
'Through here

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT

ActiveWorkbook.SaveAs Filename:= _
    "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv", FileFormat _
    :=xlCSVMSDOS, CreateBackup:=False

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
Application.Quit

to this:

'This is the area of work that we doing to the data
'Through here

Kill strFileName '<-- delete the old file

ActiveWorkbook.SaveAs Filename:= _
    "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv", FileFormat _
    :=xlCSVMSDOS, CreateBackup:=False
Application.Quit

---

furthermore your code can be refactored by properly handling the ActiveWorkbook and ActiveSheet objects and reduce the variables and code amount, like follows:

Sub fixfile()
    Const strFileName = "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv"

    Workbooks.Open(Filename:=strFileName).Worksheets(1).UsedRange.Copy Destination:=Worksheets.Add(After:=Worksheets(Worksheets.Count)).Range("A1") '<--| open 'strFileName', reference and copy its 1st worksheet 'UsedRange' and paste it to a newly added worksheet in the macro workbook. After this statement we're left with the opened workbook as `ActiveWorkbook`
    ActiveWorkbook.Close SaveChanges:=False '<--| close `ActiveWorkbook`, i.e. the just opened one. We're left with macro workbook as `ActiveWorkbook` and its newly created worksheet as `ActiveSheet`

    'This is the area of work that we doing to the data
    'Through here

    ActiveSheet.Move '<--| move `ActiveSheet` (i.e. the newly created sheet in macro workbook) to a "new" workbook having that sheet as its only one. We're left with this "new" workbook as `ActiveWorkbook`
    Kill strFileName '<--| delete the "old" 'strFileName'
    ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlCSVMSDOS, CreateBackup:=False '<--| save `ActiveWorkbook` (i.e the "new" one) as the new 'strFileName' file
    ActiveWorkbook.Close SaveChanges:=False '<--| close `ActiveWorkbook` (i.e the "new" one) without changes (we just "SavedA"s it)
    Application.Quit     
End Sub

Answer 4

The problems in your code are due to the following.

When you call SaveAs on the macro-enabled workbook, you had already appended a worksheet to it:

Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))

and then you're trying to save it as csv, which is a text file with only one worksheet, so Excel complains that you will loose information.

Moreover, you're doing the update to the csv twice: once in the

ActiveWorkbook.SaveAs Filename:= ...

Where, as a result, the current workbook becomes the saved workbook, and then again in the Workbook_BeforeClose. In the latter you dont disable the alerts, but anyway there's no need to save again.

I have come to the conclusion that what you want is to use the macro-enabled wb just as a utility for calculation, and you care only for updating the CSV workbook.

For simplicity, we will disable the alerts for the whole session, because the macro-enabled WB is used as a utility and we dont want the batch job to stop for any reason. However you can do it the traditional way, before and after saving, if you feel more comfortable with it.

' Code module ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    fixCSVFile

    ' Do the following only if you want the macro-enabled WB to keep
    ' a copy of the CSV worksheet. but my feeling is you dont want to
    ' ThisWorkbook.Save
    '''''''''''''''''''''

     Application.Quit
End Sub

Sub fixCSVFile()
    Const strFileName = "W:\Webshare\Documents Acquired in 2017\Jim Excel\snr-room-schedule.csv"
    Dim wbkS As Workbook, wshS As Worksheet, wshT As Worksheet

    Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wbkS = Workbooks.Open(Filename:=strFileName)
    Set wshS = wbkS.Worksheets(1)

    wshS.UsedRange.Copy Destination:=wshT.Range("A1")
    wbkS.Close SaveChanges:=False

    'This is the area of work that we doing to the data
    ' For purpose of testing:
    wshT.Range("A1").Value = wshT.Range("A1").Value + 1

    ' Now we will export back the modified csv
    wshT.Move '<- Here we have a temporary workbook copy of the modified csv
    With ActiveWorkbook
        .SaveAs Filename:=strFileName, FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With
End Sub

One more thing, the macro-enabled WB is now such that it closes as soon as it opens so it will be difficult to edit or modify (although there are workarounds). Therefore you should save a back-up copy of it without the Application.Quit, as a testing/maintenance copy. Only the copy that you put in production for the sake of the batch job should have the Application.Quit statement.

Answer 5

It seems like you are making changes to two files. In addition to the csv file that you are opening, you appear to be adding a sheet to the excel file that is running the VBA code with these lines:

Dim wshT As Worksheet
Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))

So my guess is that you are indeed suppressing the save prompt for the csv file but you are also getting a save prompt for the changes you made to the excel workbook when you attempt to close it. So I think you need to suppress that prompt as well by also turning off DisplayAlerts in the CloseAndSave sub, or wherever the excel workbook is actually being closed.