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

bigdessert

New Member
Joined
Mar 28, 2006
Messages
15
here is a macro i have

Code:
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

bigdessert

New Member
Joined
Mar 28, 2006
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,516
Messages
5,625,256
Members
416,085
Latest member
Jlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top