Help debug my code- subscript out of range?

[jmthompson]

Good afternoon,
I have cobbled together the code below to find a .txt file with a variable name within a specific folder and open it.

Sub text()
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyYear & "\Daily CSV Files\"
Set wb = ActiveWorkbook
With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*baycity_rpt.txt"
        .Execute
 
         Set txtfile = Workbooks.Open(.FoundFiles(1))
    End With
 
End Sub

When the macro hits this line, I get the subscript out of range error.

 Set txtfile = Workbooks.Open(.FoundFiles(1))

My guess is that it either can't find my file or I somehow need to work in the path name since the found file is in a different location than the file which houses this macro.

Any ideas?

 

[Jonmo1]

Yeah, I think it may be the path...

Try

Set txtfile = Workbooks.Open(MyCSVPath & .FoundFiles(1))

 

[schielrn]

You have 2 MyYear in the path above is one of those suppose to be MyMonth?

 

[Norie]

Are you sure any files are actually being found?

 

[iliace]

Execute methods returns 0 if no files are found. So...

        If (.Execute) Then
 
         Set txtfile = Workbooks.Open(.FoundFiles(1))
        Else
          ' no files found
        End If

 

[jmthompson]

D'oh! Schierln was right, my path was wrong!

 

[jmthompson]

Now that I have that squared away, I have hit another obstacle.

My first macro looks for a txtfile with baycity in its name, opens it, converts to excel and makes some modifications then deletes the original text file.

If the text file doesn't exist, I would like it to move to the next macro which is identical, except for the file name. If that file doesn't exist, I would like it to go to End sub.

I just can't figure out the syntax.

Here is my lastest attempt:

Sub Conversion()
Application.ScreenUpdating = False
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
On Error GoTo line60
With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*baycity_rpt.txt"
        .Execute
 
         Set txtfile = Workbooks.Open(.FoundFiles(1))
    End With
 
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A:A").Select
    Selection.Replace What:="Total                        ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                       ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                      ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
Range("A3").Value = Range("A2").Value
    Range("A3").Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Range("A3").Select
    Selection.Replace What:="0", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
 
Cells(lastRow - 3, "B").Value = "test"
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
 
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\baycity " & Range("A3").Value & "-" & Range("B3").Value & "-" & Range("C3").Value & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "*baycity_rpt.txt"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Call Conversion2
End Sub
Sub Conversion2()
Application.ScreenUpdating = False
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
On Error GoTo line123
With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*saginaw_rpt.txt"
        .Execute
 
         Set txtfile = Workbooks.Open(.FoundFiles(1))
    End With
 
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A:A").Select
    Selection.Replace What:="Total                        ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                       ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                      ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
 
Range("A3").Value = Range("A2").Value
    Range("A3").Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Range("A3").Select
    Selection.Replace What:="0", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
 
Cells(lastRow - 3, "B").Value = "test"
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\saginaw " & Range("A3").Value & "-" & Range("B3").Value & "-" & Range("C3").Value & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "*saginaw_rpt.txt"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
End Sub

I am getting label not defined here

On Error GoTo line60

 

[parry]

Hi Goto means go to a label (a specified line in the code). A label is defined as text with a colon after it such as JumpToHere in the following example. In your code you dont have a label named line60 which is why the error occurs.

Sub Example1()
Dim n As Integer

For n = 1 To 100
    If n > 50 Then
        GoTo JumpToHere
    End If
Next n


JumpToHere:
MsgBox "The loop got up to number " & n

End Sub

 

[jmthompson]

Got it, thanks!

Sub Conversion()
Application.ScreenUpdating = False
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
On Error GoTo NextStep
With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*baycity_rpt.txt"
        .Execute
        
         Set txtfile = Workbooks.Open(.FoundFiles(1))
    End With
        
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A:A").Select
    Selection.Replace What:="Total                        ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                       ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                      ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
Range("A3").Value = Range("A2").Value
    Range("A3").Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Range("A3").Select
    Selection.Replace What:="0", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        
Cells(lastRow - 3, "B").Value = "test"
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
        
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\baycity " & Range("A3").Value & "-" & Range("B3").Value & "-" & Range("C3").Value & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "*baycity_rpt.txt"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
NextStep:
Call Conversion2
End Sub
Sub Conversion2()
Application.ScreenUpdating = False
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
On Error GoTo EndSub
With Application.FileSearch
        .NewSearch
        .LookIn = MyCSVPath
        .FileType = msoFileTypeAllFiles
        .Filename = "*saginaw_rpt.txt"
        .Execute
        
         Set txtfile = Workbooks.Open(.FoundFiles(1))
    End With
        
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A:A").Select
    Selection.Replace What:="Total                        ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                       ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Total                      ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        
Range("A3").Value = Range("A2").Value
    Range("A3").Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Range("A3").Select
    Selection.Replace What:="0", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        
Cells(lastRow - 3, "B").Value = "test"
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\saginaw " & Range("A3").Value & "-" & Range("B3").Value & "-" & Range("C3").Value & ".xls"
Application.DisplayAlerts = False
Kill MyCSVPath & "*saginaw_rpt.txt"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
EndSub:
End Sub