Check For Existing File and Create New if Not Existing

[Rlb53]

I'm Checking for an existing file upon opening a ".xlsm" book.
If The File does not exist. I'm creating it.
if the file does exist, I want to bypass the call and continue with code written.
The problem I'm running into is that although the file exists.. the code is still creating and overwriting the existing file.


With the generosity of Ken... if'm using his Function to supplement the action.

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
    On Error GoTo 0
End Function

In other cases... the Function is performing as intended... but not here for some reason.

    With Sheets("Products")
         For Each cell In .Range("A:A")
             If Not cell.Value = "" Then
                If Not cell.Value = "Misc. BOL" Then
                   If Not cell.Value = "ProductName" Then
                      If Not FileFolderExists("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx") Then
                          Workbooks.Add.SaveAs ("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx")
                          Workbooks(cell.Value & "InvBkupData.xlsx").Close
                          MsgBox ("I've Created Workbook C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx")
                      End If
                   End If
                End If
            End If
         Next
     End With

What am I not seeing here? Why would the action not recognize that the file folder "DOES" exist and move on?

[Paul_Hossler]

I get confused with deeply nested If/Then's inside a For/Next loop, so I've started using Goto's for readability

I think this is your logic in the macro, but it might not be what you wanted to do

I didn't want to go all the way down Column A

Option Explicit
Public Function FileFolderExists(strFullPath As String) As Boolean
     'Author       : Ken Puls (www.excelguru.ca[/URL])
     'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
    On Error GoTo 0
End Function

Sub test()

With Sheets("Products")
    For Each cell In .Cells(1, 1).CurrentRegion.Columns(1).Cells
        If Len(Trim(cell.Value)) = 0 Then GoTo GetNextOne
        If cell.Value = "Misc. BOL" Then GoTo GetNextOne
        If cell.Value = "ProductName" Then GoTo GetNextOne
        If FileFolderExists("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx") Then GoTo GetNextOne
        
        Workbooks.Add.SaveAs ("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx")
        Workbooks(cell.Value & "InvBkupData.xlsx").Close
        MsgBox ("I've Created Workbook C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx")

GetNextOne:
    Next
End With
End Sub

[Rlb53]

Thank You for the Incredibly Quick Response.... However...

Adding the Option Explicit caused error in another "For/Next" call .

And with the suggestion you provided... It is still creating and overwriting the existing file.

Hmmmm...????

[Rlb53]

Okay... it works.

If FileFolderExists("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx") Then GoTo GetNextOne

was changed to:

If Not  FileFolderExists("C:BOLData\DataStorage\" & cell.Value & "InvBkupData.xlsx") Then GoTo GetNextOne

Awesome!! I was getting cross eyed looking at this for so long...

Thank You Immensely ! Now on to the next one ! Have a Great Evening !!

[Rlb53]

I spoke too soon... I didn't read your code all the way through... I changed this back to Unsolved....

Inserting the "Not" was not correct.

I am still getting an overwrite of an existing file..... Hmmm...

[Kenneth Hobs]

The function checks for directory/folder, not a file. Don't use the 2nd input to Dir(), vbDirectory, option if you want to check for a file. FSO methods are more reliable but Dir() methods usually work fine in most cases.

e.g.
If thisworkbook was saved, this returns true. If not saved, it will return false.

Msgbox (len(dir(thisworkbook.FullName))<>0)

[Rlb53]

Slash !.... Missing Slash "\" in the File Name...
We're Good now... I just couldn't see the forest for the trees....
Missing Slash.... Sheesh.

It was typed in as "C:BOLData\DataStorage"....
When it should have been "C:\BOLData\Storage"....

Thanks Again !!!!

[Rlb53]

Thank you Kenneth...

I see what you're saying.
For whatever reason... the Function is recognizing the existing of the file name within the directory...

(At least... as long as I put the "Slash" in as it should be... Simple errors.. just kill me... must be too tired.)

Function been successful doing so in several other applications as well... but now you've got me wondering if I've set myself up for an "Uh-Oh !" moment in the future?