CODEWITHSUNDEEP
vbaexcel
Mario
Mario

Reputation: 177

VBA EXCEL: Fixing "Runtime-error '9'": Subscript out of range

I have the below code which attempts to open a file name which changes on a daily basis if the sheet is empty. The file name is always named as such:

The reason behind this is to automate the file activation instead of copying, pasting, and renaming the file every time. I have received this error when I updated the code to include the above mentioned path. Can someone point to me out where my error is? You may find the code below:

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
If IsEmpty(A_Regular.Range("A2")) Then

Dim TxtPath, TxtName As String

TxtPath = "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"
TxtName = Year(Now()) & Month(Now()) & Day(Now()) & ".txt"

Workbooks.OpenText Filename:=TxtPath, Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=",", FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 5), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
'-------------------------------------------------------------------------'
'                               Setup Sheet                               '
'-------------------------------------------------------------------------'
    With Workbooks(TxtName)
'-------------------------------------------------------------------------'
    Dim TotalFree, TotalFields As Double
        TotalFree = Application.WorksheetFunction.CountIf(Range("D:D"), "FREE")
        Range("A1:E" & TotalFree).Copy
    End With

    Workbooks("Matcher.xlsm").Activate
    Activation.Visible = True
    Activation.Activate

        With Activation
            Range("A:E").PasteSpecial
            Range("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("A1") = "GSM"
            Range("B1") = "Type"
            Range("C1") = "Date"
            Range("D1") = "Status"
            Range("E1") = "Number"
            Range("F1") = "Pattern"
            Call Encryption
        End With
    Activation.Cells.Clear
    Windows(TxtName).Close
    Control.Activate
    Activation.Visible = False
End If

    Application.WindowState = xlMinimized
    NumberManagement.Show 0
End Sub

Upvotes: 0

Views: 64054

Answers (1)

Fratyx
Fratyx

Reputation: 5797

You open a file named "Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt" (filename without path) but you try to reference the sheet by Year(Now()) & Month(Now()) & Day(Now()) & ".txt" without Available_list. A worksheet with this name of course does not exist.

Upvotes: 4

Related Questions