Closing File with Wild Cards

[Shawn09]

Hello everyone!

I'm having an issue closing a worksheet in my VBA code. I can open it just fine in VBA using wildcards but for some reason it won't close that same file. It gives me an error message "subscript out of range".

It's the Windows(MyFile).Close that creates the error. I know it has something to do with the wildcards but not sure what... any help would be great appreciated!!!

Here is my code:

Sheets("2700 Cash Files").Select

Range("L1").Value = 1

Application.DisplayAlerts = False
Dim MyPath As String
Dim MyFile As String

MyPath = "C:\Users\User\Desktop\Export Files\"

MyInput = Application.InputBox("Enter Week Start Date (Format:MM-DD-YYYY)")
        If MyInput = False Then End
        If MyInput = "" Then End
        Range("M1").Value = MyInput

MyFile = Range("N1") & "******_200100.cas"

    ChDir MyPath & MyInput
    Workbooks.OpenText Filename:=MyFile _
        , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, Tab:=True, Comma:=True
        Range("A1:HS1").Copy
        
    Windows("Exports.xls").Activate
    Sheets("2700 Cash Files").Select
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Windows(MyFile).Close
    Range("A1").Select

 

[shg]

Use an object variable:

    Dim wkb         As Workbook
 
    Set wkb = Workbooks.OpenText(Filename:=MyFile, _
                                 Origin:=xlWindows, _
                                 StartRow:=1, _
                                 DataType:=xlDelimited, _
                                 TextQualifier:=xlDoubleQuote, _
                                 Tab:=True, _
                                 Comma:=True)
    '...
    wkb.Close

 

[Shawn09]

Now I get a Compile error message "Expected Function or variable"

Here is what I tried:

Dim wkb As Workbook
Set wkb = Workbooks.OpenText(Filename:=MyFile _
        , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, Tab:=True, Comma:=True)
        Range("A1:HS1").Copy
        
    Windows("Exports.xls").Activate
    Sheets("2700 Cash Files").Select
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wkb.Close

 

[shg]

On what line?

You still have the rest of the code that initializes the variables?

 

[Shawn09]

It highlights the top line Sub Exports2700()

Here's the entire code:

Sub Exports2700()

Sheets("2700 Cash Files").Select

Range("L1").Value = 1

Application.DisplayAlerts = False
Dim MyPath As String
Dim MyFile As String

MyPath = "C:\Users\User\Desktop\Export Files\"

MyInput = Application.InputBox("Enter Week Start Date (Format:MM-DD-YYYY)")
        If MyInput = False Then End
        If MyInput = "" Then End
        Range("M1").Value = MyInput

MyFile = Range("N1") & "******_200100.cas"

    ChDir MyPath & MyInput
Dim wkb As Workbook
Set wkb = Workbooks.OpenText(Filename:=MyFile _
        , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, Tab:=True, Comma:=True)
        Range("A1:HS1").Copy
        
    Windows("Exports.xls").Activate
    Sheets("2700 Cash Files").Select
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wkb.Close
    Range("A1").Select

 

[Shawn09]

Sorry... did it again and it highlights the .OpenText in the Set wkb = Workbooks.OpenText(Filename:=MyFile.....

 

[shg]

Try this:

Sub Exports2700()
    Const sPath     As String = "C:\Users\User\Desktop\Export Files\"
    Dim sFile       As String
    Dim vsDate      As Variant
 
    vsDate = Application.InputBox("Enter Week Start Date (MM-DD-YYYY)")
    If Not vsDate Like "##-##-####" Then
        MsgBox "Invalid date entry"
        Exit Sub
    End If
 
    With Workbooks("Exports.xls").Worksheets("2700 Cash Files")
        .Range("M1").Value = vsDate
        .Range("L1").Value = 1
        sFile = sPath & vsDate & "\" & .Range("N1").Value & "******_200100.cas"
        If Len(Dir(sFile)) = 0 Then
            MsgBox "File " & sFile & " does not exist"
            Exit Sub
        End If
 
        Workbooks.OpenText Filename:=sFile, _
                           Origin:=xlWindows, _
                           StartRow:=1, _
                           DataType:=xlDelimited, _
                           TextQualifier:=xlDoubleQuote, _
                           Tab:=True, _
                           Comma:=True
        Range("A1:HS1").Copy
        .Range("B2").PasteSpecial Paste:=xlPasteValues
        ActiveWorkbook.Close SaveChanges:=False
        .Range("A1").Select
    End With
End Sub

 

[Shawn09]

Awesome! Thank you so much for your help! I learned a better way to set up the VBA! That's what I love about Excel! Works like a charm!

 

[shg]

You're welcome, glad it worked for you.