Appending Text Files and Importing

[SWinfield]

Hi,

I'm trying to automate the importing of 4 text files into Excel, but I've so far failed to get my head around appending in VBA!

What I would like to do is have a Macro merge the files called 085.txt, 185.txt, 049.txt & 037.txt into an new file called ratebook.txt.

ratebook.txt would then be imported into excel onto an existing sheet called rate book.

The text files will always be sat in the same folder as the workbook.

I've recorded the macro for the text file import already:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\shaun.winfield\Desktop\085.TXT", Destination _
:=Sheets("rate book").Range("A1"))
.Name = "085"
.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(9, 1, 9, 1, 1, 9)
.TextFileFixedColumnWidths = Array(15, 9, 25, 10, 10)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

Thanks for any advice you can give me!

 

[Gerald Higgins]

Do you absolutely have to create the ratebook.txt file ?
Or would an alternative possibility be to import all four original text files directly into the Excel workbook ?

 

[SWinfield]

Hi,

I just need all four text files to be loaded into the same worksheet, so no I don't have to have a ratebook.txt file. So long as I can get a macro to load all four text files into the same worksheet!

 

[Gerald Higgins]

Are the source files of variable size ? Or are they always the same size, and specifically the same number of rows ?

 

[SWinfield]

They vary in number of rows but the horizontal layout is the same on each one (field widths).

 

[Gerald Higgins]

OK I'll take a look at this. Might be tomorrow before I come up with anything, perhaps others can come up with something faster ?

 

[Gerald Higgins]

See if this gets you going in the right direction. It imports one file, then finds the bottom of the data and imports the next file.

Bear in mind that my code does not have all the same options as your's during the data import stage.

Bear in mind also that you might want to do something to clear out the target range of any old data, before you import new data.

Also, bear in mind that this method of importing data creates a link to the source data file, which can be updated later on. You might want to keep this link, and then again you might not, as the data might change unexpectedly.
I've included some code which effectively deletes that link, by copy/pasting the values of the data to a new area, and then deleting the original data.

    Dim Range2 As Range
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Excel text1.txt", Destination:=Range("A1"))
        .Name = "Excel text1"
        .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 = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    Selection.Offset(rowoffset:=1, columnoffset:=0).Activate
        Set Range2 = Selection
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Excel text2.txt", Destination:=Range2)
        .Name = "Excel text2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1257
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A:B").Select
    Selection.EntireColumn.Delete
    Range("A1").Select

 

[SWinfield]

Cheers Gerald, that seems to be taking me in the right direction! I'm ok with things I can record a Macro for and copy/paste onto the complicated bits so I'm pretty sure this will do exactly what I needed.

Many thanks for taking the time to put some code together for me.