Results 1 to 6 of 6

MS Excel Multiple Files into Spreadsheet

Threaded View

  1. #1
    Registered User
    Join Date
    01-25-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2007
    Posts
    5

    MS Excel Multiple Files into Spreadsheet

    I am wanting to import multiple txt files into a spreadsheet and am trying to write a macro in VBA for this. I have it all working except for the problem that each new file is inserted horizontally instead of vertically below the previous file. This is a problem because all of the files are the exact same format and need the columns to line up. How can I enter a new line in the code so the next file gets put onto a new line below the last file?

    Here is my code:

    Sub LoadPipeDelimitedFiles()
        Dim idx As Integer
        Dim fpath As String
        Dim fname As String
    
        idx = 0
        fpath = "C:\Documents and Settings\bhawtin\Desktop\CSV Example files\"
        fname = Dir(fpath & "*.txt")
        While (Len(fname) > 0)
            idx = idx + 1
        
        Range("A8").Select
        
            With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
              & fpath & fname, Destination:=Range("A2"))
                .Name = "a" & idx
                .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
                .TextFileOtherDelimiter = ","
                .TextFileColumnDataTypes = Array(2, 2, 2)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
                fname = Dir
                
    
    
            End With
        Wend
    End Sub
    Any help is much appreciated thank you!!!
    Last edited by jaym911; 01-25-2011 at 03:13 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1