import file problem

**naaitie** · 10-30-2007, 07:16 AM

Hi,

I have a problem importing a file into Excel. Problem is that Excel doesn't recognize the new line character used in the file. The following code puts all data into one line, while I need to have them seperated. Working with the ResultStr results in an 'out of memory' exception. (I actually only need one particular line from my file.)

    FileNum = FreeFile
    Open "C:\Temp\LIJST.TXT" For Input As #FileNum

    Do Until EOF(FileNum)
        Line Input #FileNum, ResultStr
    Loop
    Close FileNum

The automatic way does work, but this will chop off the wanted line to 256 chars. (line 8, the one with all the '--')

Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Temp\LIJST.TXT", _
        Destination:=Range("A1"))
        .Name = "ANALYSE"
        .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 = 4
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
'        .TextFileFixedColumnWidths = Array(9, 9, 11, 6, 9, 302, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Anyone knows the best way to fetch that line of code from the file?

thanks in advance,

Sven

*update*
apparently, the automatic way doesn't chop off other strings longer then 256 chars. It only does that with the '--'-line?

**rylo** · 10-30-2007, 07:11 PM

Sven

The file doesn't have a CR, or a CR LF required for the line input.

I opened the text file in Word, then resaved as a new file using the windows default to make it readable. I then saved the file as a new text file (LIJST2.TXT) using the windows default and the CR/LF selection in the ends lines with.

The following seemed to parse the file correctly and output to the current sheet.

Sub aaa()
  FileNum = FreeFile
    Open "C:\Temp\LIJST2.TXT" For Input As #FileNum

    Do Until EOF(FileNum)
        Line Input #FileNum, resultstr
        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = resultstr
    Loop
    Close FileNum
End Sub

HTH

rylo

**naaitie** · 10-31-2007, 08:03 AM

I have indeed noticed that it worked when saving the file through Wordpad first. However, as the input-file is generated automatically from an other program, it is not possible to change anything concerning this file.

I have tried your code on the original file, but that didn't work either.

thanks for the input,

Sven

**mudraker** · 10-31-2007, 08:23 AM

Have you tried importing the file without splitting ito columns 1st

Sub Macro2()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\My Documents\VBA Testing\LIJST.txt", Destination:=Range("A1"))
        .Name = "LIJST_1"
        .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 = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

This give row 8 with 352 characters

**naaitie** · 10-31-2007, 12:23 PM

thanks! This thing does the trick.

I had already tried without splitting it into columns & it didn't help, but your code did!

One performance-remark: is it possible to add something like an 'TextFileEndRow = x' to that code? Then I could specify that it should only fetch that one line.
I have google'ed on this but it came up empty.