Solved: filesearch for .txt and text conversion

[MrAshton]

1) Ok so this one is pretty interesting. I need to run a file search for s_matrix.txt but I can't seem to figure out how to get the program to look for .txt (it's not one of the acknowledged formats?) I've got the directory search and everything set, I just need it to find the .txt file.


2) Once it locates the file, there is a matrix that appears as such:
3.5 4.0 4.2 1.4
2.4 5.2 1.0 2.3
3.7 1.6 3.2 3.8
4.2 3.5 6.3 2.1

as you can see, it is a 4 X 4 matrix but each row has numbers spaced with spaces. I need to copy each individual value to a seperate cell.. how can I do this. and I'm not so sure that each number in ever file will be a single digit with a single decimal..

Thanks in advance for all your help.

[Bob Phillips]

If Dir("C:\test\MrAshton.txt") <> "" Then

Workbooks.OpenText Filename:="C:\test\MrAshton.txt", _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Space:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
End If

[MrAshton]

ok so I think that's going to help, but it hasn't yet (only because I haven't gotten to it). I'm having a hard time opening the file that is found. (question 1) so that I can apply your response to Question 2 to it.

This is what I have right now.

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fLdr = .SelectedItems(1)
End With


RootDir = fLdr
NumFiles = 0

fLdr = RootDir
Application.StatusBar = "Searching for " & SearchString & " in " & fLdr

With Application.FileSearch
.NewSearch
.LookIn = fLdr
.SearchSubFolders = True
.FileTypes.Add msoFileTypeOfficeFiles
.FileTypes.Add msoFileTypeNoteItem
.Filename = SearchString
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = NumFiles + 1 To NumFiles + .FoundFiles.Count
Filenames(i) = .FoundFiles(i - NumFiles)
Next i
NumFiles = NumFiles + .FoundFiles.Count
If NumFiles > 0 Then
Workbooks.OpenText Filename:=.FoundFiles, _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Space:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
End If

End If
End With

[Bob Phillips]

Why are you doing a FileDialog and a Filesearch. Just do a simple File Dialog on a file, msoFileDialogOpen

[MrAshton]

cuz i'm a newb. hehe.
Ok I'll give that a shot.

Update:
ok so the thing is, I need it to run through a really large list of folders and subfolders and I need it to be automated. with the change you just proposed, I have to manually select the 's_matrix.txt' file that it finds. I need it to find that file, copy some text, and move on to the next.

[MrAshton]

ok I've simplified my code so that it looks like this

Sub filefinder()
Dim fLdr As String
Dim searchstring As String

searchstring = InputBox("Search for what?", "Search for what?", "")

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fLdr = .SelectedItems(1)
End With

'With Application.FileFind
'.SearchPath = fLdr
'.SubDir = True
'.MatchCase = False
'.Name = searchstring
'.View = msoViewFileInfo
'End With

With Application.FileSearch
.NewSearch
.LookIn = fLdr
.Filename = searchstring
.SearchSubFolders = True
If .Execute() > 0 Then
filesfound = .FoundFiles.Count
End If
End With
MsgBox "Found " & filesfound & " files with the name " & searchstring & "", vbOKOnly

End Sub


It successfully finds all occurances of the file, but now I want it to open. Is there a way I can do a 'for each foundfile' type of deal?

[mdmackillop]

For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
DoEvents
Next

[MrAshton]

it tells me .'FoundFiles.Count' in the "For" line is an invalid or unqualified reference. I changed it to:

For i = 1 to filesfound

and it accepted it but then did not accept the
Workbooks.Open .Foundfiles(i)
I messed around with variables and couldn't get it to work. Does it matter that the files being found are of the '.txt' file extension?

[mdmackillop]

Sorry I couldn't test with my version of Excel. The Workbook Open code should be similar to XLD's code in Post 2

[MrAshton]

ok I'll give that a shot
Thanks for your patience

[MrAshton]

what exactly do I do with the first If statement?
should the Dir be my fLdr?

[MrAshton]

Ok so I'll post my final result so incase others refer to this thread for reference.

This is the code that does everything I need it to.

Sub filefinder()
Dim fLdr As String
Dim searchstring As String

Sheets(1).Name = "Filenames"
Sheets(2).Name = "Summary"
StartWorkbook = ActiveWorkbook.Name


searchstring = InputBox("Search for what?", "Search for what?", "")

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fLdr = .SelectedItems(1)
End With

Application.StatusBar = "Searching for " & searchstring & " in " & fLdr

StartNumFiles = 0
nextentry = 1
entrypoint = 0

Sheets(2).Activate
Cells(1, 1).Activate

With Application.FileSearch
.LookIn = fLdr
.Filename = searchstring
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Do
If ActiveCell.Offset(entrypoint, 0) = "" Then
GoTo loopPoint
Else: ActiveCell.Offset(1, 0).End(xlToLeft).Activate
End If
Loop Until ActiveCell.Offset(0, 0) = ""
loopPoint:
ActiveCell = .FoundFiles(i)
ActiveCell.Offset(0, 1).Activate
'Open the s_matrix.txt file
Application.StatusBar = "Retrieving data from file:" & .FoundFiles(i) & ""
Workbooks.OpenText Filename:=.FoundFiles(i), _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Space:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
'copy first set
Application.StatusBar = "Retrieving First Set from" & .FoundFiles(i) & """"
Range("A6:D6").Copy
'paste first set into datasheet
Workbooks(StartWorkbook).Activate
ActiveSheet.Paste
'copy second set
Application.StatusBar = "Retrieving Second Set from" & .FoundFiles(i) & ""
Workbooks("s_matrix").Activate
Range("A7:D7").Copy
'paste second set into datasheet
Workbooks(StartWorkbook).Activate
ActiveCell.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
'copy third set
Application.StatusBar = "Retrieving Third Set from" & .FoundFiles(i) & """"
Workbooks("s_matrix").Activate
Range("A8:D8").Copy
'paste third set into datasheet
Workbooks(StartWorkbook).Activate
ActiveCell.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
'copy fourth set
Application.StatusBar = "Retrieving Fourth Set from" & .FoundFiles(i) & """"
Workbooks("s_matrix").Activate
Range("A9:D9").Copy
'paste fourth set into datasheet
Workbooks(StartWorkbook).Activate
ActiveCell.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
'Close the s_matrix file
Workbooks("s_matrix").Close False
Application.StatusBar = "Done with this file. Moving on to Next"
Next i
Application.StatusBar = "DataMine Complete"
End If
End With

End Sub

Thanks again to everyone who helped. You guys rock.:clap: