Looping Text Import problem
 

Hi all,

I have a macro that imports text files based on user input. My problem is
that after about 350 imports the data jumps across the page and inserts
itself where it likes!! The code I have is below...

Private Sub CommandButton1_Click()
Dim fName As String
Dim ext As String
Dim dest As Integer
Dim fileCounter As Integer
Dim endFileCounter As Integer

fileCounter = UserForm1.StartNumber.Value
endFileCounter = UserForm1.EndNumber.Value
ext = ".C09"
dest = 1

Do While fileCounter <= endFileCounter
fName = "U:\Scandat\DownloadedWipLabour\L000" & fileCounter & ext
If fileCheck(fName) Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=Range("$A$" & dest _
))
.Name = "L000" & fileCounter
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.Refresh BackgroundQuery:=False
End With
Else
End If
fileCounter = fileCounter + 1

If markBlanks("A" & dest) Then
Range("A" & dest).Value = "BLANK FILE " & fName
Else
End If

dest = dest + 1
Loop

'Application.ScreenUpdating = True

End Sub

Public Function fileCheck(fileName As String) As Boolean
fileCheck = (Dir(fileName) "")
End Function

Public Function markBlanks(cellPosition As String) As Boolean
markBlanks = Range(cellPosition).Value = ""
End Function

Each file that's imported is single line text.

Is there any reason for the data jumping across the page?

NB: It does this in XL2000 and XL2003

Looping Text Import problem
 

Actually.....fear not I found the problem.

I hadn't realised that someone had 'sneaked' four multiline textfiles into
my import directory causing each import to enter into an adjacent column!
Removed them and it's now perfectly fine.