Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-16-2018, 09:55 AM   #1
Lol999
Newly Registered User
 
Join Date: May 2017
Posts: 184
Thanks: 23
Thanked 1 Time in 1 Post
Lol999 is on a distinguished road
search computer from Excel sheet

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

Lol999 is offline   Reply With Quote
Old 04-16-2018, 05:29 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,930
Thanks: 0
Thanked 642 Times in 627 Posts
Ranman256 will become famous soon enough
Re: search computer from Excel sheet

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
Ranman256 is offline   Reply With Quote
Old 04-16-2018, 11:04 PM   #3
Lol999
Newly Registered User
 
Join Date: May 2017
Posts: 184
Thanks: 23
Thanked 1 Time in 1 Post
Lol999 is on a distinguished road
Re: search computer from Excel sheet

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

Lol999 is offline   Reply With Quote
Old 04-17-2018, 12:53 AM   #4
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,544
Thanks: 79
Thanked 1,374 Times in 1,282 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: search computer from Excel sheet

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?
Attached Files
File Type: zip ExcelOpenFolderExample.zip (14.4 KB, 11 views)
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est

Last edited by ridders; 04-17-2018 at 01:08 AM.
ridders is offline   Reply With Quote
Old 04-17-2018, 11:22 AM   #5
Lol999
Newly Registered User
 
Join Date: May 2017
Posts: 184
Thanks: 23
Thanked 1 Time in 1 Post
Lol999 is on a distinguished road
Re: search computer from Excel sheet

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
Lol999 is offline   Reply With Quote
Old 04-17-2018, 11:41 AM   #6
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,544
Thanks: 79
Thanked 1,374 Times in 1,282 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: search computer from Excel sheet

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

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Lol999 (04-17-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create query to search for computer by name dechra Queries 1 06-17-2013 02:55 AM
Copy 2 rows from various excel sheet and paste it onto one sheet irfanparbatani Excel 1 11-29-2011 03:53 PM
Excel automation for detecting new sheet and extracting data to another sheet germaine Excel 5 05-10-2010 10:41 AM
Search form on access cant work on different computer suckyboy General 3 10-25-2008 08:12 AM
Find search string and copy row to another sheet mailtec Excel 10 08-18-2006 05:22 AM




All times are GMT -8. The time now is 02:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World