Macro for importing text files but the file name changes

[CHARI]

Are you able to build a macro to import a text file the same every time but
the file name is different each time?

 

[Dave Peterson]

Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

 

[CHARI]

I'm really new to this. Can you tell me where I would put this code exactly
in my macro?

Sub test()
'
' test Macro
' Macro recorded 02/26/2009 by cwanta01
'

'
Workbooks.OpenText Filename:= _
"\\spsc_ntb01sp\cwanta01$\My Documents\WANDA\CD028 022409.RPT",
Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2,
1), Array(3 _
, 9), Array(4, 1), Array(5, 9), Array(6, 2), Array(7, 1), Array(8,
9), Array(9, 1), Array(10, _
9)), TrailingMinusNumbers:=True
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End Sub

 

[Dave Peterson]

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 1), _
Array(3, 9), Array(4, 1), _
Array(5, 9), Array(6, 2), _
Array(7, 1), Array(8, 9), _
Array(9, 1), Array(10, 9)), _
TrailingMinusNumbers:=True

With ActiveSheet.UsedRange
.Cells.Sort _
Key1:=.Columns(1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With

End Sub

Ps. Give the macro a nice, unique, meaningful name.