VBA error handling when file in path doesn't exist

[RandomUserCode]

Hi everyone. I want to make some error handling when the file in the given path, doesn't exist. Currently the if-statement doesn't work as when the two files i want to import is in the path, it goes to the else-statement and debug print "error". So i dont know why my two booleans are "false" when the files exists in the path. I have tried to say Dir(Len(filePath1) = "" then exit sub, but that fails too. Hope someone can help me out with this error handling

Code below:

    Dim filePath1 As String
    Dim filePath2 As String
    Dim fileExists1 As Boolean
    Dim fileExists2 As Boolean
    
    filePath1 = "my path" & Format(Date, "ddmmyyyy")
    filePath2 = "my path" & Format(Date, "ddmmyyyy")
    fileExists1 = Dir(filePath_Counterparty) <> ""
    fileExists2 = Dir(filePath_Counterparty) <> ""
    
    Debug.Print fileExists1 
    Debug.Print fileExists2
    
    If fileExists1 = False Or fileExists2 = False Then
        Debug.Print "Error"
        Exit Sub
    Else
        Debug.Print "Success"

        Set closedBook = Workbooks.Open(filePath1 & ".csv")
        closedBook.Sheets("sheet1" & Format(Date, "ddmmyyyy")).Copy After:=ThisWorkbook.Sheets(1)
        closedBook.Close SaveChanges:=False
        
        Set closedBook = Workbooks.Open(filePath2 & ".csv")
        closedBook.Sheets("sheet2" & Format(Date, "ddmmyyyy")).Copy After:=ThisWorkbook.Sheets(2)
        closedBook.Close SaveChanges:=False
    End If

 

[HaHoBe]

Hi,

at a quick glance: you give a value to filePath1 but check for filePath_Counterparty which hasn´t been mentioned before.

And for Windows I would expect the path for a file to start with the drive letter or network path - I can´t see that from the string "my path".

Ciao,
Holger

 

[RandomUserCode]

Hi,

at a quick glance: you give a value to filePath1 but check for filePath_Counterparty which hasn´t been mentioned before.

And for Windows I would expect the path for a file to start with the drive letter or network path - I can´t see that from the string "my path".

Ciao,
Holger

Ah that is a mistake. Should just be filePath1 and filePath2 in the dir(). Yeah i know but it makes no difference if the path is "\\user\folder\test\testfile.csv" or "S:\user\folder\test\testfile.csv". I just typed "my path" as the path works but the macro fails somehow

 

[HaHoBe]

Hi RandomUserCode,

for my part I would take care for a proper code anyhow. Searching for an error you put in at a point when something in the prior code did not work might turn out to be an endless effort to make the code run without erros.

If you feel unsafe about the rest of the code maybe you should consider commenting that part out and leave a note why you did so and when. You may check later on if the code works the way you want it to do and then uncomment the part having a proper code to work with.

Back to the original code: I don´t see any signs for a PathSeparator, and I usually include the type of file I want to check like

Const mcstrPath As String = "E:\Temp\HaHoBe\2022\"
'

Sub THP0205()

Dim strFile As String
Const cstrCALLBACK As String = "THP0205"

strFile = mcstrPath & "Essentials.xlsx"

If Len(Dir(strFile)) > 0 Then
  Workbooks.Open strFile, AddToMRU:=False
Else
  MsgBox "WB '" & strFile & "' couldn´t be found at the place indicated. Called from " & cstrCALLBACK, vbInformation, "My Title Message"
End If

End Sub

Ciao,
Holger