![]() |
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 |
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 |
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