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
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
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.
[vba]
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[/vba]
Note: as far as I know, Excel 2000 (and below) doesn't support FileDialog(msoFileDialogFolderPicker).
HTH
Jimmy
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
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:
[vba] Workbooks.OpenText Filename:=Fldr & "\" & FN, DataType:=xlDelimited, Space:=True, _
ConsecutiveDelimiter:=True
[/vba]
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
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
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.
Posting Permissions
Reply With Quote
