When sending a worksheet to the USB port …

Hot Topic

Running a macro if new mail if from a specific address
SendKeys From Excel
converting text to Hours Minutes and Seconds
ExecuteCommand and PrintOutput procedures
Creating MROUND function
Action Pack Question
Redistributing SSAS cubes
Size Form to fit user resolution
Sum of hours for month following submission date only
.onAction assign value

Microsoft ISV

Setting a global variable and referring to it from a private sub routine?
How to allow cookies for Windows Live ID
CDONTS
error in formula
the function about string "Right()"
Help with a formula
Move a DataLabel in a chart in excel
Application.File Search.
VBA Powerpoint - identifying textboxes by type
Solved - Search all text objects - PowerPoint VBA

When sending a worksheet to the USB port …

Hey,

When sending a worksheet to the USB port … (memory stick).

How do I detect if the memory device is not in the USB port

…. and how do I handle the error if its not there

Best Regards

Cathrine

Answer this question

When sending a worksheet to the USB port …

  • Deza

    Hey Derek

    Thanks for your reply.

    I have a workbook that I want to save to a memory stick. How can I write the code so it will detect witch drive letter the USB have

    Cath

    Dim fsoObj As Scripting.FileSystemObject
    Set fsoObj = New Scripting.FileSystemObject
    With fsoObj
    If .Drives("E:").IsReady = True Then
    'code goes here
    End If
    End With
    Set fsoObj = Nothing

  • clkdiv

    Hey ADG

    Great thanks for the help!

    I did some modification to your code so it suits my application… it works fine.

    Cath

  • SaintAnger

    Hi,

    You could check that the directory/drive exists. Your memory stick should have a drive letter you could use the FileSystemObject to determine if the drive is there. The only thing is the drive isn't always going to be the same, for example it might appear as drive E:\ most of the time but it might not always be the case.

    Sub ShowDriveList()
    On Error Resume Next
    Dim fs, d, dc, s, n
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set dc = fs.Drives
    For Each d In dc
    s = s & d.DriveLetter & " - "
    If d.DriveType = 3 Then
    n = d.ShareName
    Else
    n = d.VolumeName
    End If
    s = s & n & vbCrLf
    Next
    MsgBox s
    End Sub

    This code snippet lists all the drives on your machine, you can adapt this to check if drive E:\ (for example), your USB drive, exists.



  • spotluri

    Hi

    The USB will be a removeable drive (drivetype =1), beware if you have a floppy drive this is also type 1. The below modified help text example lists the removeable drives. If A: is your only other removeable drive any, other drive letter that shows up will be your USB.

    Sub ShowDriveList()
    Dim fs, d, dc, s, n
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set dc = fs.Drives
    For Each d In dc

    If d.drivetype = 1 Then
    If d.isready Then
    s = d.Path

    End If
    End If
    s = s & vbCrLf
    Next
    MsgBox s
    End Sub
  • When sending a worksheet to the USB port …
Answer this question