Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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 -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52347 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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" wrote in message ... 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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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 -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52347 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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 -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52347 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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" a écrit dans le message de ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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 +------------------------------------------------------------------- -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52347 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in modifying a filesearch macro!
Incredible! THank you so much Nick. Now to get on with this bad boy -- drucey ------------------------------------------------------------------------ drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553 View this thread: http://www.excelforum.com/showthread...hreadid=523478 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modifying Macro | Excel Discussion (Misc queries) | |||
Need help modifying a macro | Excel Discussion (Misc queries) | |||
Modifying Macro | Excel Worksheet Functions | |||
Filesearch in Remotely Called Macro | Excel Programming | |||
FileSearch macro returning incorrect docs? | Excel Programming |