Hello,
I have a situation that I can't figure out how to accomplish. I have a
folder that currently has about 310 excel files in it. The file names
are mostly random. In the end what I need to do is find out the contents
of a particular cell in each file. The cell I need is a constant one, it
is K38 in each file. Once I have this information, I need to put that
value into appropriate cells in the workbook I'm coding in.
Currently I'm using a file search(the code is a direct cut from the
Visual Basic help)
Private Sub CommandButton1_Click()
Set fs = Application.FileSearch
With fs
.LookIn = "P:\Test"
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

However, all this does is inundate the workbook with message boxes
telling me the file names. I've tried to get that data dumped into
cells, but have no luck. Any ideas? If you need clarification just ask.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

Create a new worksheet (it'll overwrite any existing data):

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim FS As FileSearch

Set FS = Application.FileSearch

With FS
.LookIn = "P:\Test"
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i, 1).Value = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

In sample code, lots of people use msgbox's just to show how the structure of
the code works.

Lots of times, it just means "put your real code here".


Darrin Henry wrote:

Hello,
I have a situation that I can't figure out how to accomplish. I have a
folder that currently has about 310 excel files in it. The file names
are mostly random. In the end what I need to do is find out the contents
of a particular cell in each file. The cell I need is a constant one, it
is K38 in each file. Once I have this information, I need to put that
value into appropriate cells in the workbook I'm coding in.
Currently I'm using a file search(the code is a direct cut from the
Visual Basic help)
Private Sub CommandButton1_Click()
Set fs = Application.FileSearch
With fs
.LookIn = "P:\Test"
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

However, all this does is inundate the workbook with message boxes
telling me the file names. I've tried to get that data dumped into
cells, but have no luck. Any ideas? If you need clarification just ask.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Thanks Dave, it worked perfectly. I knew I was on the right track, but
was not sure of the syntax to export it to the cell contents. Know that
this problem has been solved, I have another one. Currently the contents
of one cell is: P:\Credit Card Orders\Federal\CCBrogdon040705.xls. I
need to turn that into this: ='P:\Credit Card
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!$K$38

Which will then show me what the contents of that cell is. Any ideas how
to code this? Or do you think there is a better way of doing it. Is it
possible to have the .FoundFiles property, output the results to
variables, something like:

DIM FILE1

FILE1 = NEXT i

I'm not even sure if that would work. But I know with variables, I could
probably do this myself. Also, I recorded a macro, where I manually did
the changes required, it is he

ActiveCell.FormulaR1C1 = _
"='P:\Credit Card _
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!R38C11"

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

One way:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim FS As FileSearch
Dim myPath As String
Dim myFolderName As String
Dim myFileName As String

myPath = "C:\my documents\excel"

Set FS = Application.FileSearch

With FS
.NewSearch
.LookIn = myPath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If FolderAndName(.FoundFiles(i), myFolderName, myFileName) _
= False Then
MsgBox "something's ain't right!"
Else
ActiveSheet.Cells(i, 1).Formula _
= "'" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Function FolderAndName(WholeFileName As String, _
JustFolder As String, JustName As String) As Boolean
Dim iCtr As Long
FolderAndName = False
For iCtr = Len(WholeFileName) To 1 Step -1
If Mid(WholeFileName, iCtr, 1) = "\" Then
JustFolder = Left(WholeFileName, iCtr)
JustName = Mid(WholeFileName, iCtr + 1)
FolderAndName = True
Exit For
End If
Next iCtr
End Function

I actually didn't make it a formula. I figured for testing, you may want to
play with it a bit.

This line:
ActiveSheet.Cells(i, 1).Formula _
= "$$$$$'" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"

I used $$$$$ to make it text (for testing first):

ActiveSheet.Cells(i, 1).Formula _
= "='" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"

I'd try it, change it to an equal sign to see if it worked ok.



Darrin Henry wrote:

Thanks Dave, it worked perfectly. I knew I was on the right track, but
was not sure of the syntax to export it to the cell contents. Know that
this problem has been solved, I have another one. Currently the contents
of one cell is: P:\Credit Card Orders\Federal\CCBrogdon040705.xls. I
need to turn that into this: ='P:\Credit Card
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!$K$38

Which will then show me what the contents of that cell is. Any ideas how
to code this? Or do you think there is a better way of doing it. Is it
possible to have the .FoundFiles property, output the results to
variables, something like:

DIM FILE1

FILE1 = NEXT i

I'm not even sure if that would work. But I know with variables, I could
probably do this myself. Also, I recorded a macro, where I manually did
the changes required, it is he

ActiveCell.FormulaR1C1 = _
"='P:\Credit Card _
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!R38C11"

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

It worked perfectly. Thanks Dave.

*** Sent via Developersdex http://www.developersdex.com ***

Sorry, to dig up this kind of old thread. But I have a problem with the
coding. It seems to not want to sort them by LastModified, as I need. It
always seems to go via file name, to reiterate my code is as follows:

Private Sub FindTotals_Click()
Dim i As Long
Dim FS As FileSearch
Dim myPath As String
Dim myFolderName As String
Dim myFileName As String

myPath = "C:\Hard Data"

Set FS = Application.FileSearch

With FS
.NewSearch
.LookIn = myPath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If FolderAndName(.FoundFiles(i), myFolderName, myFileName) _
= False Then
MsgBox "something's ain't right!"
Else
ActiveSheet.Cells(i, 1).Formula _
= "='" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Function FolderAndName(WholeFileName As String, _
JustFolder As String, JustName As String) As Boolean
Dim iCtr As Long
FolderAndName = False
For iCtr = Len(WholeFileName) To 1 Step -1
If Mid(WholeFileName, iCtr, 1) = "\" Then
JustFolder = Left(WholeFileName, iCtr)
JustName = Mid(WholeFileName, iCtr + 1)
FolderAndName = True
Exit For
End If
Next iCtr
End Function


Any idea why it's not sorting via last modified? Thanks.


*** Sent via Developersdex http://www.developersdex.com ***

This technique was first posted by Dana DeLouis:

With Application.FileSearch
'// Wake Excel up for msoSortByLastModified to work !
.NewSearch
.LookIn = "C:\"
.Filename = "*.jnk"
.Execute SortBy:=msoSortBySize
End With

With Application.FileSearch
.NewSearch
'....your existing code here



Darrin Henry wrote:

Sorry, to dig up this kind of old thread. But I have a problem with the
coding. It seems to not want to sort them by LastModified, as I need. It
always seems to go via file name, to reiterate my code is as follows:

Private Sub FindTotals_Click()
Dim i As Long
Dim FS As FileSearch
Dim myPath As String
Dim myFolderName As String
Dim myFileName As String

myPath = "C:\Hard Data"

Set FS = Application.FileSearch

With FS
.NewSearch
.LookIn = myPath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If FolderAndName(.FoundFiles(i), myFolderName, myFileName) _
= False Then
MsgBox "something's ain't right!"
Else
ActiveSheet.Cells(i, 1).Formula _
= "='" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Function FolderAndName(WholeFileName As String, _
JustFolder As String, JustName As String) As Boolean
Dim iCtr As Long
FolderAndName = False
For iCtr = Len(WholeFileName) To 1 Step -1
If Mid(WholeFileName, iCtr, 1) = "\" Then
JustFolder = Left(WholeFileName, iCtr)
JustName = Mid(WholeFileName, iCtr + 1)
FolderAndName = True
Exit For
End If
Next iCtr
End Function

Any idea why it's not sorting via last modified? Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson