Need application to stop if file exists

Topic

New Reply

I have the following code that exports a sheet to a CSV file.

If the file already exists, I want a warning to over-write or not.

If the user doesn’t want to over-write, I need to exit the VB application.

What’s missing?

Code:

Sub ExportCSVFINAL()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet


    Worksheets("TextFile").Activate
    FName = Cells(1, 15).Value


Set shtToExport = ThisWorkbook.Worksheets("MarrReformatted")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                              

wbkExport.SaveAs FileName:=FName, FileFormat:=xlCSV

wbkExport.Close SaveChanges:=True

End Sub

Reply | Quote

Replies

Kevin,

Here’s some sample code to show you how to do it but you’ll need to adjust it to your circumstances.

Code:

Option Explicit

Sub Test()

   Dim wkbCur    As Workbook
   Dim iAns      As Integer
   Dim vPathName As Variant
   Dim zResult   As String
   
   Set wkbCur = ActiveWorkbook
  
   vPathName = Split(ActiveWorkbook.FullName, ".")
   
   zResult = Dir(vPathName(0) & ".csv")
   
   If (zResult = Null) Then
   
     wkbCur.SaveAs Filename:=vPathName(0) & ".csv", FileFormat:=xlCSV
   
   Else
     iAns = MsgBox("The File: " & vPathName(0) & ".csv Already Exists!" & _
                   vbCrLf & vbCrLf & _
                   "Do you with to Replace it?", vbYesNo + vbInformation, _
                   "File Name Conflict Resolution:")
     If (iAns = vbYes) Then
       Application.DisplayAlerts = False
       wkbCur.SaveAs Filename:=vPathName(0) & ".csv", FileFormat:=xlCSV
       Application.DisplayAlerts = True
     Else
      Exit Sub   'This could also be Application.Quit
     End If
                   
   End If

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks, RG.

I wish I could say I understood this to the point where I can adapt it, but , sadly, I don’t.
Too green with VB.

I thought there would be a (simple) test within my code that would accomplish this.

Reply | Quote

Kevin,

Here’s a commented version maybe that will help.

Code:

Option Explicit

Sub Test()

   Dim wkbCur    As Workbook
   Dim iAns      As Integer
   Dim vPathName As Variant
   Dim zResult   As String
   
   Set wkbCur = ActiveWorkbook
  
   'Get the file spec of current workbook w/o extension, e.g. .xls?
   vPathName = Split(ActiveWorkbook.FullName, ".")
   
   'See if a file with the current file spec and an extension of .csv exists.
   zResult = Dir(vPathName(0) & ".csv")
   
   'Test to see if file exists zResult = Null of file DOES NOT Exist.
   If (zResult = Null) Then
     'Write out file in CSV format.
     wkbCur.SaveAs Filename:=vPathName(0) & ".csv", FileFormat:=xlCSV
   
   Else
     'File does exist ask user if they wish to replace it.
     iAns = MsgBox("The File: " & vPathName(0) & ".csv Already Exists!" & _
                   vbCrLf & vbCrLf & _
                   "Do you with to Replace it?", vbYesNo + vbInformation, _
                   "File Name Conflict Resolution:")
     If (iAns = vbYes) Then
       Application.DisplayAlerts = False
       wkbCur.SaveAs Filename:=vPathName(0) & ".csv", FileFormat:=xlCSV
       Application.DisplayAlerts = True
     Else
	  'User selected not to replace exit subroutine or Application your choice.
      Exit Sub   'This could also be Application.Quit
     End If
                   
   End If

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG, the comments helped a little bit…not totally. In researching this, I found another option (below). However, it opens a dialog box to confirm the folder into which the file is to be saved. Can this be easily modified so that it just saves the file?

Code:

