D
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.
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
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