How to skip a block of code if file is not found?

Question

I have code that pulls .csv documents together into a single workbook and edit a few minor things. It creates a workbook, shuffles in the files from a "Staging Folder" and then autofits/hides some columns/makes row 1 bold.

The .csv's are an output from a Python program, and it can range anywhere from 1-8 files. Each of the eight files will have a unique name that will remain constant with every output.

For Example:
Geometry Errors will ALWAYS give an output named Geometry Errors.
The output will always have some combination of the eight files depending on the errors found.

If I don't have all eight files, then the code doesn't work correctly.
It looks in the staging folder named "DVIEW Staging" and if it doesn't find the file I have the On Error Resume Next line.

Application.DisplayAlerts = False
Set newbook = Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & "DVIEW Outputs.xlsx"
    
Environ ("USERPROFILE") + "\DVIEW Staging"
On Error Resume Next

Workbooks.Open Filename:= _
        Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Geometry_Errors_Table.csv"
        On Error Resume Next
    Sheets("Geometry_Errors_Table").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
        Columns("A:Z").EntireColumn.AutoFit
        Range("A:A,B:B,C:C,I:I,J:J,K:K,L:L").Select
        Selection.EntireColumn.Hidden = True
        Rows("1:1").Select
        Selection.Font.Bold = True
        Range("D1").Select
        On Error Resume Next
        
Workbooks.Open Filename:= _
        Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Fiber_and_Splice_Relationship_Errors.csv"
        On Error Resume Next
    Sheets("Fiber_and_Splice_Relationship_E").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
        Columns("A:Z").EntireColumn.AutoFit
        Range("A:A,C:C").Select
        Selection.EntireColumn.Hidden = True
        Rows("1:1").Select
        Selection.Font.Bold = True
        Range("B1").Select
        On Error Resume Next

So when it can't find it, it runs all of the hiding and autofit commands on the CURRENT sheet (one that it found). Meaning if there is one sheet is in the output folder, it could potentially run the autofit/hide/bold seven other times.

How do I block off a section of code to skip if it can't find the document?

I want the block of code beneath the file search to run on THAT file, or not run at all.
I don't want the Geometry Errors block of code to hide seven columns on the Fiber_and_Splice_Relationship_Errors sheet, and then run the Fiber_and_Splice_Relationship_Errors commands after that (because I only want the two columns hidden for that one).

Possible duplicate of [VBA check if file exists](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists) — BruceWayne, Dec 21 '18 at 19:26
That answer says to exit the sub if the file is not found, I do not want it to completely exit the rest of the macro. I want it to run all 8 blocks if file exists, but skip the singular block of code if THAT file does not exist. — Just Another Guy, Dec 21 '18 at 19:52
Have you tried using an `If` statement to see if the .csv file exists first and if the file doesn't exist in the directory use `GoTo` instead of `On Error Resume Next` to look for the next .csv file. `On Error Resume Next` will just ignore the error and run the next line of code. — Zack E, Dec 21 '18 at 20:10
@J.Wagner consider breaking down your procedure into smaller sub-procedures then: your macro is doing too many things, that's why - *and* it needs control flow statements, e.g. `If...Then...Else...End If`. Also note that repeatedly running `On Error Resume Next` doesn't do anything; once is already enough - if needed at all (it rarely is). — Mathieu Guindon, Dec 21 '18 at 20:38
Zack E, I think that would be a great way to go about it! Let me check out the other answers options first though. — Just Another Guy, Dec 26 '18 at 13:30
Mathieu, i have a code that is 3 times as big as this (does a similar, but not the same exact function), and it doesn't have any hangups like this. the difference there is, there is a template to copy stuff into, and all sheets exist before the code is ran. — Just Another Guy, Dec 26 '18 at 13:31

score 1 · Answer 1 · answered Dec 21 '18 at 20:24

1

Try the below:

Option Explicit

Sub test()

    Dim FileName As String
    Dim Directory As String

    FileName = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

    If Len(FileName) = 0 Then
        Exit Sub
    End If

    Directory = "C:\Users\mario\Desktop\Marios\" & FileName

    If Len(Dir(Directory)) = 0 Then
      MsgBox "File does not exist"

    End If

End Sub

answered Dec 21 '18 at 20:24

Error 1004

7,877
3
23
46

This would work if i was just concerned about if the file exists. And I definitely DON'T want the Sub to exit if it can't find one of the 8 files. The point is i want it to run each of the potential 8 files separately, but skip a block of code if it can't find the document. So we're halfway there. Let's look for the document in a specific folder, and if it's not there have run a GoTo line to the next block of code i'm wanting. Now my issue is I don't know how to set the folder and file for each block of code specifically. – Just Another Guy Dec 26 '18 at 14:19

score 1 · Accepted Answer · answered Dec 26 '18 at 16:11

I found the answer finally! Zack E put me on the right path, but here is the answer to have it do what I wanted it to do:

If MsgBox("This macro will combine DVIEW outputs. Do you wish to continue?", vbYesNo) = vbNo Then Exit Sub

    Dim FileGeoErrors As String
    Dim FileFiberAndSplice As String
Set newbook = Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & "DVIEW Outputs.xlsx"

