Macro bombs out....I want it to continue.

[bigdessert]

here is a macro i have

Sub openandprint()
   
   Dim iMon As Integer
   Dim sDate As String
   Dim i As Integer
   i = 1

PromptForDate:
    yearinput = InputBox("Enter the year in YYYY format(e.g 2007)")
    monthinput = InputBox("Enter the month in MM format (e.g 08)")
    dayinput = 1
    
   sDate = monthinput & "/" & dayinput & "/" & yearinput
   ' If Cancel was pressed, end macro.
   If sDate = "" Then Exit Sub
   ' If Date entered is not a valid date, display message.
   If Not IsDate(sDate) Then
      MsgBox "Please enter a valid date."
      GoTo PromptForDate
   End If
   ' The Month function returns the month number (1-12) of the date.
   Select Case Month(sDate)
      Case 4, 6, 9, 11
         sNumDays = "30"
      Case 2
         If Year(sDate) Mod 4 = 0 Then
            sNumDays = "29"
         Else
            sNumDays = "28"
         End If
      Case Else
         sNumDays = "31"
   End Select
   
    Do While i <= sNumDays
    Sheets("Sheet2").Select
    pathname = "C:\DirectSOFT4\projects\" & Format$(sDate, "yyyy") & "\" & Format$(sDate, "mmm") & "\" & Application.WorksheetFunction.Text(i, "dd") & "\sheet2.csv"
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & pathname, Destination:=Range("A1"))
        .Name = "sheet2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        End With
        
    daygallons = Application.Sum(Range("D2:D1500"))
    daygallons1 = daygallons / 1000000
    MsgBox daygallons
    Sheets("Sheet1").Select
    Cells(i + 2, 3).Value = daygallons1
    Cells(i + 2, 1).Value = Format$(sDate, "mm") & "/" & Application.WorksheetFunction.Text(i, "dd") & "/" & Format$(sDate, "yyyy")
    i = i + 1
    Sheets("Sheet2").Select
    Cells.Select
    Selection.ClearContents
    Loop
    Sheets("Sheet1").Select
    Sheets("Sheet1").Range("G1:H1").Value = Format$(sDate, "mmmm") & " of " & Format$(sDate, "yyyy")
End Sub

now it goes through a loop and a directory structure loading a csv file and summing it and putting it onto a different sheet. Now if lets say there is a missing day so there is no 05 folder, then the macro bombs out with message
"Run-time error '1004':
excel cannot find the text file to refresh this external data range."

Is there a way i can say if directory 04 is not found, consider data to be calculated a 0 value and pass onto the next day, instead of the macro bombin?

Thanks for all your help!

 

[bigdessert]

I figured it out

posting in case some one else needs to search it.

added my modifications in bold below

basically if it detects an error, it specifies a value of 0 and continues.

Sub openandprint()

Dim iMon As Integer
Dim sDate As String
Dim i As Integer
i = 1

PromptForDate:
yearinput = InputBox("Enter the year in YYYY format(e.g 2007)")
monthinput = InputBox("Enter the month in MM format (e.g 08)")
dayinput = 1

sDate = monthinput & "/" & dayinput & "/" & yearinput
' If Cancel was pressed, end macro.
If sDate = "" Then Exit Sub
' If Date entered is not a valid date, display message.
If Not IsDate(sDate) Then
MsgBox "Please enter a valid date."
GoTo PromptForDate
End If
' The Month function returns the month number (1-12) of the date.
Select Case Month(sDate)
Case 4, 6, 9, 11
sNumDays = "30"
Case 2
If Year(sDate) Mod 4 = 0 Then
sNumDays = "29"
Else
sNumDays = "28"
End If
Case Else
sNumDays = "31"
End Select

Do While i <= sNumDays
Sheets("Sheet2").Select
pathname = "C:\DirectSOFT4\projects\" & Format$(sDate, "yyyy") & "\" & Format$(sDate, "mmm") & "\" & Application.WorksheetFunction.Text(i, "dd") & "\sheet2.csv"
On Error Resume Next
If Err = 0 Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & pathname, Destination:=Range("A1"))
.Name = "sheet2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

daygallons = Application.Sum(Range("D2:D1500"))
daygallons1 = daygallons / 1000000
Sheets("Sheet1").Select
Cells(i + 2, 3).Value = daygallons1
Cells(i + 2, 1).Value = Format$(sDate, "mm") & "/" & Application.WorksheetFunction.Text(i, "dd") & "/" & Format$(sDate, "yyyy")
i = i + 1
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
Else
daygallons = 0
daygallons1 = daygallons / 1000000
Sheets("Sheet1").Select
Cells(i + 2, 3).Value = daygallons1
Cells(i + 2, 1).Value = Format$(sDate, "mm") & "/" & Application.WorksheetFunction.Text(i, "dd") & "/" & Format$(sDate, "yyyy")
i = i + 1
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
End If
Loop
Sheets("Sheet1").Select
Sheets("Sheet1").Range("G1:H1").Value = Format$(sDate, "mmmm") & " of " & Format$(sDate, "yyyy")
End Sub