search computer from Excel sheet (1 Viewer)

Lol999

Registered User.
Local time
Today, 14:11
Joined
May 28, 2017
Messages
184
Hi all, not had a lot to do with Excel since the early 90's so please bear with me.

I have a spreadsheet with assorted data, one column of which could be used to hold file paths. Is there a way to open the file explorer window with the correct folders in it by selecting a particular cell in Excel and clicking a button?

Strange one I know but it's what the client wants unless I can think of a more elegant solution.

many thanks, Lol
 

Ranman256

Well-known member
Local time
Today, 17:11
Joined
Apr 9, 2015
Messages
4,339
paste this code into a module. (Alt-F11, insert , module)

Then it will open ANY file via its extension....
.pdf files will open in acrobat,
.doc files in word
etc

USAGE:
OpenNativeApp "c:\folder\file.xls"
'opens in excel
or
OpenNativeApp activecell.value
'opens item in field in native app: word, or even file explorer if its just a path.

Code:
Option Compare Database
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&

Public Sub OpenNativeApp(ByVal psDocName As String)
Dim r As Long, msg As String

r = StartDoc(psDocName)
If r <= 32 Then
    'There was an error
    Select Case r
        Case SE_ERR_FNF
            msg = "File not found"
        Case SE_ERR_PNF
            msg = "Path not found"
        Case SE_ERR_ACCESSDENIED
            msg = "Access denied"
        Case SE_ERR_OOM
            msg = "Out of memory"
        Case SE_ERR_DLLNOTFOUND
            msg = "DLL not found"
        Case SE_ERR_SHARE
            msg = "A sharing violation occurred"
        Case SE_ERR_ASSOCINCOMPLETE
            msg = "Incomplete or invalid file association"
        Case SE_ERR_DDETIMEOUT
            msg = "DDE Time out"
        Case SE_ERR_DDEFAIL
            msg = "DDE transaction failed"
        Case SE_ERR_DDEBUSY
            msg = "DDE busy"
        Case SE_ERR_NOASSOC
            msg = "No association for file extension"
        Case ERROR_BAD_FORMAT
            msg = "Invalid EXE file or error in EXE image"
        Case Else
            msg = "Unknown error"
    End Select
'    MsgBox msg
End If
End Sub

Private Function StartDoc(psDocName As String) As Long
Dim Scr_hDC As Long

Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
End Function
 

Lol999

Registered User.
Local time
Today, 14:11
Joined
May 28, 2017
Messages
184
Thanks for the code, I understand so little of it it's not funny.

To clarify though, what I am looking to do is open the file explorer window to show the root folder for a particular file, not to open any documents specifically.

Many thanks, Lol
 

isladogs

MVP / VIP
Local time
Today, 21:11
Joined
Jan 14, 2017
Messages
18,186
This will open Explorer to a specified folder:
Code:
Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus

I rarely use code in Excel these days.
However, the attached example uses this code on buttons to open folders
Nothing fancy but it works

Perhaps you can adapt it to do the same directly clicking on a cell?
 

Attachments

  • ExcelOpenFolderExample.zip
    14.4 KB · Views: 100
Last edited:

Lol999

Registered User.
Local time
Today, 14:11
Joined
May 28, 2017
Messages
184
Evening Colin, many thanks for your help. I'm a bit busy for a day or two enjoying myself but I'll have a look at it this week.

Is there any annotation on the code? I would simply like to learn which bit does what.

Thanks once again, Lol
 

isladogs

MVP / VIP
Local time
Today, 21:11
Joined
Jan 14, 2017
Messages
18,186
There are 2 buttons.
Each has the shell code line where I've specified a particular folder.
That's it.
You'll want to adapt it yourself
 

Users who are viewing this thread

Top Bottom