[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VBA to return list of all files with a particular file extension across all a user's logical drives

Posted on 2014-01-23
1
Medium Priority
?
941 Views
Last Modified: 2014-01-25
Hello Experts,

I have an Front end/back end Access application where the end users can have multiple back end files. All these files end in ".eve". More frequently than I would like end users "forget' where the back end files have been created. I can get them a list of files on a logical drive (example C: drive) using command line window with:

CD C:\
dir *.eve /S >C:\SEMP\evefiles.txt

I am after a VBA routine to cycle through all their logical drives and list the .eve files in each drive/folder with  "Last Accessed" information. I am familiar with GetLogicalDriveStrings but can't put put it all together into a single routine an end user can call simply.
0
Comment
Question by:JohnCling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
Beneford earned 2000 total points
ID: 39806135
This should do what you need.

Option Compare Database
Option Explicit

Dim fso As New FileSystemObject
Dim fld As Folder

Private Declare Function GetLogicalDriveStrings Lib "kernel32" _
    Alias "GetLogicalDriveStringsA" _
    (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

' Taken from http://support.microsoft.com/kb/291573
Private Function GetDriveStrings() As String
    ' Wrapper for calling the GetLogicalDriveStrings API
    
    Dim result As Long          ' Result of our api calls
    Dim strDrives As String     ' String to pass to api call
    Dim lenStrDrives As Long    ' Length of the above string
    
    ' Call GetLogicalDriveStrings with a buffer size of zero to
    ' find out how large our stringbuffer needs to be
    result = GetLogicalDriveStrings(0, strDrives)
    
    strDrives = String(result, 0)
    lenStrDrives = result
    
    ' Call again with our new buffer
    result = GetLogicalDriveStrings(lenStrDrives, strDrives)
    
    If result = 0 Then
        ' There was some error calling the API
        ' Pass back an empty string
        ' NOTE - TODO: Implement proper error handling here
        GetDriveStrings = ""
    Else
        GetDriveStrings = strDrives
    End If
End Function

' adapted from https://support.microsoft.com/kb/185601/EN-US
Sub FindFile(ByVal sFol As String, sFile As String)
   Dim tFld As Folder, tFil As File, FileName As String
   
   Dim foundFile As String
   Dim foundDetail As String
   On Error GoTo Catch
   Set fld = fso.GetFolder(sFol)
   FileName = Dir(fso.BuildPath(fld.Path, sFile), vbNormal Or vbReadOnly) 'Or vbHidden Or vbSystem
   While Len(FileName) <> 0
      foundFile = fso.BuildPath(fld.Path, FileName)
      foundDetail = FileDateTime(foundFile)
      FileList.AddItem foundDetail & " -> " & foundFile  ' Load ListBox
      FileName = Dir()  ' Get next file
      DoEvents
   Wend
   lStatus.Caption = "Searching " & vbCrLf & fld.Path & "..."
   If fld.SubFolders.Count > 0 Then
      For Each tFld In fld.SubFolders
         DoEvents
         FindFile tFld.Path, sFile
      Next
   End If
   Exit Sub
Catch:  FileName = ""
       Resume Next
End Sub

Private Sub bSearch_Click()
    Dim ds As String
    Dim i As Integer
    ds = GetDriveStrings
    For i = 1 To Len(ds) Step 4
        FindFile Mid(ds, i, 3), SearchFor
    Next i
    
End Sub

Private Sub Command44_Click()
    lStatus.Caption = ""
    While FileList.ListCount > 0
        FileList.RemoveItem (0)
    Wend
End Sub

Open in new window

0

Featured Post

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question