PDA

View Full Version : [SOLVED] Check For Existing File and Create New if Not Existing



Rlb53
06-30-2016, 05:51 PM
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 (http://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
06-30-2016, 06:25 PM
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 ([URL]http://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

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
06-30-2016, 06:41 PM
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
06-30-2016, 06:50 PM
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
06-30-2016, 06:59 PM
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
06-30-2016, 07:10 PM
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
06-30-2016, 07:11 PM
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
06-30-2016, 07:19 PM
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?