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

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 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