I have recently recorded a macro to import data from an external data source. The code below is basic tweak of that recording.
What i essentially want to do is have this code loop through
Worksheets("FileList").Range("I1") to
Worksheets("FileList").Range("I6") as there are 6 cells that contain the directories of the files. Also a problem that has poped its head up is when im importing multiple files. The first file will import with no issues, but when it does the 2nd, 3rd etc it will most the previous import to the right instead of below the new import. Im sure there is something im not seeing here and your help would be greatly appreciated.
What i essentially want to do is have this code loop through
Worksheets("FileList").Range("I1") to
Worksheets("FileList").Range("I6") as there are 6 cells that contain the directories of the files. Also a problem that has poped its head up is when im importing multiple files. The first file will import with no issues, but when it does the 2nd, 3rd etc it will most the previous import to the right instead of below the new import. Im sure there is something im not seeing here and your help would be greatly appreciated.
Code:
Sub Import1()
Sheets("Import").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
Worksheets("FileList").Range("I1").Value, Destination:=Range("A1"))
.Name = ""
.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, 1, 1, _
1, 1, 1)
.TextFileFixedColumnWidths = Array(21, 4, 3, 4, 23, 25, 31, 23, 23, 7, 5, 6, 6, 5, 8, 8, 8, _
36, 8, 7, 5, 4, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
End Sub