manx viking
New Member
- Joined
- Jun 22, 2021
- Messages
- 2
- Office Version
- 2019
- Platform
- 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 -
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.
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