[RESOLVED] Importing text files using vba?

[j.reynolds]

Hi!

I'm trying to import two text files using vba. I can import the first file, no problem, but when I try to import the second one, things get a little screwy. Here's what I have so far...

Sub example(wkbk As Workbook, qrsh, vfilepath As String, ufilepath As String)
       
    With qrsh.QueryTables.Add(Connection:= _
        "TEXT;" & vfilepath, _
        Destination:=qrsh.Range("$A$2"))
        .Name = "SampleTextFileName1"
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlFixedWidth
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 9, 1, 9, 1, 9, 1, 9)
        .TextFileFixedColumnWidths = Array(3, 9, 3, 2, 7, 2, 108, 10, 16, 10)
        .Refresh BackgroundQuery:=False
    End With
    
    x = qrsh.Range("A2").End(xlDown).Row
    
    With qrsh.QueryTables.Add(Connection:="TEXT;" & ufilepath, Destination:=qrsh.Range("$A" & x))
        .Name = "SampleTextFileName2"
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlFixedWidth
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 9, 1, 9, 1, 9, 1, 9)
        .TextFileFixedColumnWidths = Array(3, 9, 3, 2, 7, 2, 108, 10, 16, 10)
        .Refresh BackgroundQuery:=False
    End With

When I import the first file, I get the 7 columns I want from A2, to the last row (normally somewhere in the 800s). Then when I import the second file in the same worksheet at the end of where the first file left off I get my seven columns and all the data, yet the data from the FIRST file gets moved over 7 columns. almost as if the second import is inserting 7 new colums? I'm not really sure if my description makes sense... If anyone has any ideas how to fix this, or a different way to import these files, please let me know! I'm a beginner at this and don't really know what I'm doing?

[vbfbryce]

Not sure what your text files look like, but if they are delimited by a specific character, you could use something like this:

Sub callIt()
    Call ImportTextFile("c:\users\myName\documents\vb\textTest.txt", "^")
    'this calls the sub, passing the file name to read and the delimiting character
End Sub

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

'SaveColNdx = ActiveCell.Column
SaveColNdx = 1  'the start of the data will be in column A in this case
'RowNdx = ActiveCell.Row
RowNdx = 15     'the start of the data will be in row 15, change as necessary

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

[westconn1]

another possibility is to delete the empty cells in front of the first file, after the second file is imported
try like

range("a1:g" & x).Delete xlshifttoleft

[j.reynolds]

Thank you! This works perfectly!

another possibility is to delete the empty cells in front of the first file, after the second file is imported
try like

range("a1:g" & x).Delete xlshifttoleft