using a wildcard within VBA to format a worksheet

[manx viking]

Hi, newbie so please be gentle.

I have a daily generated text file which is always post-fixed with random letters, eg FILENAMEABC.txt

I can easily open this file up via VBA using a wildcard -

Workbooks.OpenText Filename:="C:\filename*.txt"

However what I am struggling with is using VBA to then select the worksheet.

As its a text file the worksheet will always pick up the file name so I need my VBA to be able to use a wildcard here.

I have tried to use a wildcard using the sheet name and also tried using the sheet code name (Sheet1) with little success.

Can anyone point me in the right direction.

Many thanks.

Sub Macro1()

'sample that works if filename is not variable.

  Workbooks.OpenText Filename:="H:\FILENAMEABC.TXT", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, OtherChar:="³"
    Range("A12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("FILENAMEABC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("FILENAMEABC").Sort.SortFields.Add Key:=Range( _
        "B13:B695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("FILENAMEABC").Sort
        .SetRange Range("A12:O695")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub Macro1()

'sample that semi works using a wildcard - will open workbook but errors on worksheet selection.

  Workbooks.OpenText Filename:="H:\FILENAME*.TXT", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, OtherChar:="³"
    Range("A12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("FILENAME*").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("FILENAME*").Sort.SortFields.Add Key:=Range( _
        "B13:B695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("FILENAME*").Sort
        .SetRange Range("A12:O695")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub Macro1()

'sample that semi works using a wildcard - will open workbook but errors on worksheet selection using code name

  Workbooks.OpenText Filename:="H:\FILENAME*.TXT", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, OtherChar:="³"
    Range("A12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Worksheets("Sheet1").Sort.SortFields.Clear
    Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "B13:B695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Worksheets("Sheet1").Sort
        .SetRange Range("A12:O695")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Joe4]

Welcome to the Board!

Since text files only have one sheet, you do not need to reference the sheet name.
You could just use:

ActiveSheet.

in all your references of

Worksheets("Sheet1").

Alternatively, you could "capture" the name of the sheet when you first open the file, i.e.

Set ws = ActiveSheet

Then you can replace all

Worksheets("Sheet1").

references with

ws.

 

[DanteAmor]

Hi and welcome to MrExcel

Txt files only have one sheet, so we can use sheet 1:

Sub Macro1()

'sample that semi works using a wildcard - will open workbook but errors on worksheet selection.

  Workbooks.OpenText Filename:="H:\FILENAME*.TXT", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, OtherChar:="³"
    Range("A12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range( _
        "B13:B695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(1).Sort
        .SetRange Range("A12:O695")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Simplified code:

Sub Macro1()
'sample that semi works using a wildcard - will open workbook but errors on worksheet selection.
  Dim lr As Long, lc As Long
  Workbooks.OpenText Filename:="H:\FILENAME*.TXT", Origin:=xlWindows, _
      StartRow:=1, DataType:=xlDelimited, OtherChar:="³"
  lr = Range("B" & Rows.Count).End(3).Row
  lc = Cells(12, Columns.Count).End(1).Column
  Range("A12", Cells(lr, lc)).Sort Range("B13"), xlAscending, Header:=xlYes
End Sub

 

[manx viking]

Many thanks, that works perfectly

 

[DanteAmor]

Glad we could help & thanks for the feedback