Auto identify drive

[vba317]

Is there a way to automatically ID a drive. Sometimes when I put my USB flash drive in my computer it can be either a G drive, H drive or E drive. My program is set up to save my program results on the H drive. So if the computer ID's the drive as something else I have to manually edit the macro. Is there a way to solve this?

Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
        'Change path to suit
       
            '.LookIn = "F:\Investments\Summary\Daily_A"
            '.LookIn = "G:\Investments\Summary\Daily_A"
            .LookIn = "H:\Investments\Summary\Daily_A"
                        
            .FileType = msoFileTypeExcelWorkbooks
            
            If .Execute > 0 Then 'Workbooks in folder

                For lCount = 1 To .FoundFiles.Count 'Loop through all files.
                    'Open Workbook x and Set a Workbook variable to it
                    Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                    'ActiveWindow.WindowState = xlMinimized
                    Call BulkQuotesXL.UpdateData
Set wbCodeBook = ThisWorkbook
R1 = ActiveSheet.UsedRange.Rows.Count
                                    R2 = "F" & R1
                                    SR = R1 - 22
                                    ActiveWindow.ScrollRow = SR
                                    Range(R2).Select
                            End Select

                        End With

                    Next
                    
                    wbResults.Close SaveChanges:=True
                    
                Next lCount

           ' End If

        End If

    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    '
End Sub

 

[HarleyQuinn]

I don't have a USB drive to test this with at the moment but I think you can use the UNC path to it so you don't have to use a drive letter at all.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[vba317]

I tried to change the path name to the UNC convention using
LookIn = "\\Investments\Summary\Daily_A"

This solution did not work. I tried using the debug functions and it seems like the program is searching on the C drive instead of the USB drive. Is there a different solution to this type of problem?

 

[HughLerwill]

You may consider placing the program onto the USB drive and have it autorun when inserted.

 

[DaveInIowa]

Will something like this work? Does your USB drive have a volume name? I used "Investments" in this example; change it you whatever you've named your drive to better identify it.

' Add a reference to:
' Microsoft Scripting Runtime

Public Function GetInvestmentsUSBDriveLetter() As String
    Dim d As Drive
    Dim fso As New FileSystemObject
    
    GetInvestmentsUSBDriveLetter = ""
    
    For Each d In fso.Drives
        With d
            If (.IsReady) Then
                If (.DriveType = Removable And .VolumeName = "Investments") Then
                    GetInvestmentsUSBDriveLetter = .DriveLetter
                    Exit For
                End If
            End If
        End With
    Next
    
End Function