How to skip to the next step of a macro rather than have the macro stop on an error

[figgylynn1023]

I get reports for multiple reports that I am building a macro to find whether any entries are duplicated on several reports. However, each report has data coming from three reporting sections, 113, 2856, and 5298.

I have built the macro to pull the saved data (which arrives in text files) and convert them to excel and do the necessary formatting. However, on certain days not every reporting section submits a report.

How can I program the macro to continue with the rest of the steps of formatting with the data it DOES find, rather than error and stop completely just because it doesn't find one set of data for the report?
*Also, is there a way to create an alert that will pop up saying which data it did not find?

Below is the code for the data pulling and formatting for a single report. Each subsequent report uses exact code, swapping out report names only.

Sub BF_Report()
'
' BF_Report Macro
' Convert BF text files to Excel, formats and copies to template.
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Dim LastRow As Long
Dim Day As String

Day = InputBox("Date of File (mmddyy):")
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
' Opening Text Files
Workbooks.OpenText Filename:= _
"\\Drive\Folder\bf" & Day & "_113.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
""\\Drive\Folder\bf" & Day & "_2856.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
""\\Drive\Folder\bf" & Day & "_5298.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
' Insert Org ID
Windows("bf" & Day & "_113.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1000113"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
Windows("bf" & Day & "_2856.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1002856"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
Windows("bf" & Day & "_5298.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1005298"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
' Copy to 113 sheet
Windows("bf" & Day & "_2856.txt").Activate
ActiveSheet.UsedRange.Copy
Windows("bf" & Day & "_113.txt").Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("bf" & Day & "_5298.txt").Activate
ActiveSheet.UsedRange.Copy
Windows("bf" & Day & "_113.txt").Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
' Closing Extra Windows
Windows("bf" & Day & "_2856.txt").Activate
ActiveWindow.Close
Windows("bf" & Day & "_5298.txt").Activate
ActiveWindow.Close
' Insert Report Type
Windows("bf" & Day & "_113.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "BF"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
' Moving Columns
Range("A1").EntireColumn.Insert
Range("F1").EntireColumn.Copy
Range("A1").EntireColumn.PasteSpecial
Range("C1").EntireColumn.Insert
Range("AA1").EntireColumn.Copy
Range("C1").EntireColumn.PasteSpecial
' Move to Template Sheet
ActiveSheet.UsedRange.Copy
Windows("Report_Comparison_Template_1.2.xlsm").Activate
ActiveSheet.Paste
' Close 113 Window
Windows("bf" & Day & "_113.txt").Activate
ActiveWindow.Close

End Sub

 

[marka87uk]

"On Error Resume Next" should work.

 

[figgylynn1023]

Just to make clarify (as I'm new to VBA), I would put this in the code? Where?

 

[marka87uk]

On its own line just under 'Sub BF_Report()'.

 

[figgylynn1023]

I input the line you offered like this:

Code:
Sub BF_Report()
On Error Resume Next
'
' BF_Report Macro
' Convert BF text files to Excel, formats and copies to template.
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Dim LastRow As Long
Dim Day As String

Day = InputBox("Date of File (mmddyy):")
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
End Code

But as it got to the copy and paste sections to move the two extra sheets to the 112 sheet, my data was jumbled, and when it went to copy and paste to the template sheet (final copy), it started moving columns that it shouldn't and then closed my entire template worksheet. Any thoughts?

 

[figgylynn1023]

Seems to me that it kept going with the individual steps of copying and pasting in active sheets even when the active sheet was not the sheet that was supposed to be open (since it didn't exist, which is what would bring up the error message).

Is there a way to say something like:
If there is no document 2856, skip all steps related to 2856? If there is no document 5298, skip all steps related to 5298?

 

[marka87uk]

Yes you could check if the files exists with an IF statement. Unfortunately I can't search at the moment as I have limited Internet access but it would go something like this for each file...

' Reference to a function to see if a file exists
IF FileExists("\\Drive\Folder\bf" & Day & "_5298.txt") = True THEN
' Do whatever routine you require with the file
ELSE
' Do nothing
END IF

 

[kpark91]

Hi, figgy.
Using a "On Error Resume Next" in this case is not ideal for your situation because you will be dealing with the file throughout the code and you would need On Error Resume Next before almost every single line.

So, I would suggest that you take marka87uk's suggestion because you essentially want to get fully RID of the errors not put them under a rug :P

Anyways, to expand on marka87uk's code, there are two ways to find whether the file exists:
1) Using a microsoft scipting runtime in your vba library. This is considered to be much faster and yet much less-error prone. However, it would have to run in OS above or same as Windows XP.
2) Using DIR() function to find if the file exists. Since, this is much easier to do and does not require any additional libraries, I will show you this code:

If Len(Dir("C:\Users\Blarg\Desktop\Data\Hello.xls", vbNormal)) = 0 Then
    MsgBox "The File does not exist!"
    'or your code
End If

You would copy + paste your whole code into that if statement but remember to change ur file path.

 

[Norie]

You should definitely check if the data/file is present rather than using On Error.

Mark has the write idea, what you can use to check if a file exists is use Dir.

If Len(Dir(strFileName)) = 0 Then
  
        MsgBox strFilename & " not found."
       ' put code here to do whatever you want to do when the file isn't found
Else
 
     ' put code here for when the file is found
 
End If

Another thing you might want to do is open each file on it's own, format it or whatever then close it and move onto the next file.

Opening all the files at the beginning is just going to confuse things and cause problems.

If you are doing the exact same thing with each file you might even considering doing the whole thing in a loop, with the file names in an array.

There's a few other things that could probably do with a look at, for example all the unqualified references in the code.

For example when you have something like this which doesn't have a worksheet reference:

Range("A1").EntireColumn.Insert

VBA will run this code on whatever worksheet/workbook it regards as active, which may or may not be the right one.

You actually appear to have encountered a similar problem when you say the data was jumbled up.

 

[figgylynn1023]

This code ended up working!

    If Len(Dir("\\Drive\Folder\bf" & Day & "_113.txt", vbNormal)) = 0 Then
        MsgBox "The 113 File does not exist!"
    Else
        Workbooks.OpenText Filename:= _
            "\\Drive\Folder\bf" & Day & "_113.txt" _
            , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
            Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
            Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
            , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
            Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
            TrailingMinusNumbers:=True
        Windows("bf" & Day & "_113.txt").Activate
            Range("A1").EntireColumn.Insert
            Range("A1").FormulaR1C1 = "113"
            Range("B1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Offset(0, -1).FillDown
    End If

Thank you everyone for all of your assistance! Saved a lot of time and frustration!