Find File Path to USB Drive

**andyrads** · 06-12-2009, 11:19 AM

Hi! I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.

The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?

Many thanks for anyone who can help!

**Leith Ross** · 06-12-2009, 01:03 PM

Hello andyrads,

Welcome to the Form!

This macro will search all the local drives for the file, and returns either an empty string "" or the file name when the search is successful. Add your file folder path and the file name to the macro. These are marked in red. Copy this code into a standard VBA module.

Function SearchDrives() As String

  Dim DriveLetter As String
  Dim FileName As String
  Dim FilePath As String
  Dim FN As String
  Dim I As Integer
  
    FilePath = "Documents and Settings\Owner\My Documents"
    FileName = "Test.xls"
    
    For I = 1 To 26
      DriveLetter = Chr$(64 + I) & ":\"
      On Error GoTo FileError
      FN = Dir(DriveLetter & FilePath & "\" & FileName)
      If StrComp(FN, FileName, vbTextCompare) = 0 Then
         SearchDrives = FileName
         Exit Sub
      End If
GetNextDrive:
    Next I
        
  Exit Function
  
FileError:
    If Err = 52 Then
       Err.Clear
       Resume GetNextDrive
    Else
       On Error GoTo 0
       Resume
    End If
  
End Function

**andyrads** · 06-12-2009, 01:37 PM

Hi! And thanks for the quick reply. I've copied and pasted the code into my current module, but this causes a syntax error "end sub expected". I think I'm doing something wrong?

**andyrads** · 06-12-2009, 01:51 PM

Hi Leith Ross! I've tried again and put the code into a standard macro. I now get an error message "Invalid outside procedure". Could you put the code above into a complete macro for me to fiddle with? Many thanks for your help.

**Kenneth Hobson** · 06-12-2009, 02:33 PM

Here is another method.

Sub Test_TheFileName()
  Dim sFilename As String
  
  sFilename = ThisWorkbook.FullName
  sFilename = TheFileName(sFilename, "")
  MsgBox sFilename, , "Guess1"
  
  sFilename = ThisWorkbook.FullName
  sFilename = TheFileName("", sFilename)
  MsgBox sFilename, , "Guess2"

  sFilename = TheFileName("f:\myfiles\excel\ken.xls", "g:\myfiles\excel\ken.xls")
  MsgBox sFilename
  
End Sub

Function TheFileName(guess1 As String, guess2 As String) As String
  Select Case True
    Case Dir(guess1) <> "" And guess1 <> ""
      TheFileName = guess1
    Case Dir(guess2) <> "" And guess2 <> ""
      TheFileName = guess2
  End Select
End Function

**Leith Ross** · 06-12-2009, 04:45 PM

Hello andyrads,

Sorry for the delay. I had to leave for awhile. The "Sub" statement should be "Function". I write more "Sub" procedures than functions and just automatically type "Sub". Also, I changed the code to start with drive "E", since "A-D" are generally hard drives.

Function SearchDrives() As String

  Dim DriveLetter As String
  Dim FileName As String
  Dim FilePath As String
  Dim FN As String
  Dim I As Integer
  
    FilePath = "Documents and Settings\Owner\My Documents"
    FileName = "Test.xls"
    
   'Search drives "E" through "Z"    
    For I = 5 To 26
      DriveLetter = Chr$(64 + I) & ":\"
      On Error GoTo FileError
      FN = Dir(DriveLetter & FilePath & "\" & FileName)
      If StrComp(FN, FileName, vbTextCompare) = 0 Then
         SearchDrives = FileName
         Exit Function
      End If
GetNextDrive:
    Next I
        
  Exit Function
  
FileError:
    If Err = 52 Then
       Err.Clear
       Resume GetNextDrive
    Else
       On Error GoTo 0
       Resume
    End If
  
End Function