Hyperlink Search Macro

Hello again,

Also, the open function you posted earlier is for opening spread sheets. Is there one that opens folders or files?

Click to expand...

Yes, I all ready mentioned this, ShellExecute(). Here it is, again:

http://www.mrexcel.com/board2/viewtopic.php?p=1306858#1306858

This will launch files...

You want to open Windows Explorer to a certain directory? Here's one approach:

http://www.mrexcel.com/board2/viewtopic.php?p=756513#756513

I'm sure there are plenty of other ways, as well.

OK so I made a macro that had all the code from the one you explained. I changed .xls to .pdf and tried to add in the shellexecute stuff. This is what I have


'Name of Sub Routine
Sub foobar()
'Late Bind Variable for the File Scripting Object
Dim fso As Object
'String Variable for working with the Dir() function
Dim strName As String
'Array of Strings Variable - it's 2d, and Long Integer Variable
Dim strArr(1 To 65536, 1 To 1) As String, i As Long


'String Constant for root Search Folder
Const strDir As String = "X:\Water Stewardship Maps and Plans"
'This is our search term constant
Const searchTerm As String = "production"

'Locate first file with our search term and .xls extension
'Within our root folder
Let strName = Dir$(strDir & "\*" & searchTerm & "*.pdf")
'Start looking for the next files
Do While strName <> vbNullString
'Use a counter, to populate our 1-based, 2d Array of Strings
Let i = i + 1
'Popupulate the appropriate element within the Array
Let strArr(i, 1) = strDir & "\" & strName
'Find the next matching file
Let strName = Dir$()
'Keep going through root folder
Loop
'Late bind with FSO, we're using this to 'walk through' subfolders
Set fso = CreateObject("Scripting.FileSystemObject")
'Call our recursive procedure
'We Marshall our variables byRef as we're going to manipulate them
'This allows us to avoid using Public Variables
Call recurseSubFolders(fso.GetFolder(strDir), strArr(), i, searchTerm)
'We're done with FSO, terminate it
Set fso = Nothing
'Lets make sure we actually found at least one match
If i > 0 Then
'Pass our 2d Array of Strings to the Worksheet (GET RID OF BELOW FOR NOW)
'Range("A1").Resize(i).Value = strArr
Call ShellExecute(0, "Open", "test DB.mdb", vbNullString, _
"C:\", 1)
'Shell "Explorer.exe /e, c:\temp\", vbMaximizedFocus

'End of our conditional
End If
'End of our routine
End Sub







'Here's our recursive Sub Routine, we use this to walk Sub Directories

Private Sub recurseSubFolders(ByRef Folder As Object, _
ByRef strArr() As String, _
ByRef i As Long, _
ByRef searchTerm As String)
'Late bind to Work with FSO's SubFolder Object
Dim SubFolder As Object
'String Variable for working with the Dir() function
Dim strName As String
'Loop for iterating through each SubFolder Object in the Root Folder
For Each SubFolder In Folder.SubFolders
'Use Dir() to return a potential match
Let strName = Dir$(SubFolder.Path & "\*" & searchTerm & "*.xls")
'Start looking for the next files
Do While strName <> vbNullString
'Use a counter, to populate our 1-based, 2d Array of Strings
Let i = i + 1
'Popupulate the appropriate element within the Array
Let strArr(i, 1) = SubFolder.Path & "\" & strName
'Find the next matching file
Let strName = Dir$()
'Keep going through the Subfolder folder
Loop
'Call the recursive routine, we want to walk through Subfolders
'Of each Subfolder, and so forth, capturing the entire tree of Folders
Call recurseSubFolders(SubFolder, strArr(), i, searchTerm)
'Next SubFolder Object
Next
'End of our routine
End Sub








However, I can't add this little bit of code

Private Declare Function ShellExecute _
Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Anywhere without an error message coming up saying only comments can appear after End Sub. I'm in way over my head.

API function declarations need to be at the top of the module.

Thanks for your help, everything is working now

Just one last thing though. Let's say that the files I am working with have two parts to their file names. Lets say the first part is a letter from A to Z and the second part is a number. So a file might be called "A number 5"

Now I have it set up now so that it takes the search term as the text from the highlighted cell. Lets say that cell says A. It would be able to find file A, but only if there was only one of them. If there was A 1 through A 5 though, it would just open the last one. In a case like this, I would have 5 rows that said A and in the cell to the right of those 5 A's, I would have the numbers 1 through 5.

Can I set up the search so that it will only come up positive if it finds A AND 5? Dual search terms?

Currently I have to click the leftmost cell (the text in this cell contains the main search term) and then run the macro. Above I said that I wanted it to find a second search term in the cell to the right of the one selected.

Might it be possible that no matter what cell in a row you clicked it would always find the first search term in the leftmost cell of that row and the second search term in the next cell to the right? Thanks

Here is what I have so far





'Name of Sub Routine
Sub foobar()
'Late Bind Variable for the File Scripting Object
Dim fso As Object
'String Variable for working with the Dir() function
Dim strName As String
'Array of Strings Variable - it's 2d, and Long Integer Variable
Dim strArr(1 To 65536, 1 To 1) As String, i As Long

'String Constant for root Search Folder
Const strDir As String = "X:\Water Stewardship Maps and Plans"
'This is our search term constant
Dim searchTerm As String

Let searchTerm = ActiveCell.Text

'Locate first file with our search term and .xls extension
'Within our root folder
Let strName = Dir$(strDir & "\*" & searchTerm & "*.pdf")
'Start looking for the next files
Do While strName <> vbNullString
'Use a counter, to populate our 1-based, 2d Array of Strings
Let i = i + 1
'Popupulate the appropriate element within the Array
Let strArr(i, 1) = strDir & "\" & strName

'Find the next matching file
Let strName = Dir$()

'Keep going through root folder
Loop
'Late bind with FSO, we're using this to 'walk through' subfolders
Set fso = CreateObject("Scripting.FileSystemObject")
'Call our recursive procedure
'We Marshall our variables byRef as we're going to manipulate them
'This allows us to avoid using Public Variables
Call recurseSubFolders(fso.GetFolder(strDir), strArr(), i, searchTerm)
'We're done with FSO, terminate it
Set fso = Nothing
'Lets make sure we actually found at least one match
If i > 0 Then

'Pass our 2d Array of Strings to the Worksheet (GET RID OF BELOW FOR NOW)
'Range("A1").Resize(i).Value = strArr
Call ShellExecute(0, "Open", strArr(i, 1), "", "", 1)

'End of our conditional
End If
'End of our routine
End Sub


'Here's our recursive Sub Routine, we use this to walk Sub Directories
Private Sub recurseSubFolders(ByRef Folder As Object, _
ByRef strArr() As String, _
ByRef i As Long, _
ByRef searchTerm As String)
'Late bind to Work with FSO's SubFolder Object
Dim SubFolder As Object
'String Variable for working with the Dir() function
Dim strName As String
'Loop for iterating through each SubFolder Object in the Root Folder
For Each SubFolder In Folder.SubFolders
'Use Dir() to return a potential match
Let strName = Dir$(SubFolder.Path & "\*" & searchTerm & "*.pdf")
'Start looking for the next files
Do While strName <> vbNullString
'Use a counter, to populate our 1-based, 2d Array of Strings
Let i = i + 1
'Popupulate the appropriate element within the Array
Let strArr(i, 1) = SubFolder.Path & "\" & strName
'Find the next matching file
Let strName = Dir$()
'Keep going through the Subfolder folder
Loop
'Call the recursive routine, we want to walk through Subfolders
'Of each Subfolder, and so forth, capturing the entire tree of Folders
Call recurseSubFolders(SubFolder, strArr(), i, searchTerm)
'Next SubFolder Object
Next
'End of our routine
End Sub