Help in modifying a filesearch macro!

[drucey]

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.

Please Login or Register  to view this content.

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 the moment it's A1, but it's going to be something i plan to roll out throughout the little company i work for so need it to look good (hence 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 the filesearch.
For example, in column A it lists all the files found, but in B i want it to get cell value C4 from the sheets listed in column A. The sheet is an electronic ordering system (trying to convince the company to reduce paper usage!) and the macro above lists all orders that have been placed. I would love to be able to list which Supplier it was sent to next to the filesearch'd list of orders.

Any help would be immensely appreciated, thank you!

[NickHK]

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

"drucey" <drucey.24t4s1_1142587807.7983@excelforum-nospam.com> wrote in
message news:<drucey.24t4s1_1142587807.7983@excelforum-nospam.com>...
>
> 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 Sub
> --------------------
>
>
> 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 the
> moment it's A1, but it's going to be something i plan to roll out
> throughout the little company i work for so need it to look good (hence
> 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 the
> filesearch.
> For example, in column A it lists all the files found, but in B i want
> it to get cell value C4 from the sheets listed in column A. The sheet
> is an electronic ordering system (trying to convince the company to
> reduce paper usage!) and the macro above lists all orders that have
> been placed. I would love to be able to list which Supplier it was sent
> to next to the filesearch'd list of orders.
>
> Any help would be immensely appreciated, thank you!
>
>
> --
> drucey
"drucey" <drucey.24t4s1_1142587807.7983@excelforum-nospam.com> wrote in
message news:drucey.24t4s1_1142587807.7983@excelforum-nospam.com...
>
> 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 Sub
> --------------------
>
>
> 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 the
> moment it's A1, but it's going to be something i plan to roll out
> throughout the little company i work for so need it to look good (hence
> 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 the
> filesearch.
> For example, in column A it lists all the files found, but in B i want
> it to get cell value C4 from the sheets listed in column A. The sheet
> is an electronic ordering system (trying to convince the company to
> reduce paper usage!) and the macro above lists all orders that have
> been placed. I would love to be able to list which Supplier it was sent
> to next to the filesearch'd list of orders.
>
> Any help would be immensely appreciated, thank you!
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523478
>

[drucey]

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?

[drucey]

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!

[NickHK]

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

"drucey" <drucey.24t73b_1142590803.0744@excelforum-nospam.com> wrote in
message news:drucey.24t73b_1142590803.0744@excelforum-nospam.com...
>
> 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?
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523478
>

[Ardus Petus]

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
'----------------------------------------------
"drucey" <drucey.24t4s1_1142587807.7983@excelforum-nospam.com> a écrit dans
le message de news:drucey.24t4s1_1142587807.7983@excelforum-nospam.com...
>
> 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 Sub
> --------------------
>
>
> 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 the
> moment it's A1, but it's going to be something i plan to roll out
> throughout the little company i work for so need it to look good (hence
> 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 the
> filesearch.
> For example, in column A it lists all the files found, but in B i want
> it to get cell value C4 from the sheets listed in column A. The sheet
> is an electronic ordering system (trying to convince the company to
> reduce paper usage!) and the macro above lists all orders that have
> been placed. I would love to be able to list which Supplier it was sent
> to next to the filesearch'd list of orders.
>
> Any help would be immensely appreciated, thank you!
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523478
>

[drucey]

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 thought that i can do 3 searches at once?

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

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

and again, search for all worksheets "Completed*.xls" and show the 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!

[drucey]

Oooh i did it! I love you guys!

[NickHK]

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


"drucey" <drucey.24t8ha_1142592604.5431@excelforum-nospam.com> wrote in
message news:drucey.24t8ha_1142592604.5431@excelforum-nospam.com...
>
> 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!
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523478
>

[drucey]

Incredible! THank you so much Nick.

Now to get on with this bad boy