Finding, Comparing folder names then opening explorer

Hello

i tried posting this earlier, but something seems to be wrong with either my 
browser or the site, so...

i have a project where i have a field that stores the serial number of a 
product. i need to take the field, trim it to 5 characters and then use it to 
search a folder and all the subfolders, trimming thier names to the first 5 
characters, and then capture the full name of the folder, and use that to 
open windows explorer at the found folder.

so if i have a field with the value "08003" and i want to find that 
sub-sub-subfolder in the folder "c:\WO\" and then open explorer when it finds 
the subfolder "C:\WO\Compressor\1 Completed\08003 Airport Petrocan\", what 
functions and code do i need?
0
Utf
1/23/2008 12:26:29 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
718 Views

Similar Articles

[PageSpeed] 30

Hi Dawn

A bit longwinded but here is the code taht finds all the files on your c drive

1.  First set up a table called "tbl_FilesInFolders"

Filelds
FileInFolderID = AutoNumber
FolderName   = Text 255
FileName       = Text 255

Copy and Run the following code

Option Compare Database
Option Explicit

Public Function fFileinFolder() As Long
    On Error Resume Next

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim intStart As Integer
    Dim lngFiles As Long
    Dim lngFilesCount As Long
    Dim strFile As String
    Dim strDir As String
    Dim strDirFile As String
    Dim strSQL As String
    Dim fs As Object
    Dim rstDetail As Object
    Dim lngTotalErrors As Long
    Dim strFolder As String
    Dim strFoundInFolder As String
    
On Error GoTo FileinFolder_Error
    
    lngTotalErrors = 0
    strFolder = "C:\"
    
    '-- Delete Contents of Existing Table
    DoCmd.RunSQL ("DELETE * FROM tbl_FilesInFolders;")

    
    
    
    '-- Trap directory not found
    If Dir(strFolder, vbDirectory) = "" Then
        MsgBox "Directory Not Found:" & vbCrLf & strFolder, vbCritical, 
"Audit File List"
        GoTo FileinFolder_Exit
    End If
    
    
    '-- Set up files used
    Set dbs = CurrentDb
    Set fs = Application.FileSearch
    
        Set rstDetail = dbs.OpenRecordset("tbl_FilesInFolders", dbOpenDynaset)
        
    
    '-- Look into Folders
    
    With fs
        .lookin = strFolder
        .SearchSubFolders = True
        .FileName = "*.*"           'search for all file
                                    'if just say excel then .FileName = 
