Below is a macro I recorded for importing the text file AJ9285 into excel from an external location.
I would like to modify it so that excel will give me a prompt and I can type the text file name in.
Could you kindly help me out please? Thanks
Sub adt()
'
' adt Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\AJ9285.ADT", _
Destination:=Range("$Z$13"))
.Name = "AJ9285"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 25, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 _
, 10, 10, 8, 12)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("AB:AP").Select
Selection.Delete Shift:=xlToLeft
Range("AE13").Select
ActiveCell.FormulaR1C1 = "1"
Columns("AC:AC").Select
Selection.Delete Shift:=xlToLeft
Range("AD14").Select
ActiveCell.FormulaR1C1 = "2"
Columns("AD:AD").Select
Selection.Delete Shift:=xlToLeft
Range("AC20").Select
End Sub
I would like to modify it so that excel will give me a prompt and I can type the text file name in.
Could you kindly help me out please? Thanks
Sub adt()
'
' adt Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\AJ9285.ADT", _
Destination:=Range("$Z$13"))
.Name = "AJ9285"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 25, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 _
, 10, 10, 8, 12)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("AB:AP").Select
Selection.Delete Shift:=xlToLeft
Range("AE13").Select
ActiveCell.FormulaR1C1 = "1"
Columns("AC:AC").Select
Selection.Delete Shift:=xlToLeft
Range("AD14").Select
ActiveCell.FormulaR1C1 = "2"
Columns("AD:AD").Select
Selection.Delete Shift:=xlToLeft
Range("AC20").Select
End Sub