Solved

check if file exists and read data if it does

Posted on 2014-02-04
1
198 Views
Last Modified: 2014-02-05
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
0
Comment
Question by:DemonForce
1 Comment
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now