Using FileExist to check name before saving file

We use a very sophisticated Excel Spreadsheet to generate Order Confirmations, Invoicing and Delivery Notes. It is full of Macros so everything is automated and it takes out the human error but the only thing we can't control is saving the file as the wrong number so looking for a workround.

Typically we save files as:

'OC6027.pdf', 'OC6028.pdf', 'OC6029.pdf'... or 'IN6027.pdf', 'IN6028.pdf', 'IN6029.pdf'...

but nothing stopping the user accidentally putting 'OC6020' in the first spreadsheet as the next sequential number and then the current macros will copy across the error to the rest of the paperwork and just overwrite an existing file.

Is there a way to look into a folder (we only use 1 folder on the entire network) to check the last sequential file name and return it's value +1?

The only other alternative would possibly be to use If FileExist in the current Macro before saving

Can anybody give any guidelines?

try these two procedures, that should work for you:

Sub find_next()
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim x As Long, y As Long, z As Long
    Dim filenum As String
    Dim arr() As Variant
    Set wb = ThisWorkbook
    Set ws = Sheets("Sheet2")
    Dim StrFile As String
    StrFile = Dir("Put Your Path Here\*.pdf")
    Do While Len(StrFile) > 0
        z = z + 1
        StrFile = Dir
    Loop
    ReDim arr(1 To z)
    StrFile = Dir("Put Your Path Here\*.pdf")
    Do While Len(StrFile) > 0
        x = x + 1
'        Debug.Print StrFile
        filenum = Mid(StrFile, 3, 4)
        arr(x) = filenum
'        Debug.Print arr(x)
        StrFile = Dir
    Loop
    Call QuickSort(arr, 1, UBound(arr))
    For y = 1 To UBound(arr)
        'Debug.Print arr(y)
    Next y
    Debug.Print "The next number in sequence is " & arr(UBound(arr)) + 1
End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)
     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Wend

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Wend

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If
  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
End Sub

change what is in RED to meet your needs

Hi DMCGOV and PaulSP8 and thanks for the assistance although after a few hours of trying to get this to work, have decided that this might be too complicated for what we need.

After a bit of a re-think, all we need to do is ensure that the file is saved as what is in the Range("G3") which is working well and just ensure we don't overwrite another file so going down the MsgBox route.

So all we need is to check if the value Range("G3") does not exists as a file name in the folder and if so bring up a vbYesNo message box which lets the user know the file exists and give the option to overwrite or abort. This is where I am at the moment but getting a 'Compile Error' on the Range("G3") saying Sub or Function not defined even though it is working perfectly well later in the routine.

Sub Save_and_print()
'
If FileExist("\\WDMYCLOUD\Euromatic\Invoices\" & Range("G3") & ".pdf") = True Then
MsgBox Range("G3").Value & "already exists" & vbNewLine & "Overwrite?", vbYesNo, "WARNING"
If Answer = vbNo Then End Sub
End If
'
' Save pdf as cell reference (working)
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "\\WDMYCLOUD\Euromatic\Invoices\" & Range("G3") & ".pdf", Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=1, To:=1, _
        OpenAfterPublish:=True
'
' Set the active Printer (working)
'
    Application.ActivePrinter = "Canon MG6200 series Printer on Ne01:"
'
' Print desired number of copies from a cell (working)
'
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=Range("K4").Value, Collate _
        :=True, IgnorePrintAreas:=False
End Sub

Have I got the FileExist wrong?

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. You are still quite new, so I will do it for you this time.)

Hi again dmcgov

below if the full file FileExist that isn't working in red.

Sub Save_and_print()
'
'
If FileExist("\\WDMYCLOUD\Euromatic\Invoices\" & Range("G3") & ".pdf") = True Then
MsgBox Range("G3").Value & "already exists" & vbNewLine & "Overwrite?", vbYesNo, "WARNING"
If Answer = vbNo Then End Sub
End If
'
' Save pdf as cell reference (working)
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "\\WDMYCLOUD\Euromatic\Invoices\" & Range("G3") & ".pdf", Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=1, To:=1, _
        OpenAfterPublish:=True
'
' Set the active Printer (working)
'
    Application.ActivePrinter = "Canon MG6200 series Printer on Ne01:"
'
' Print desired number of copies from a cell (working)
'
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=Range("K4").Value, Collate _
        :=True, IgnorePrintAreas:=False
End Sub

I am not sure if we have got the arguments correct or if pressing the "NO" button will jump to the end and miss out the file saving and printing.

at the moment we are using everything else

Thanks for your support

Hi RobVerheyden that is an interesting option and didn't think of that. The files currently look like:

OC2213.pdf
OC2214.pdf
OC2215.pdf
OC2216.pdf

etc etc and generally the user looks up the last number in the folder and types in the next one. They should enter "OC2217" but have in the past types something like "OC2117" and overwritten files which we can't detect or retrieve.

The most ideal situation would be a macro button beside the which they press and it automatically works out the last file that exists and enters the value +1 then they don't even have to look in the folder.

Thanks for thinking outside the box

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. I will do it for you this time - next time, you must do this yourself, please.)

You really need to read our forum rules, please: https://www.excelforum.com/forum-rul...rum-rules.html

I did it for you.

Please take a moment to read those rules - they aren't complicated.

please post a desensitized workbook. there are issues with fileexist and variables, but i need to see what your seeing.

actually do this:

Dim answer As VbMsgBoxResult
If FileExist("\\WDMYCLOUD\Euromatic\Invoices\" & Range("g3").Value & ".pdf") Then
    answer = MsgBox(Range("g3").Value & "already exists" & vbNewLine & "Overwrite?", vbYesNo, "WARNING")
If answer = vbNo Then Exit Sub
End If

Based on the filenames you provided in Post#8.

This function will lookup last used number and add 1.

Function NewNumber(fldrName As String) As Long
    With CreateObject("scripting.filesystemobject")
        For Each fl In .getfolder(fldrName).Files
            lnum = Application.Max(lnum, Mid(.getbasename(fl), 3, Len(.getbasename(fl)) - 2))
        Next
    End With
    NewNumber = lnum + 1
End Function

Sub tst()
    MsgBox NewNumber("D:\Test2\") 'Change to suit correct foldername
End Sub

hi colours taken out and modified the title to be more relevant - sorry still understanding the etiquette

What are the code tags?