ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBA to search for possible files in Windows Explorer (https://www.excelbanter.com/excel-programming/451109-vba-search-possible-files-windows-explorer.html)

Phrank

VBA to search for possible files in Windows Explorer
 
Hi. I've got the VBA code below that does indeed work fine for
opening Windows Explorer and go to the DHR folder; however, it does
not do the final search for the LotSNnum.

What I'm trying to do is have the Windows Explorer window present me
with the possible matching files that exist somewhere in the DHR
folder, which is why the LotSNnum variable is just extracting a set
number of characters. This will become more clear below.

Under the DHR folder, there are several 'year' subfolders, and in each
of those year folders there are many product ID folders, and in those
folders are many PDFs, each titled with a Lot number or a Serial
Number RANGE (e.g. 1336501413-1336501453.pdf). Each of these files
shows the manufacture history for that given range of Lot/Serial
numbers for the device.

So, instead of manually opening windows explorer and going to the DHR
folder, expanding, selecting the year, expanding, selecting the
device, expanding, selecting the appropriate file, I just want the
user to be able to click the buttom, have the macro extract the first
8 digits (including a leading zero, which I've also been struggling
with, even with the NumberFormat line of code) and feed those into the
Windows Explorer search.

For example, if I wanted to find the DHR file for serial number
1336501423, then 13365014 would be fed into the search via the code,
and I would see in my windows explorer the options below. As you can
see, I'm not able to select the exact file because of the range.
....but IF I could do that, it would be even better.

1336501413-1336501453
1336501463-1336501503

My current code is below. Thanks for any help or advice.

Dim LotSNnum As Long
Dim wePath As String

wePath = "\\server.company.path\DHR\"

Range("E13").NumberFormat = "@"
LotSNnum = Mid(Range("E13").Value, 1, 8)

Shell "C:\WINDOWS\explorer.exe """ & wePath & LotSNnum, vbNormalFocus

Claus Busch

VBA to search for possible files in Windows Explorer
 
Hi Frank,

Am Fri, 25 Sep 2015 10:50:31 -0400 schrieb Phrank:

My current code is below. Thanks for any help or advice.

Dim LotSNnum As Long
Dim wePath As String

wePath = "\\server.company.path\DHR\"

Range("E13").NumberFormat = "@"
LotSNnum = Mid(Range("E13").Value, 1, 8)

Shell "C:\WINDOWS\explorer.exe """ & wePath & LotSNnum, vbNormalFocus


try it with xlDialogOpen:
(Modify wePath to your Path)

Sub Test()
Dim wePath As String
Dim LotsNnum As String

wePath = "H:\Test\"
LotsNnum = Mid(Range("E13").Value, 1, 8)

Application.Dialogs(xlDialogOpen).Show (wePath & LotsNnum & "*.pdf")
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Phrank

VBA to search for possible files in Windows Explorer
 
Hi Claus,

That does open the Open dialog box and load the first 8 characters of
the Lot or Serial #, but it tries to open as Excel files, and it
doesn't try to open the .PDF files. I was hoping to have the Windows
Explorer window open to the appropriate subfolder (defined in the
code), and then display the Search window to the next sub-folder with
all the PDFs, and then have the wild card results displayed. The same
as opening the window, drilling down to the appropriate subfolder, but
then seeing the wild card results. Does that make sense?

Thanks Claus.

Frank


On Fri, 25 Sep 2015 20:01:26 +0200, Claus Busch
wrote:

Hi Frank,

Am Fri, 25 Sep 2015 10:50:31 -0400 schrieb Phrank:

My current code is below. Thanks for any help or advice.

Dim LotSNnum As Long
Dim wePath As String

wePath = "\\server.company.path\DHR\"

Range("E13").NumberFormat = "@"
LotSNnum = Mid(Range("E13").Value, 1, 8)

Shell "C:\WINDOWS\explorer.exe """ & wePath & LotSNnum, vbNormalFocus


try it with xlDialogOpen:
(Modify wePath to your Path)

Sub Test()
Dim wePath As String
Dim LotsNnum As String

wePath = "H:\Test\"
LotsNnum = Mid(Range("E13").Value, 1, 8)

Application.Dialogs(xlDialogOpen).Show (wePath & LotsNnum & "*.pdf")
End Sub


Regards
Claus B.



All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com