VBA to search for possible files in Windows Explorer

Giganews Newsgroups
Subject: VBA to search for possible files in Windows Explorer
Posted by:  Phrank (pbe…@hotmail.com)
Date: Fri, 25 Sep 2015

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

Replies