How can a HDD serial# locate a Drive?

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
365
Platform
Windows
Hi All,

I am using Excel 2003 to backup files automatically to 2 external drives.
The user initially selects the drives for the project to reside on. Since these removable drives could be unplugged and reattached, there is a strong chance that they will have different drive letters assigned.

Instead of requiring the user to continually browse for the drive I would like to use the HDD serial number (since it will never change) to locate the target and copy the files. I know that I could loop through the drives but I would rather just use the copy command.
Example: FileCopy "C:\Original_File", "E:\Copied_File"

My question is how do I write the command using the serial number instead of the drive letter.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Can get path and serial number like this :
Code:
'========================================================================================
'- GET EXTERNAL HARD DRIVES (includes flash drives)
'========================================================================================
Sub GET_EXTERNAL_DRIVES()
    Dim FSO, Drive, Counter
    '------------------------------------------------------------------------------------
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Counter = 0
    '------------------------------------------------------------------------------------
    '- LOOP ALL DRIVES
    For Each Drive In FSO.drives
        If Drive.isready And Drive.drivetype = 1 Then
            Counter = Counter + 1
            MsgBox ("Path   :  " & Drive.Path & "\" & vbCr _
                  & "Serial : " & Drive.serialnumber)
        End If
    Next
    '------------------------------------------------------------------------------------
    MsgBox ("Found " & Counter & " external drive(s).")
End Sub
'========================================================================================
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
365
Platform
Windows
Thanks Brian,

I had decided to go with a For Loop to match up the pre-selected Serial number. Once it finds the number I just wrote the FileCopy command as usual. I did like the way you wrote the For Loop, it was a little more straight forward then what I have been using.

Mark
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,656
Messages
5,512,656
Members
408,909
Latest member
Burnrose

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top