"*.XLS"
    
        If .Execute() > 0 Then
    
       
        
        DoCmd.Hourglass True
    
                lngFilesCount = .foundfiles.Count
            
        '-- Get Data in a loop
            For lngFiles = 1 To lngFilesCount
                On Error Resume Next
                
                '-- Full path & file name
                strDirFile = .foundfiles(lngFiles)
                
                '-- Get file & dir names
                intStart = LastInStr(strDirFile, "\")
                strFoundInFolder = Left(strDirFile, intStart)
                
                strFile = Mid$(strDirFile, intStart + 1)
          '-- Write data to file
            rstDetail.AddNew
                           
                rstDetail!FolderName = strFoundInFolder
                rstDetail!FileName = strFile
                           
            rstDetail.Update
                
            
            
            Next lngFiles
        Else
            MsgBox "No files were found in the directory.", vbInformation, 
"File List"
        End If
    End With
     


FileinFolder_Exit:
    On Error Resume Next
    rst.Close
    rstDetail.colse
    Set dbs = Nothing
    Exit Function
    
FileinFolder_Error:
    MsgBox Err.Description, vbCritical, "File List Error"
    Resume FileinFolder_Exit

End Function
Function LastInStr(strSearched As String, strSought As String) As Integer
    Dim intCurrVal As Integer, intLastPosition As Integer
    
    intCurrVal = InStr(strSearched, strSought)
    
    Do Until intCurrVal = 0
        intLastPosition = intCurrVal
        intCurrVal = InStr(intLastPosition + 1, strSearched, strSought)
    Loop
    
    LastInStr = intLastPosition

End Function



"DawnTreader" wrote:

> Hello
> 
> i tried posting this earlier, but something seems to be wrong with either my 
> browser or the site, so...
> 
> i have a project where i have a field that stores the serial number of a 
> product. i need to take the field, trim it to 5 characters and then use it to 
> search a folder and all the subfolders, trimming thier names to the first 5 
> characters, and then capture the full name of the folder, and use that to 
> open windows explorer at the found folder.
> 
> so if i have a field with the value "08003" and i want to find that 
> sub-sub-subfolder in the folder "c:\WO\" and then open explorer when it finds 
> the subfolder "C:\WO\Compressor\1 Completed\08003 Airport Petrocan\", what 
> functions and code do i need?
0
Utf
1/23/2008 3:40:02 AM
sorry for the double post.

"Trever B" wrote:

> Hi Dawn
> 
> A bit longwinded but here is the code taht finds all the files on your c drive
> 
> 1.  First set up a table called "tbl_FilesInFolders"
> 
> Filelds
> FileInFolderID = AutoNumber
> FolderName   = Text 255
> FileName       = Text 255
> 
> Copy and Run the following code
> 
> Option Compare Database
> Option Explicit
> 
> Public Function fFileinFolder() As Long
>     On Error Resume Next
> 
>     Dim dbs As DAO.Database
>     Dim rst As DAO.Recordset
>     Dim intStart As Integer
>     Dim lngFiles As Long
>     Dim lngFilesCount As Long
>     Dim strFile As String
>     Dim strDir As String
>     Dim strDirFile As String
>     Dim strSQL As String
>     Dim fs As Object
>     Dim rstDetail As Object
>     Dim lngTotalErrors As Long
>     Dim strFolder As String
>     Dim strFoundInFolder As String
>     
> On Error GoTo FileinFolder_Error
>     
>     lngTotalErrors = 0
>     strFolder = "C:\"
>     
>     '-- Delete Contents of Existing Table
>     DoCmd.RunSQL ("DELETE * FROM tbl_FilesInFolders;")
> 
>     
>     
>     
>     '-- Trap directory not found
>     If Dir(strFolder, vbDirectory) = "" Then
>         MsgBox "Directory Not Found:" & vbCrLf & strFolder, vbCritical, 
> "Audit File List"
>         GoTo FileinFolder_Exit
>     End If
>     
>     
>     '-- Set up files used
>     Set dbs = CurrentDb
>     Set fs = Application.FileSearch
>     
>         Set rstDetail = dbs.OpenRecordset("tbl_FilesInFolders", dbOpenDynaset)
>         
>     
>     '-- Look into Folders
>     
>     With fs
>         .lookin = strFolder
>         .SearchSubFolders = True
>         .FileName = "*.*"           'search for all file
>                                     'if just say excel then .FileName = 
> "*.XLS"
>     
>         If .Execute() > 0 Then
>     
>        
>         
>         DoCmd.Hourglass True
>     
>                 lngFilesCount = .foundfiles.Count
>             
>         '-- Get Data in a loop
>             For lngFiles = 1 To lngFilesCount
>                 On Error Resume Next
>                 
>                 '-- Full path & file name
>                 strDirFile = .foundfiles(lngFiles)
>                 
>                 '-- Get file & dir names
>                 intStart = LastInStr(strDirFile, "\")
>                 strFoundInFolder = Left(strDirFile, intStart)
>                 
>                 strFile = Mid$(strDirFile, intStart + 1)
>           '-- Write data to file
>             rstDetail.AddNew
>                            
>                 rstDetail!FolderName = strFoundInFolder
>                 rstDetail!FileName = strFile
>                            
>             rstDetail.Update
>                 
>             
>             
>             Next lngFiles
>         Else
>             MsgBox "No files were found in the directory.", vbInformation, 
> "File List"
>         End If
>     End With
>      
> 
> 
> FileinFolder_Exit:
>     On Error Resume Next
>     rst.Close
>     rstDetail.colse
>     Set dbs = Nothing
>     Exit Function
>     
> FileinFolder_Error:
>     MsgBox Err.Description, vbCritical, "File List Error"
>     Resume FileinFolder_Exit
> 
> End Function
> Function LastInStr(strSearched As String, strSought As String) As Integer
>     Dim intCurrVal As Integer, intLastPosition As Integer
>     
>     intCurrVal = InStr(strSearched, strSought)
>     
>     Do Until intCurrVal = 0
>         intLastPosition = intCurrVal
>         intCurrVal = InStr(intLastPosition + 1, strSearched, strSought)
>     Loop
>     
>     LastInStr = intLastPosition
> 
> End Function
> 
> 
> 
> "DawnTreader" wrote:
> 
> > Hello
> > 
> > i tried posting this earlier, but something seems to be wrong with either my 
> > browser or the site, so...
> > 
> > i have a project where i have a field that stores the serial number of a 
> > product. i need to take the field, trim it to 5 characters and then use it to 
> > search a folder and all the subfolders, trimming thier names to the first 5 
> > characters, and then capture the full name of the folder, and use that to 
> > open windows explorer at the found folder.
> > 
> > so if i have a field with the value "08003" and i want to find that 
> > sub-sub-subfolder in the folder "c:\WO\" and then open explorer when it finds 
> > the subfolder "C:\WO\Compressor\1 Completed\08003 Airport Petrocan\", what 
> > functions and code do i need?
0
Utf
1/23/2008 8:34:00 AM
Reply:

Similar Artilces:

comparing excel spreadsheets from command line
Hi, I am running tests here that involve comparing two excel spread sheets. right now we have to manually open the files, copy and paste the contents of each into a separate too, and run a compare. This is time consuming as it involves an awful lot of manual intervention. Ideally, I would like to run some sort of command that takes the two files as params and which outputs (preferable in some sort of graphical format) the differences between the two files. Is that possible? Or am I asking too much? thanks much and have a nice day Graham hi, see this site. http://www.cpearson.com/ex...

Opening Money 2000 file in Money 2007
I have Money 2007 Home & Business, version 16.0.150.1303. It seems to work fine except when I try and use it to open a money 2000 file that used UK pounds as the base currency. First I get a message saying it is converting the file, then it says I must specify an exchange rate for $/� which is not available online but when the currency screen comes up it says a file needed to run planner is missing or corrupted and the program closes. If I try and start the new converted file it goes straight to the currency screen with the same missing/corrupted planner file message and I can'...

Re: Compare chart
Hi, I need to compare two products that are described by a set of standard attributes each. If all the attributes were same range of values that would be extremel easy task - you take a radial or line chart and consider it done. The trouble is each attribute has completely different range of values One of them is 0-1, often a few percents, while other ranges 0-10 000 As a result if I put it in the same serie and use the same scale i will not work properly. Moreover there is no correlation between th attributes, so one cannot forecast the scale limits. Any ideas how to show this? Thanks, ...

Opening a selected file in Word from MFC app
Hi all, I've a simple app in which the user can select a file from a list. Then I want to open that file, using Word (as it's a Word document). Nothing too scary, but it's got me stumped. I've tried various samples & examples (automation et al) but have got nowhere. It seems to me that if I can simulate the behaviour of the "Run" box (from the Start-> prompt) then I should be able to just throw the file name at it (similar to double-clicking in Explorer) and all will be well. However, nothing works so far. Has anyone out there any advice for me (aside from...

open folder close when reopened
If Ihave several folders open like inbox/recived, inbox/from, inbox/subject, sent/to, drafts, etc. and I go from another folder like MS Word to the bottom Outlook bar to see the folders I have opened and click on one that may already be open but not visible, it closes which is very agrivating, cannot find to change this setting. Version of Outlook? The folder state should be remembered when you switch between navigation panes. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ ...

Outlook wont open #2
When trying to open Outlook 2000 get error message "Exchange server is unavailable." Everything is configured correctly because it used to work fine and all other users in company are able to access Exchange server fine through their Outlook. Also unable to ping Exchange server from PC, but can ping every other server on our network. Any ideas? Thanks in advance Delete your profile and install a new one. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal ...

Tape Symbolic Name
I have three tape drives w/ symbolic names of tape0, tape1, and tape2. While running in a linux environment, there is no problem, but when running in WinXP, windows switches the symbolic names around, which ruins a tape read program I use. Any ideas on how to fix this? Thanks Jeremiah Thoman by the way, this is written in VC++ 2003 .Net ...

Single Instance of Outlook 2002 Open
Is there a way to restrict how many instances of Outlook can be running simultaneously? I would like to allow only one instance of the program to be open. I didn't think you could open up more than one instance of OL2002. It sure won't let me open up more than one at a time. If I try to launch it again, it merely opens up the existing Outlook window. Larry Williams wrote: > Is there a way to restrict how many instances of Outlook > can be running simultaneously? I would like to allow only > one instance of the program to be open. ...

windows explorer has stopped working....
Hi all, I just installed Service pack 2 for windows vista home premiun, now every time I want to delete something I got this error message "windows explorer has stopped working" and after that "windows explorer is restarting". Basically I can't delete anything. Any ideas to solve this problem?? TIA In Control Panel choose Problem Reports and Solutions (type problem in Start's searchbox), go to Problem History, right click your error and choose Check For Solution. You can also examine the details. In Administrative Tools choose Reliability an...

Two sets of Personal Folders #3
I have two sets of Outlook Today - [Personal Folders] on my folders list. They are duplicates. How do I get rid of one of them? Thanks. You can try this option: http://www.outlook-tips.net/howto/ghosts.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, SAC asked: | I have two sets of Outlook Today - [Personal Folders] on my folders | list. They are duplicates. How do I get rid of one of them? | | Thanks. ...

How Can I Synchronize Outlook 2000 and Outlook 2003 Folders
System #1 has Outlook 2003 and is permanently connected to the network.System #2 has Outlook 2000 and can be connected to the network but is mobile (it's a laptop). I want to use either/or as aI please for e-mail, calendar, etc. I'm supposing that means making sure each has an identical set of folders/an identical PSTs. Because either could have an open PST, I don't think copying/overwritng a PST on one or the other is a good idea. No MS Exchange Server. And no Net Folders inOutlook 2003 :-(. I'm not really familiar with the concept of Offline Folders, but would that be a way...

opening email attachment
help!! i was sent a email attachment in another version of publisher I have done every thing to get it open....still cant get it open I have publisher 2000 on xp.. please help The file is probably 2002 or 2003. You should contact the person who sent you the file and ask them to save it as a Pub 2000 file. If you don't have to edit this file, tell them to convert it to a .pdf file and send you that. -- JoAnn Paules MVP Microsoft [Publisher] "darlin me" <darlin me@discussions.microsoft.com> wrote in message news:6342D674-0DCE-4420-AC1B-521D0CA8917C@microsoft.com......

Finding
Two curves are crossing in my scattered chart. Along the X-axis I now can see at which X-value this crossing takes place. It is easy to calculate what value this is, but can Excel report this value with some kind of function? Hello Hans, You need to find the equations that fit the two curves and then so some simple algebra. Tell us more about the curses: can they be fit to a polynomial? Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Hans Petter H�ie" <Hans_Petter@discussions.microsoft.com> wrote in message news:259501c47df4$0f94...

how do I link tab name to content of a cellt?
I would like to be able to set up a template where the name of the tab is the contents of a specific cell (e.g.) if I enter 2007 in A1 I want the tab name to become 2007 but if I change A1 to be 2010 the tab name should change too. You are better off doing it the other way around, which won't require a macro. Is that possible? See: http://www.officearticles.com/excel/return_the_worksheet_name_to_a_cell_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "MadMurr" <MadMurr@discussions.microsoft.com> wrote in message news:7CFA2D86-AE04-4374-BF98-4058420...

Exchange Open Relay, sort of...
Hello: I've been working on this open relay issue on my exchange server for a bit and now I come to understand that I do not have an open relay. However there is still a problem. Let's say my domain is foo.com Right now anyone can use my server to send email to my domain users as long as they say it comes from a foo.com address. I tested this by conntecting via telnet into mail.foo.com and useing it to send email from any address with @foo.com to anyone in the domain. For example telnet: open mail.foo.com HELO MAIL FROM:<spammer@foo.com> RCPT to:fred@foo.com dat...

shared workbook file wont open for one users
we have a shared workbook......and when one particular user tries to open it .......it starts to open and then closes. Now when i open the file under other accts its fine....and i lock to see who has it open under tool, share workbook, it shows that that user with the problem has it open. we log in to a terminal server and the file is on the server. but its just one user with this issue ... Please Help Sometimes the file that holds the toolbar customizations gets hosed. You could use windows start button|Find to look for *.xlb. Rename all you find to *.xlbOLD Then open exc...

How to activate an open worksheet?
Hi, I have a quick question: How to bring a non-active excel sheet o top? (i.e. make it the active one)? Now let's say I open 2 excel files (A.xls and B.xls). Now, whenever received an input, I have a formula which will be triggered by an OnKe "~". That formula/module will check whether that input belong to A.xl or B.xls. So if I enter an input into A.xls and it does belong t A.xls, nothing happen. However, if I enter an input to A.xls, an actually that input belong to B.xls, I have to delete that input fro A.xls, and fill it to B.xls. I got everything working, but I can't ...

Type mismatch error when opening word doc using automation
I get a Type Mismatch error when trying to open word file by using automation. I am using msword.h header from Office 2007 and compiling with VC++ 6.0. CoInitialize(0); _Application oWord ; Documents oDocs; _Document oDoc; COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR); //For optional args //Start Word if(!(oWord.CreateDispatch("Word.Application", NULL))) { AfxMessageBox("Error starting Word.", MB_SETFOREGROUND); return 0; } //Open a document oDocs = oWord.GetDocuments(); //error happens on this line oDoc = oDocs.Open(COleVariant("C:\\PROJECTS\\RRD_OFFIC...

