Saving to a USB drive

[Matt S]

Hi,

I've written a macro and have gotten to the point where I'd like the macro
to save the file. I'm trying to make the macro as easy to use as possible.
To that end, I'd like the macro to save the generated file to a USB stick,
which has a drive letter that is variable. It is either E: or F:, depending
on which stick is used. The user can either save to the desktop or to the
USB key. What I'd like to happen is if the drive is not E then try F. If it
is neither, then a USB stick is not inserted and a message should pop up to
say restart the macro after inserting the stick and the macro should end.
Currently, only an error pops up to debug if no stick is inserted (which I do
not want them to do).

This is what I have so far:

' Method to save file either to USB stick or to Desktop
Dim fPath As String
fPath = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"

file = ActiveWorkbook.Name
Windows("HELO Macros.xls").Activate

If Range("D3") = "To Stick" Then
Windows(file).Activate
ActiveWorkbook.SaveAs Filename:="E:\Runlogs\" & Name & "-" &
ilngFileNumber, FileFormat:=xlWorkbookNormal

ElseIf Range("D3") = "To Desktop" Then
Windows(file).Activate
ActiveWorkbook.SaveAs Filename:=fPath & Name & "-" & ilngFileNumber,
FileFormat:=xlWorkbookNormal
End If


Thank you!
Matt

 

[Don Guillett]

Please do NOT save to removable data. SAVE and then COPY to wherever
desired. Reverse when you want it back.

 

[Matt S]

Don,

Could I get some help with the coding so that after saving to a temp file on
the hard disk I copy to the stick? Then I'd like to do the same thing... try
saving to E:, if not then F:, otherwise pop up a message and say rerun macro.

Thanks,
Matt

 

[Peter T]

Why not just try writing a test file, eg

Sub test()
Dim sDrive As String

sDrive = GetEF
If Len(sDrive) = 0 Then
MsgBox "E or F not found"
Else
MsgBox sDrive
End If

End Sub

Function GetEF() As String
Dim i As Long
Dim sFile As String
Dim iFF As Integer
Const cFILE As String = ":\TestFile.tmp"
Const cTEXT As String = "This is a test file"

drv = Array("E", "F")
On Error Resume Next
For i = 0 To 1
sFile = drv(i) & cFILE
iFF = FreeFile

Open sFile For Output As #iFF
Print #iFF, cTEXT
n = LOF(iFF)
Close iFF

If n > 0 Then
Kill sFile
GetEF = drv(i)
Exit For
End If
Next

End Function

Regards,
Peter T