Run-time Error 1004; Error handling

Question

I've got a problem with my error handling. I'm stitching together a series of numbered files, for multiple business locations. I've written the code to move to the next location loop on the error of "file not there", because each location has a different number of files.

Even with the error handling, I'm getting a Run-time 1004 error. How can this be? It bombs on the second location loop, works on the first. I've used a 15-file loop to be safe, because the max I have encountered is 12.

Sub OpenURL()

Dim LocBackupFile As String
    Dim CurrentFile As String
    Dim HubFileName As String

Application.DisplayAlerts = False
    Filedate = Format(Date, "mm-dd-yyyy")

'Cycle through Hubs
    HubArray = Array("GA100%20-%20AHUB", "TX100%20-%20DHUB", "CA200%20-%20HHUB", "IN100%20-%20IHUB", "WA100%20-%20KHUB", _
            "AB100%20-%20LHUB", "MO100%20-%20MHUB", "NC100%20-%20NHUB", "OH100%20-%20OHUB", "PA100%20-%20SHUB", _
            "IN200%20-%20THUB", "UT100%20-%20UHUB", "ON100%20-%20VHUB", "MN100%20-%20WINO", "NL100%20-%20YHUB")

For Hub = LBound(HubArray) To UBound(HubArray)

HubName = Left(HubArray(Hub), 5)
    HubFileName = HubName & " NoLocBackup " & Filedate & ".xlsb"

For CheckAndOpen = 1 To 15

LocBackupFile = "http://fastnet.--------.com/sites/fastreports/Hubs/" & HubArray(Hub) & "/locbackup_ws" & CheckAndOpen & ".xls"

On Error Resume Next
            Workbooks.Open FileName:=LocBackupFile
            On Error GoTo Done

CurrentFile = "locbackup_ws" & CheckAndOpen & ".xls"
            If CheckAndOpen = 1 Then
                RowCount = ActiveSheet.UsedRange.Rows.Count
                Workbooks.Add.SaveAs FileName:="R:" & HubFileName, FileFormat:=50
                If RowCount >= 65000 Then
                    DestRowCount = 65001
                End If
                Workbooks(CurrentFile).Sheets(1).Range("A1:H" & RowCount).Copy Destination:=Workbooks(HubFileName).Sheets(1).Range("A1")
            Else
                RowCount = ActiveSheet.UsedRange.Rows.Count
                If RowCount < 64999 Then
                    Workbooks(CurrentFile).Sheets(1).Range("A3:H" & RowCount).Copy Destination:=Workbooks(HubFileName).Sheets(1).Range("A" & DestRowCount)
                Else
                    Workbooks(CurrentFile).Sheets(1).Range("A3:H65000").Copy Destination:=Workbooks(HubFileName).Sheets(1).Range("A" & DestRowCount)
                    DestRowCount = DestRowCount + 64998
                End If
            End If

Workbooks(CurrentFile).Close SaveChanges:=False

Next CheckAndOpen

Done:

On Error GoTo 0

Workbooks(HubFileName).Save
    Workbooks(HubFileName).Close

Next Hub

Application.DisplayAlerts = True

End Sub

antonismark · Answer

I have had the same issue (error handling not catching error 1004). 
This is how i solved it:
I put the code inside the For loop in a separate "child" sub (i.e. process_file), and passed to it all the required parameters. 
for you: In the "mother" sub OpenURL(), you would have:
On Error GoTo err_line 'or do something else
For CheckAndOpen = 1 To 15
   has_err = True
   Call process_file( all required parameters here)
   has_err = False
err_line:
   if has_err then
      'your handling code goes here
   End If
Next

and outside the mother sub OpenURL() you would have:
Sub process_file(all required parameters here)
  '...code from the for in the mother....
End sub

The error handling in the child-sub process_file will still work, but if everything fails the mother will resume.
Edit
You may want to use this method to catch only the line that breaks the code (put that line only in a child sub, and do the error handling in the parent). Also, this is a work around, and not an actual fix to the issue.

SierraOscar · Answer

Go to Tools->Options-> and on the General tab change "Error Trapping" to "Break on Unhandled Errors"

Run-time Error 1004; Error handling

2 Answers

Add your own answers!

Ask a Question