Hi,
Below is macro for importing text files from the Z: Drive.
However if the file does not exist I get run time error 1004.
Instead of this I would like the macro to prompt to search for the next file name.
Your help with modifying the code would be well appreciated
Sub adt_final()
fname = InputBox("Enter Lab Batch #")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\" & fname & ".ADT", _
Destination:=Range("$AA$14"))
Columns("AA:AC").Select
Selection.Delete Shift:=xlToLeft
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.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(9, 24, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 _
, 10, 10, 8, 12)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("AC:AQ").Select
Selection.Delete Shift:=xlToLeft
Range("AF14").Select
ActiveCell.FormulaR1C1 = "1"
Columns("AD:AD").Select
Selection.Delete Shift:=xlToLeft
Range("AE15").Select
ActiveCell.FormulaR1C1 = "2"
Range("AE16").Select
ActiveWindow.SmallScroll Down:=-3
Range("AA13").Select
ActiveCell.FormulaR1C1 = "SAMPLE ID"
Range("AB13").Select
ActiveCell.FormulaR1C1 = "Au(g/t)"
Range("AC13").Select
ActiveCell.FormulaR1C1 = "S(%)"
Range("AE16").Select
ActiveCell.FormulaR1C1 = "3"
Range("AA13:AC13").Select
Selection.Font.Bold = True
Range("AE17").Select
ActiveCell.FormulaR1C1 = "4"
Range("AE18").Select
ActiveWindow.SmallScroll Down:=0
Range("AE14:AE17").Select
Selection.ClearContents
Range("AA14").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub
Below is macro for importing text files from the Z: Drive.
However if the file does not exist I get run time error 1004.
Instead of this I would like the macro to prompt to search for the next file name.
Your help with modifying the code would be well appreciated
Sub adt_final()
fname = InputBox("Enter Lab Batch #")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\" & fname & ".ADT", _
Destination:=Range("$AA$14"))
Columns("AA:AC").Select
Selection.Delete Shift:=xlToLeft
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.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(9, 24, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 _
, 10, 10, 8, 12)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("AC:AQ").Select
Selection.Delete Shift:=xlToLeft
Range("AF14").Select
ActiveCell.FormulaR1C1 = "1"
Columns("AD:AD").Select
Selection.Delete Shift:=xlToLeft
Range("AE15").Select
ActiveCell.FormulaR1C1 = "2"
Range("AE16").Select
ActiveWindow.SmallScroll Down:=-3
Range("AA13").Select
ActiveCell.FormulaR1C1 = "SAMPLE ID"
Range("AB13").Select
ActiveCell.FormulaR1C1 = "Au(g/t)"
Range("AC13").Select
ActiveCell.FormulaR1C1 = "S(%)"
Range("AE16").Select
ActiveCell.FormulaR1C1 = "3"
Range("AA13:AC13").Select
Selection.Font.Bold = True
Range("AE17").Select
ActiveCell.FormulaR1C1 = "4"
Range("AE18").Select
ActiveWindow.SmallScroll Down:=0
Range("AE14:AE17").Select
Selection.ClearContents
Range("AA14").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub