check if file exists and read data if it does

Hi,

I have a USB Drive with a file called xcel.axe on it, how can I get excel to scan all EXTERNAL drives, i.e. for USB thumbdrives hunting for this file, and if found load its contents into a variable called password, the .axe file is just a txt file with one line.

the file is stored in the root of my usb
DemonForceAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Robberbaron (robr)Connect With a Mentor Commented:
i would be using the FileSystemObject fso

fso has a drives collection. I assume all 'external' drives are removable...

Sub Test1()
    MsgBox "AXE text=" & GetAxeText

End Sub

Function GetAxeText() As String
    Const dtRemovable = 1
    
    Dim dfldr As Object
    Dim filenameReqd As String
    GetAxeText = ""   'set as blank
    filenameReqd = "xcel.axe"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set dc = fso.Drives
    For Each d In dc
        Select Case d.DriveType
           Case 0: ' "Unknown"
           Case 1: ' "Removable"
              'this is a candiate drive
              Set dfldr = d.RootFolder
              flname = dfldr & filenameReqd
              If fso.FileExists(flname) Then
                   Set ts = fso.OpenTextFile(flname, 1)
                   txtLine = ts.ReadLine
                   ts.Close
                   GetAxeText = txtLine
                   Exit Function
              End If
           Case 2: ' "Fixed"
           Case 3: ' "Network"
           Case 4: ' "CD-ROM"
           Case 5: ' "RAM Disk"
        End Select
    Next
End Function

Open in new window

0
All Courses

From novice to tech pro — start learning today.