FileGeoErrors = Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Geometry_Errors_Table.csv"
FileFiberAndSplice = Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Fiber_and_Splice_Relationship_Errors.csv"

If Dir(FileGeoErrors) <> "" Then
Workbooks.Open Filename:=FileGeoErrors
Sheets("Geometry_Errors_Table").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
            Columns("A:Z").EntireColumn.AutoFit
            Range("A:A,B:B,C:C,I:I,J:J,K:K,L:L").Select
            Selection.EntireColumn.Hidden = True
            Rows("1:1").Select
            Selection.Font.Bold = True
            Range("D1").Select
Else: GoTo 1
End If

1:
If Dir(FileFiberAndSplice) <> "" Then
Workbooks.Open Filename:=FileFiberAndSplice
Sheets("Fiber_and_Splice_Relationship_E").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
            Columns("A:Z").EntireColumn.AutoFit
            Range("A:A,C:C").Select
            Selection.EntireColumn.Hidden = True
            Rows("1:1").Select
            Selection.Font.Bold = True
            Range("B1").Select
Else: GoTo 2
End If

2:
If Dir(FileFiberCircuits) <> "" Then
Workbooks.Open Filename:=FileFiberCircuits
Sheets("Fiber_Has_Circuits").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
            Columns("A:Z").EntireColumn.AutoFit
            Range("A:A").Select
            Selection.EntireColumn.Hidden = True
            Rows("1:1").Select
            Selection.Font.Bold = True
            Range("B1").Select
Else: GoTo 3
End If

The key here is the Directory search, and if it finds the file it executes the block of code. If it doesn't it jumps to the next block that is numbered in order. This way it cycles through all 8 POTENTIAL sheets, runs code if it exists, or ignores it if it doesn't.

Glad you were able to get a solution. Another way would be able to set up a boolean function that looks for the .txt file and assign a variable if the file exists then look for that specific variable within a module. It might help streamline the code a little too. — Zack E, Dec 26 '18 at 16:23
Zack, I really appreciate your help and quick responses. This was driving me insane, and as always, it was fairly simple to resolve when it got down to it. — Just Another Guy, Dec 26 '18 at 16:33

Zack E · Answer 3 · 2018-12-26T14:44:55.567

Untested, but something like this may work and of course edit it to suit your needs.

Sub Test()
    Dim FileGeoErrors As String
    Dim FileFiberAndSplice As String
    Dim GeoErrPath As String, FiberSplicePath As String
    Set newbook = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:=Aname & "DVIEW Outputs.xlsx"

    Environ ("USERPROFILE") + "\DVIEW Staging"

    FileGeoErrors = Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Geometry_Errors_Table.csv"
    FileFiberAndSplice = Environ("USERPROFILE") & "\Desktop\DVIEW Staging\Fiber_and_Splice_Relationship_Errors.csv"


    Workbooks.Open Filename:=FileGeoErrors
    Sheets("Geometry_Errors_Table").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
    GeoErrPath = FileGeoErrors
    ActiveSheet.Range("ZZ125") = GeoErrPath
        If Len(GeoErrPath) > 0 Then
            Columns("A:Z").EntireColumn.AutoFit
            Range("A:A,B:B,C:C,I:I,J:J,K:K,L:L").Select
            Selection.EntireColumn.Hidden = True
            Rows("1:1").Font.Bold = True
            Range("D1").Select
        End If
    Workbooks.Open Filename:=FileFiberAndSplice
    Sheets("Fiber_and_Splice_Relationship_E").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
    FiberSplicePath = FileFiberAndSplice
    ActiveSheet.Range("ZZ125") = FiberSplicePath
        If Len(FiberSplicePath) > 0 Then
            Workbooks.Open Filename:=FileFiberAndSplice
            Sheets("Fiber_and_Splice_Relationship_E").Move After:=Workbooks("DVIEW Outputs.xlsx").Sheets(1)
            Columns("A:Z").EntireColumn.AutoFit
            Range("A:A,C:C").Select
            Selection.EntireColumn.Hidden = True
            Rows("1:1").Font.Bold = True
            Range("B1").Select
        End If

End Sub

I can't make the If Len statement work, it keeps thinking all are zero's — Just Another Guy, Dec 26 '18 at 14:15
I updated the answer to save the file path to a cell in the active worksheet then test to see if the path in the cell was greater than 0. You can change the `If Len(GeoErrPath) > 0` to `If GeoErrPath<>vbNullstring` as well. — Zack E, Dec 26 '18 at 14:47
It still wont work if a file doesn't exist. When it tried to open the workbook via filename and it doesn't exist it wont continue the code, instead the debugger comes up saying the file can't be found. If I "On Error Resume Next" it goes back to the original issue. I can't seem to make On Error GoTo work either. This works fine if all files are available, but that will rarely be the case. — Just Another Guy, Dec 26 '18 at 15:40
Just so you are aware. I am looking up as many things as possible and have been trying to get this to work all morning. I am not just waiting for a response, so if I come up with anything I will let you know. I just cant get it to get past a file if it doesn't exist. — Just Another Guy, Dec 26 '18 at 15:52

How to skip a block of code if file is not found?

3 Answers3