Multiple text file importing

[Davorito]

Hi guys,


I want to store data in 1 worksheet from multiple .txt files. Also, I want the first cell to contain the file name not the file path (if possible) so I can link it to the graphs later on. There is also AT MOST 7 columns in the data while the number of rows is variable and the first column of each array is separated by 7 columns.


Dim myFile As String
Dim myValue As Integer
Dim rData As Integer
Dim Data As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String


Dim Delimiter As String
Dim row As Integer
Dim counter As Integer
Dim counterArrSep As Integer
Dim FileName As String










Sub Button1_Click()

'Input number of employees
myValue = InputBox("Please enter the number of employees below", "number of employees", vbOKCancel)

'Cancel (doesn't work properly)
If myValue = 0 Then
Exit Sub
End If

'Inputs
Delimiter = " "
row = 1

'Populate the table
Do While counter < myValue


'.txt file processing


'Show open file dialog box
myFile = Application.GetOpenFilename()

'Cancel
If myFile = "False" Then
Exit Sub
End If

'Get file name (doesn't work)
FileName = Dir(myFile, vbDirectory)
Dim DataArray()
DataArray(counterArrSep, 0) = FileName

'Open file
rData = FreeFile
Open myFile For Input As rData

'Store file content inside a variable
Data = Input(LOF(rData), rData)

'Close file
Close rData

'Separate Out lines of data
LineArray() = Split(Data, vbCrLf)


'Read Data into an Array Variable
For x = LBound(LineArray) To UBound(LineArray)

If Len(Trim(LineArray(x))) <> 0 Then

'Split up line of text by delimiter
TempArray = Split(LineArray(x), Delimiter)

'Determine how many columns are needed
col = UBound(TempArray)

'Re-Adjust Array boundaries
ReDim Preserve DataArray(col, row)

'Load line of data into Array variable
For y = LBound(TempArray) To UBound(TempArray)
DataArray(y + counterArrSep, row) = TempArray
Next y
End If

'Next line
row = row + 1

Next x

'Clear array
Erase TempArray

'Increments the count to get another file
counter = counter + 1


'Adds space between each arrays in the Worksheet
counterArrSep = counterArrSep + 7


Loop
End Sub


The .txt files looks like this:
...\employees\John.txt


apples pears oranges carrots
4 5 34 2
43 5,5 4 43
6 54 9 7,5
41,5 55 0 2


...\employees\Steve.txt
apples pears oranges carrots cabbages
6 56 6 2 0
4 1 4 12 5
0 7 9 7 6
0 12 1 5 3
1 44 3 6 0
4 4 4,5 6 23



Expected result

John.txt								Steve.txt
apples	pears	oranges	carrots					apples	pears	oranges	carrots	cabbages
4	5	34	2					6	56	6	2	0
43	5,5	4	43					4	1	4	12	5
6	54	9	7,5					0	7	9	7	6
41,5	55	0	2					0	12	1	5	3
								1	44	3	6	0
								4	4	4,5	6	23

<tbody>
</tbody>