using a wildcard within VBA to format a worksheet

manx viking

New Member
Joined
Jun 22, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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 -
VBA Code:
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.

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,112
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Since text files only have one sheet, you do not need to reference the sheet name.
You could just use:
VBA Code:
ActiveSheet.
in all your references of
VBA Code:
Worksheets("Sheet1").

Alternatively, you could "capture" the name of the sheet when you first open the file, i.e.
VBA Code:
Set ws = ActiveSheet
Then you can replace all
VBA Code:
Worksheets("Sheet1").
references with
VBA Code:
ws.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,639
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

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

VBA Code:
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:
VBA 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
 
Last edited:
Solution

Forum statistics

Threads
1,141,865
Messages
5,709,076
Members
421,613
Latest member
wyzco

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