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
When sending a worksheet to the USB port …
Hot Topic
- vba runtime error 59
- Select/cut/insert
- Word: Normal.dot template menu bar
- Limit Macro to current workbook
- Control Break Trap will not work whilst Userform is displayed
- Possible to add multiple (300+) hyperlinks that run the same VBA code?
- Selection of only one object
- ScreenUpdating
- Select with macro based on criteria
- Elegance (and performance) Problem
Microsoft ISV
- Need to detect whether Access subform currently shows vertical scrollbar (urgent??!)
- Is there a today() function in VBA?
- Read Data from Execel with VBA and write to txt file
- Downloading Excel spreadsheet from website
- VBA Editor fails to start from Access 2003
- iexplore.exe - Application Error
- Can't open regedit
- WOW64
- microsoft need to work on explorer 7
- Run-time error '1004' in import macro
When sending a worksheet to the USB port …
RRMN
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
Kamii47
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
llorrac
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.
NagySaNa
Hey ADG
Great thanks for the help!
I did some modification to your code so it suits my application… it works fine.
Cath