Unable to Resolve names when i attach a .doc/.xls document using Mail Recipient as at
When i am trying to attach a word/excel document as a attachment(File>>Sent To>>Mail Recipient(As Attachment)) a new outloo envolope opens with the file attached in it, however when i enter a address in To or CC, the same does not get resolved automatically. When i click on To i am unable to see GAL, all i can see is Outloo Address Book and Contact.However if i open a new messsage and type i some name in the TO box i get all the names resolved,and i can see GA as well. All the settings in Services TAb are perfect. Additional Info: Office 2000 Outlook set to use offline access K...

Access to the [No Display Name] window was denied. Activity Tracking
We've setup activity tracking in GP 7.5, but for any third party products, if we deny security to one of those windows, we get the message: Access to the [No Display Name] window was denied. This works fine in GP 9.0 for the third party product. Is there anyway to populate a lookup table or something to make it work in 7.5? Thanks, Steven Vallarian Central Service Association ...

Clear
i had to open another user's folder to verify information in the email. i used Open, Open Other User's Folder... now when i go to Open, the other persons name shows up there. how do i remove it so that it is not listed everytime. i am using Office 2007 with SBS2003 thank you, jat You can either clear this via the registry or via OutlookTools (free). For details see; http://www.howto-outlook.com/howto/clearmru.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, ...

