PDA

View Full Version : Add/select multiple files



Davorito
12-09-2015, 09:44 AM
Hey guys,

The code below basically gets a specific column from a .txt file and transposes it in the Excel file.

Problem: it does it only file by file while I want to input multiple files AND I want each set of data below each other.



Public Sub Example1()


'dialog result
Dim intDialogResult As Integer


'path selected by user
Dim strPath As String


'single line of data from the text file
Dim strLine As String


'string seperated by the delmiter
Dim arrString() As String


'curren row in excel sheet
Dim i As Integer


Dim FileName As String


ActiveCell.End(xlToLeft).Select


'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False


'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear


'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show


'if the user selected a file
If intDialogResult <> 0 Then

'path selected by the user
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)


'close the file index #1 if its already opened
Close #1

'open the file for reading
Open strPath For Input As #1

FileName = StrReverse(Left(StrReverse(strPath), InStr(1, StrReverse(strPath), Application.PathSeparator) - 1))




i = 1
While EOF(1) = False
Line Input #1, strLine
arrString = Split(strLine, " ")
ActiveCell(1, i) = arrString(6)
i = i + 1
Wend
End If
Close #1
ActiveCell.offset(0, 0) = FileName
ActiveCell.offset(1, 0).Activate


End Sub