Help in modifying a filesearch macro!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

drucey - 17 Mar 2006 10:27 GMT

Hi all, seems like a nice place this!

I'm trying to get a macro that does the following:

Searches in a set folder for excel sheets
Lists them in a sheet
Makes the listed results hyperlinks to the listed sheet

WHich i have managed, and it works a treat.

Code
-------------------
Dim lCount As Long
Sheets("Existing Orders").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Range("B2").Select

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "J:\Purchase Orders\FM"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "Order FM*.xls"
Range("B2").Select
If .Execute > 0 Then 'Workbooks in folder
Range("B2").Select
For lCount = 1 To .FoundFiles.Count 'Loop through all.
Range("B2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Cells(lCount, 1), Address:= _
.FoundFiles(lCount), TextToDisplay:= _
Replace(.FoundFiles(lCount), "J:\Purchase Orders\FM\", "")
Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select

End Su
-------------------

I have that running on workbook_open, and it's all fine.

But i really need to:

Specifiy which column and row the results start to show in - at th
moment it's A1, but it's going to be something i plan to roll ou
throughout the little company i work for so need it to look good (henc
not starting in the first column/row, so i can make it all pretty)

In the next column, call up a cell value from the sheet listed from th
filesearch.
For example, in column A it lists all the files found, but in B i wan
it to get cell value C4 from the sheets listed in column A. The shee
is an electronic ordering system (trying to convince the company t
reduce paper usage!) and the macro above lists all orders that hav
been placed. I would love to be able to list which Supplier it was sen
to next to the filesearch'd list of orders.

Any help would be immensely appreciated, thank you

Reply to this Message

NickHK - 17 Mar 2006 11:01 GMT

drucey,
The reason you always start in A1 is that your initial Anchor value
evaluates to Cells(1,1). So you need to use something like
Cells(MyStartRow+lCount,MyStartColumn)
You can take out the Range("B2").Select's, as they confuse the issue and
achieve nothing.
Also, if you plan to use this multiple places, it would be better to change
the routine to a function, and pass in the required info as parameters.
e.g. Function MyFileSearch (argStartDir as String, _
argOutputToSheet As Worksheet, _
argStartRange as Range, _
Optional argPattern as
String="*.xls", _
Optional argIncludeFullPath as
Boolean=true)
As long

Adjust code because it will not (neccesarily) be running on the Active
sheet.
e.g. argOutputToSheet.Hyperlinks.Add Anchor:=argStartRange.Offset(lCount,0)

Then say return the number of files found from the function, or an error
value

So you can then call it with one line from anywhere.
Dim RetVal As Long
RetVal=MyFileSearch("J:\Purchase Orders\FM", Range("D17"),"Order FM*.xls")
If retVal > 0 Then
'OK, Found some file
Else

NickHK

> Hi all, seems like a nice place this!
>
[quoted text clipped - 135 lines]
>
> Any help would be immensely appreciated, thank you!

Reply to this Message

drucey - 17 Mar 2006 11:15 GMT

you beautiful man you!

unfortunatly i have only been playing with excel and vba a few months
so some of that is wayy above me, but i get the first bit.

beautiful man you.

Don't suppose you have a PSP

Reply to this Message

drucey - 17 Mar 2006 11:49 GMT

Oh ok, don't know as much as i thought.

Don't suppose you could help me set up the function bit Nick please?

Never thought you could do so much with Excel, loving it at the moment

Reply to this Message

NickHK - 23 Mar 2006 05:16 GMT

drucey,
Been busy, time now; so see if this helps.
Note the both routine require "OptionBase 1". Also "Option Explicit" is
always a good idea.
I used the native VB Dir(), instead of Excel's .FileSearch as many people
say the latter can produce flakey results.
For completeness, if you are dealing with 100's or 1000's of returned files,
you may want to look into optimising the:
ReDim Preserve FileNames(FileCount)
so you make space for 50 or 100 files at a time instead only 1.

'------------------------------
Option Explicit
Option Base 1

Private Sub cmdGetList_Click()
Dim i As Long
Dim FileList() As String
Const START_DIR As String = "C:\Documents and Settings\Nick\Desktop\"

For i = 1 To MyFileSearch(START_DIR, FileList(), , False)
With ActiveSheet.Range("A1")
.Hyperlinks.Add Anchor:=.Offset(i, 0), _
Address:=START_DIR & FileList(i), _
TextToDisplay:=FileList(i)
End With
Next

End Sub
'------------------------------
'If this routine is in your Personal.xls, you can call it anytime you need a
file listing.
'What you do with retuned list is then up the calling routine
Function MyFileSearch(ByVal argStartDir As String, _
ByRef FileNames() As String, _
Optional argPattern As String = "*.xls", _
Optional argIncludeFullPath As Boolean = True) _
As Long
Dim FileCount As Long
Dim FileName As String

If Left(argStartDir, 1) <> "\" Then argStartDir = argStartDir & "\"

FileName = Dir(argStartDir & argPattern)

Do While FileName <> ""
FileCount = FileCount + 1
'Make room for the new filename
ReDim Preserve FileNames(FileCount)

If argIncludeFullPath = True Then
FileNames(FileCount) = argStartDir & FileName
Else
FileNames(FileCount) = FileName
End If

FileName = Dir()
Loop

MyFileSearch = FileCount

End Function
'------------------------------

NickHK

> Oh ok, don't know as much as i thought.
>
> Don't suppose you could help me set up the function bit Nick please?
>
> Never thought you could do so much with Excel, loving it at the moment!

Reply to this Message

drucey - 24 Mar 2006 13:14 GMT

Incredible! THank you so much Nick.

Now to get on with this bad boy

Signature

drucey

Reply to this Message

NickHK - 17 Mar 2006 11:53 GMT

drucey,
PSP: Play Station ? No.
Media Monkey provides my entertainment.

Regarding the function, it is well worth starting out thinking to separate
out the re-usable parts of code, instead of just bunging everything in one
long routine that is only good for that specific occasion.

NickHK

Nick

> you beautiful man you!
>
[quoted text clipped - 4 lines]
>
> Don't suppose you have a PSP?

Reply to this Message

Ardus Petus - 17 Mar 2006 12:07 GMT

Trt following code.

HTH
--
AP

'-------------------------------------------------
Sub BuildSummary()
'Adjust following string constants to your needs
Const sumWS = "Existing Orders"
Const startRange = "A4"
Const extractrange = "C4"
Dim lCount As Long
Dim destRng As Range
Dim WB As Workbook
Worksheets(sumWS).Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "J:\Purchase Orders\FM"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "Order FM*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
Set destRng = Range(startRange).Offset(lCount - 1, 0)
Set WB = Workbooks.Open( _
Filename:=.FoundFiles(lCount), _
updatelinks:=False, _
ReadOnly:=True)
ActiveSheet.Hyperlinks.Add _
Anchor:=destRng, _
Address:=.FoundFiles(lCount), _
TextToDisplay:=WB.Name
destRng.Offset(0, 1).Value = _
WB.Worksheets(1).Range(extractrange)
WB.Close False
Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub
'----------------------------------------------

> Hi all, seems like a nice place this!
>
[quoted text clipped - 65 lines]
>
> Any help would be immensely appreciated, thank you!

Reply to this Message

drucey - 17 Mar 2006 12:42 GMT

I want to be as clever as you lot when i grow up!

That code works great, thank you so much Nick and AP!

AP - i've put that in, and it works fantastic - is there a way though
that i can do 3 searches at once?

ie. Just like the above code, search for all worksheets "Order*.xls" i
Column B and their C4 value in column C

but possible to search for all worksheets "Draft*.xls" and show th
results in colum F and their C4 value in column G

and again, search for all worksheets "Completed*.xls" and show th
results in column K and their C4 values in column L

To show you what i'm trying to do, i'll attach what i've done so far

+-------------------------------------------------------------------

|Filename: FM Purchase Order.zip
|Download: http://www.excelforum.com/attachment.php?postid=4468

+-------------------------------------------------------------------

Reply to this Message

drucey - 17 Mar 2006 12:50 GMT

Oooh i did it! I love you guys

Reply to this Message