Sub ExportCSV()

        Dim FlSv As Variant
        Dim MyFile As String
        Dim sh As Worksheet
        Dim MyFileName As String
        Dim DateString As String

         DateString = Format(Now(), "yyyy-mm-dd_hh_mm")
       ' DateString = Format(Now(), "yyyy-mm-dd_hh_mm_ss_AM/PM") '<~~ uses current time from computer clock down to the second
        MyFileName = "MarrReformatted_" & DateString

        Set sh = Sheets("MarrReformatted")
        sh.Copy
        FlSv = Application.GetSaveAsFilename(MyFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Where should we save this?")

     If FlSv = False Then GoTo UserCancel Else GoTo UserOK

UserCancel:             '<~~ this code is run if the user cancels out the file save dialog
        ActiveWorkbook.Close (False)
        MsgBox "Export canceled"
        Exit Sub

UserOK:                 '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
        MyFile = FlSv
        With ActiveWorkbook
            .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
            .Close False
        End With


    End Sub

Reply | Quote

I cannot believe how many ways I’ve tried to accomplish not having the dialog box appear and have the file simply SAVED.
Application Alerts = false doesn’t do the trick and I think I’ve tried placing that in various spots in the macro.

GROAN.

Any help will be more that greatly appreciated!!!

Reply | Quote

Hi kweaver

..in the spirit of showing some more code, in addition to RG’s code, maybe this might help too.
See if you can follow this vba.
(PS: I don’t like underscores in_file_names so I changed that bit so you could see how to do it alternatively – but you can use your preferred format)

Code:

Sub exportSheetToCSV()

Worksheets("TextFile").Activate
zFolder = [o1]                          'fetch folder name (for filesave) from cell [O1]
If zFolder = "" Then                    'check for empty cell
[o1] = "C:"                            'set default value if folder cell is empty
zFolder = "C:"                         'folder for filesave
End If                                  'end of test for empty folder-name-for-file-save

If Right(zFolder, 1)  "" Then        'check for required "" in folder name
zFolder = zFolder & ""                 'append required backslash for folder name
End If                                  'end of test for missing backslash

zDateStamp = Format(Now(), "yyyy-mm-dd@hh-mmam/pm")          'prefix name for csv file
zFilename = "MarrReformatted@" & zDateStamp & ".csv"    'add required csv file extension
zSaveAs = zFolder & zFilename           'full path and filename for csv file save

'CHECK IF FILE ALREADY EXISTS IN SPECIFIED LOCATION..
If Dir(zSaveAs)  "" Then               'this filename is already in the specified location..
saywhat = "The file named [" & zFilename & "] already exists in location.." & vbCr
saywhat = saywhat & zFolder                                         'add text
saywhat = saywhat & vbCr & vbCr                                     'add 2 lines
saywhat = saywhat & "Do you want to overwrite the existing file?"   'add text

btns = vbYesNo + vbQuestion + vbDefaultButton2  'message box buttons
boxtitle = "EXPORT SHEET TO CSV FILE.."         'message box heading

answer = MsgBox(saywhat, btns, boxtitle)        'display message box; save User response

If answer  vbYes Then                 'User cancelled, so..
Exit Sub                                'nothing else to do
End If                                  'end of test for User cancelled
End If                                  'end of test for file already exists

'PROCEED WITH FILESAVE..
Set zSheet = Sheets("MarrReformatted")  'sheet to save as a csv file
zSheet.Select                           'switch to the sheet

zSheet.Copy                             'copy the sheet to a new workbook
Application.DisplayAlerts = False       'ignore save changes message when saving as a csv
ActiveWorkbook.SaveAs Filename:=zSaveAs, FileFormat:=xlCSV  'save the file
ActiveWorkbook.Close                                        'close the file
Application.DisplayAlerts = True        'turn alerts back on

ThisWorkbook.Activate                   'return to THIS workbook

End Sub

zeddy

Reply | Quote

Kevin,

Try this:

Code:

Sub ExportCSV()

        Dim FlSv As Variant
        Dim MyFile As String
        Dim sh As Worksheet
        Dim MyFileName As String
        Dim DateString As String

         DateString = Format(Now(), "yyyy-mm-dd_hh_mm")
       ' DateString = Format(Now(), "yyyy-mm-dd_hh_mm_ss_AM/PM") '<~~ uses current time from computer clock down to the second
        MyFileName = "MarrReformatted_" & DateString

        Set sh = Sheets("MarrReformatted")
        sh.Copy
        FlSv = Application.GetSaveAsFilename(MyFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Where should we save this?")

     If FlSv = False Then 
     	 '<~~ this code is run if the user cancels out the file save dialog
        ActiveWorkbook.Close (False)
        MsgBox "Export canceled"
     Else
        '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
        MyFile = FlSv
        With ActiveWorkbook
            Application.DisplayAlerts = False
            .SaveAs Filename:=MyFile, FileFormat:=xlCSV, CreateBackup:=False
            .Close False
            Application.DisplayAlerts = True
        End With
      End If

    End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG & Zeddy…thanks to both of you. I like both of them but the problem still remains that the dialog box opens.

I don’t want the save as dialog box to open…I just want the file saved. I can’t find the right code or location in the VBA routine to put that missing code to avoid the dialog box opening! Still need help.

OK. As of Wednesday evening…I’m using RGs but the dialog box STILL APPEARS.

Reply | Quote

Hi kweaver

..my posted code only shows a message box if you try and export the sheet as a csv file more than once in the last minute.
From your first post#1, your code used this snippet:
Worksheets(“TextFile”).Activate
FName = Cells(1, 15).Value
This ‘retrieves’ the contents from column 15, row 1, (this is cell [o1] ) on the sheet named [TextFile]
I used this info in my posted code to ‘fetch’ the folder name for the file-save location.
..And if that cell is ’empty’, my code will set a default save location as C:
So, if you have a sheet named [TextFile], and a sheet named [MarrReformatted], then my code should work exactly as you wanted without showing ANY dialog box.
Copy my code into a vba module, and try it!

zeddy

Reply | Quote

You could perhaps use this version of your original:

Code:

Sub ExportCSVFINAL()

    Dim wbkExport             As Workbook
    Dim shtToExport           As Worksheet


    Worksheets("TextFile").Activate
    fname = Cells(1, 15).Value


    Set shtToExport = ThisWorkbook.Worksheets("MarrReformatted")     'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    'check if file exists
    If Dir(fname)  vbNullString Then
        ' prompt to overwrite
        If MsgBox(Prompt:="File already exists. Do you want to overwrite?", _
                    Buttons:=vbYesNo + vbQuestion, Title:="Overwrite file?") = vbNo Then
            ' exit if no
            Exit Sub
        Else
            ' delete the file if yes
            Kill fname
        End If
    End If
    wbkExport.SaveAs Filename:=fname, FileFormat:=xlCSV

    wbkExport.Close SaveChanges:=True

End Sub

Reply | Quote

Zeddy: I NOW see two things I was overlooking that you just clarified.

1. In O1 cell I has the complete path as well as the file name. When I removed the file name, everything worked!!

2. I was going crazy (crazier than I usually am) when it worked once and then didn’t a second time! That’s because I tried a different set of data within the 1-minute time frame.

Hmm. Interesting restriction. Where is that time limitation imposed in your code?
UPDATE: I see where!!

It DOES work as described!

PHEW.

Much gratitude!!

Kevin

Reply | Quote

Hi Kevin

..good work! I can see your vba understanding is getting better all the time.
By adjusting the filename timestamp format, you now know how to restrict to 1-hour timeframes.
By removing all time elements from the filename, you can also restrict to 1-day timeframes
(e.g. xxxxxx@yyyy-mm-dd.csv etc etc.)
I did think about adding a “DONE” message when an exported sheet had been saved, but I assumed you were trying to avoid messages. I confess I had to keep checking my test folder to see whether the exported-sheet file was actually saved or not. That 1-minute timeframe caught me out too!
I know that RG’s code and Rory’s code is more compact, but my intention was to document each line of code to show the steps being taken.

zeddy

Reply | Quote