Solved: import multiple text files from listbox

[daniel_d_n_r]

Hi all
this code dumps a list of .dat file names into a list box.
it is a multiselect list box with check boxes.

what i am trying to do is when i check multiple items in the list it will import the text file/s into the current workbook,leaving me with a sheet for each imported text file

any ideas would be helpful.
cheers

Public Sub CommandButton11_Click()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Dan\My Documents\Copy of Sapflow"
.SearchSubFolders = True
.Filename = "*.dat"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
WSUD.ListBox1.AddItem Dir(.FoundFiles(i), vbDirectory)
Next i
Else
MsgBox "There were no files found."
End If
End With
end sub

[Bob Phillips]

Private Sub cmdOK_Click()
Dim i As Long
Dim First As Boolean
Dim wb As Workbook
First = True
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Workbooks.Open .List(i)
If First Then
First = False
Set wb = ActiveWorkbook
Else
ActiveWorkbook.Worksheets(1).Move After:=wb.Worksheets(wb.Worksheets.Count)
End If
End If
Next i
End With
End Sub

[daniel_d_n_r]

The problem i have is that the selected item needs to have a path as in .foundfiles(i) 'This includes the full pathh
however
Dir(FoundFiles(i),vbdirectory) 'this trims the file path for display in the listbox


The only thing i have come up with is to add a collection of the original foundfiles with full path and somhow connect them on selection.


Private mycollection1 As Collection
Public Sub CommandButton11_Click()
Set mycollection1 = New Collection
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Dan\My Documents\Copy of Sapflow"
.SearchSubFolders = True
.Filename = "*.dat"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count

mycollection1.Add .FoundFiles(i)
WSUD.ListBox1.AddItem Dir(.FoundFiles(i), vbDirectory)
Next i
Else
MsgBox "There were no files found."
End If
End With

so far i have only come up with one monstosity of tangled code after another, even going as far as this below
Option Compare Text
Private Sub CommandButton56_Click()
With Listbox1
For x = 0 To ListBox1.ListCount
If .Selected(x) Then
For Each i In mycollection1
mystr = Right(i, (Len(i) - InStrRev(i, "\")))
If mystr = .List(x) Then
Debug.Print .List(x)' this is were i would like to import the file with its path from collection1
End If
Next i
End If
Next x
End With

mystr = Right(i, (Len(i) - InStrRev(i, "\"))) ' this is actually trying to temporarily manipulate the file path for a comparison
So far as you can see it is a mess and cannot work out how to associate these values.
As you can see the main problem is that once the item is imported into the listbox it loses it path and only exists as a filename,which in turn will not open the file.

Any ideas? maybe i am going around this the wrong way? it seems i amtrying to fit a circle in to a square shaped hole.

Cheers

[Bob Phillips]

You know the path as you use that ijn the Lookin value of the FileSearch, so just save that in a variable and pre-prend it to the Listbox selection when needed.

BTW, FileSearch has been dropped in Excel 2007.

[daniel_d_n_r]

thanks mate,

The problem is that the filesearch searches subfolders which means there is at least 5 paths depending on which subfolder the file resides in.
This is good for me as I dont have to have a set path and that filesearch retreives the path for each seperate file, however using a listbox this way is decidedly difficult.

I will keep looking into this
thanks for the post its been helpful

cheers

[Bob Phillips]

Oh yeah, didn't spot that.

I would add a nother column to the listb ox then, don't display it though, with the full path



WSUD.ListBox1.List(WSUD.ListBox1.ListCount - 1, 1) = FoundFiles(i)


and retrive that value when accessing the file




.List(x, 1)

[daniel_d_n_r]

thanks
never thought of another column.
this even works if extra items are added to the list.

cheers