How do I find which screen my app is running in?
Hi, When my app starts it always starts up in the primary display of a multi-display setup. If the user decides to always put my app in screen 2, is there a way in VB that allows you to identify the screen you are running in? I would then check the screen I am running in as the user closes the app and store it in a registry setting. Then when the user re-opens the app make sure it comes up in the same screen (probably needs a test to ensure the user hasn't disconnected a screen since you last opened)? I have had a look at the "My" command and can find read only ...

Finding the biggest number out of 8 variables
Hi All Does anybody know of an easy way to find out which variable has the highest value out of 8 variables? For example, I have these values in 8 vars: 3 0 7 30 37 17 0 7 Although from a human's perspective it's easy to see that the 5th var has the highest value, how can I chose the appriopriate var programmatically? Thanks There are many solutions. You could put the values into an array and pass the array to a QuickSort routine. The QuickSort algorithm is difficult to understand, but you really don't need to know how it works. See: http://vbnet.mvp...

How do I get RSS feeds to show up in the Outlook 2003 RSS folder?
I want RSS feed subscriptions to come into my Outlook 2003 RSS folder (at work). I did this at home (same version of Outlook) but can't remember how. I subscribe to FeedDemon but was hoping to not have to open a separate program to see my RSS feeds. ...

Unable to open Excel file #4
Error message: File cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. The file was not saved as read-only and the file is on a diskette. Sounds like a bad disk. Copy the disk to the hard drive somewhere an try to open there. -- kkkni ----------------------------------------------------------------------- kkknie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=754 View this thread: http://www.excelforum.com/showthread.php?threadid=26520 Vic Cop...