PDA

View Full Version : Import multiple files into a single sheet



Jdrewrd
06-04-2008, 07:34 PM
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

JimmyTheHand
06-05-2008, 02:48 AM
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

Jdrewrd
06-05-2008, 05:13 AM
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

Jdrewrd
06-05-2008, 05:24 AM
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.

JimmyTheHand
06-05-2008, 08:13 AM
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.

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

Jdrewrd
06-05-2008, 09:14 AM
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.

Jdrewrd
06-05-2008, 09:16 AM
oops, fwd invalid filetype.

JimmyTheHand
06-05-2008, 10:42 PM
It's only one line of code that must be modified. New code line:

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

Charlize
06-06-2008, 04:31 AM
Cross posted ? http://www.excelforum.com/showthread.php?t=646235

Charlize

Jdrewrd
06-06-2008, 05:20 AM
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.