Import multiple files into a single sheet

Hi,
I am in need of a VBA code that will ask for a folder from a user and import all of the files within that directory to a single sheet and stack the data from those files. The files are *.fwd and can be read with notepad as plain text. They seem to be space delimited. The data is arranged in columns and this needs to be preserved once the data is imported. Any help with this?

Drew

Drew, welcome to VBAX!

More info is needed from your part. What is the structure of the data files, what do you mean by "stacking the data", what should the output look like, etc. It would be best if you could upload an example workbook with the desired output, and also a .fwd file to study it's structure.

Jimmy

Ok, i'll try to clarrify. Really i just need a code that will import all of the data on the page without omitting anything and treat it as space delimited. The data text files, which are *.fwd files may not necessarily all be structured the same. There structure for the most part is:

Title: xxxxxx
INfo: xxxxxxxx
More infor:xxxxxxx

Data1 Data2 Data3 Data4 Data5 Data6 Data7
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx

Also, the files will have varying number of rows. If the above is one text file, two files imported (will need more than two imported however) would look like this:

Title: xxxxxx
INfo: xxxxxxxx
More infor:xxxxxxx

Data1 Data2 Data3 Data4 Data5 Data6 Data7
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx

Title: xxxxxx
INfo: xxxxxxxx
More infor:xxxxxxx

Data1 Data2 Data3 Data4 Data5 Data6 Data7
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx

I have a code already that will sort this out once imported. The most important thing is that the data in the format xx xx xx xx be placed in different cells.
Thanks,
Drew

Can't upload more than one file. This is what the data would look like if I used the excel import window and told it to import as space delimited. I have simply copy and pasted this data to show what two files imported would look like.

You can upload several files, just have to zip them into one, first.
Try this code in the target workbook. Change the target worksheet's name (red, bold part of the code) as needed.

Code:

---

Sub ImportFiles()
    Dim Fldr As String, FN As String
    Dim wsDst As Worksheet, rngDst As Range
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "cancelled by user"
            Exit Sub
        End If
        Fldr = .SelectedItems(1)
    End With
    Set wsDst = ThisWorkbook.Sheets("Sheet1")
    FN = Dir(Fldr & "\*.fwd", vbNormal)
    Do While FN <> ""
        Workbooks.OpenText Filename:=Fldr & "\" & FN, Space:=True
        Set rngDst = wsDst.Range("A" & wsDst.Rows.Count).End(xlUp).Offset(1)
        ActiveSheet.UsedRange.Copy rngDst
        FN = Dir()
        ActiveWorkbook.Close False
    Loop
End Sub

---

Note: as far as I know, Excel 2000 (and below) doesn't support FileDialog(msoFileDialogFolderPicker).

HTH

Jimmy

Thanks for the reply Jimmy. Its almost working for me. It is importing multiple files and stacking them the way I like. However, seems to be incorrectly placing the data into columns. Here is a copy of the data which I should have sent to you in the first place.

oops, fwd invalid filetype.

It's only one line of code that must be modified. New code line:

Code:

---

        Workbooks.OpenText Filename:=Fldr & "\" & FN, DataType:=xlDelimited, Space:=True, ConsecutiveDelimiter:=True

---

Now all data get exactly to the place they belong. However, in case data format is important, it's possible that further adjustments will be necessary, because after running this macro, trailing zeros of numeric data are removed.
Let me know what you think of that.

Jimmy

Cross posted ? http://www.excelforum.com/showthread.php?t=646235

Charlize

This works great, exactly what I need. I wasnt aware of cross posting (i admit I may have skimmed the rules a litltle), but the solution is on this page and Ill be sure to post it anywhere I posted the question. Thanks again, I cant tell you how many codes I have tried.