search, copy, open Window explorer, paste in the address bar, and open file

[Chalhoub]

Good day all,
I have an excel spreadsheet that contains over 500k links. Excel will only let you hyperlink around 65k. I would like to create a macro to (either with a button or CTRL+char) to:
1- open a dialog box
2- type in a filename (e.g. 4r23y)
3- press enter
4- the macro will search the entire worksheet and find the cell with the link in it (j:\folder\4r23y.pdf)
5- copy the link contents (j:\folder\4r23y.pdf)
6- open a window explorer
7- paste the link (j:\folder\4r23y.pdf) in the address bar
8- press enter to open the file

I can never get all the steps to work in a single macro.
Thank you all in advance if you can help.

 

[PeteWright]

This should do the job, I've tested it with a few sample files:

Sub OpenFileInExplorer()
   Dim UsedRange As Range: Set UsedRange = ActiveSheet.UsedRange
   Dim Cell As Range
   Dim FileNameToSearchFor As String
   FileNameToSearchFor = Application.InputBox( _
      Prompt:="Please enter filename", Title:="File Search", Type:=2, Default:="")
   For Each Cell In UsedRange
      If InStrRev(Cell.Value2, FileNameToSearchFor) > 0 Then
         Shell "C:\WINDOWS\explorer.exe """ & Cell.Value2 & """"
         'Shell "explorer.exe C:\myfile.txt"
         'Call Shell("explorer.exe " & Cell.Value2, vbNormalFocus)
         'Call Shell("explorer.exe /select," & Cell.Value2, vbNormalFocus)
         Exit Sub
      End If
   Next Cell
   MsgBox Prompt:="File not found!", Buttons:=vbOKOnly, Title:="Search Result"
End Sub

But there is one thing to keep in mind: A lot of data means a lot of time to search. You wrote that you got 500k+ links.
Imagine following scenario:

Your Worksheet has a link address (e.g. "j:\folder\4r23y.pdf") at the end of your data (e.g. in cell A500000) then the VBA code I wrote would start searching in cell A1 and going down until a result is found (or not). That would take some time.

Please let me know if you have any further questions

 

[Chalhoub]

This should do the job, I've tested it with a few sample files:

Sub OpenFileInExplorer()
   Dim UsedRange As Range: Set UsedRange = ActiveSheet.UsedRange
   Dim Cell As Range
   Dim FileNameToSearchFor As String
   FileNameToSearchFor = Application.InputBox( _
      Prompt:="Please enter filename", Title:="File Search", Type:=2, Default:="")
   For Each Cell In UsedRange
      If InStrRev(Cell.Value2, FileNameToSearchFor) > 0 Then
         Shell "C:\WINDOWS\explorer.exe """ & Cell.Value2 & """"
         'Shell "explorer.exe C:\myfile.txt"
         'Call Shell("explorer.exe " & Cell.Value2, vbNormalFocus)
         'Call Shell("explorer.exe /select," & Cell.Value2, vbNormalFocus)
         Exit Sub
      End If
   Next Cell
   MsgBox Prompt:="File not found!", Buttons:=vbOKOnly, Title:="Search Result"
End Sub

But there is one thing to keep in mind: A lot of data means a lot of time to search. You wrote that you got 500k+ links.
Imagine following scenario:

Your Worksheet has a link address (e.g. "j:\folder\4r23y.pdf") at the end of your data (e.g. in cell A500000) then the VBA code I wrote would start searching in cell A1 and going down until a result is found (or not). That would take some time.

Please let me know if you have any further questions

I reviewed quickly and that worked fine. THANK YOU so much. I agree with you, it is not as fast as CTRL+F. I will test later this week. I have to clean the data first, there are duplicates and sometime different versions of the file. I am wondering if there is a way to list the matching filenames first. Thank you again.

 

[PeteWright]

I am wondering if there is a way to list the matching filenames first

What exactly do you mean?

 

[RoryA]

You shouldn't really need to shell explorer at all for that - either shellexecute or the followhyperlink method should work. You could use the Find method or Match or Filter worksheet functions to locate the relevant data instead of looping.

 

[Chalhoub]

You shouldn't really need to shell explorer at all for that - either shellexecute or the followhyperlink method should work. You could use the Find method or Match or Filter worksheet functions to locate the relevant data instead of looping.

I agree. What I meant by "list of duplicates" is similar when you use CTRL+F "find all" and you get all the matching results in a window. Double thanks for following up.

 

[RoryA]

You need some means of displaying all the results though, which basically means creating a form with a listbox or similar that you can then populate with items that match the data you enter (in a textbox on the same form).