Copy to a flash drive

[ypukpete]

i am using Excel 2003. I am looking for VBA code which I can use with a
command button which will open the Copy-to Window so that I can copy the
workbook I have open to a Flash Drive in a USB port. All help gratefully
accepted
Thanks.

 

[FSt1]

hi
i use 2003 also. here is the code i use to save to my flash drive. the code
lists my flash drive as G. your's may be different.
saving to the flash drive takes a while almost like saving to the old time
floppy disks. don't know why. it's faster to transfer the file vis explorer
but that is rather manual.
Sub FlashSave()
Application.DisplayAlerts = False
Application.StatusBar = "Saving to Flash"
ChDir "G:\Excel"

ActiveWorkbook.SaveAs Filename:="G:\Excel\Expences2008.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False ,_
CreateBackup:=False

Application.StatusBar = "saving to c drive"

ChDir "C:\Documents and Settings\FSt1\My Documents\XL"

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\FSt1\My Documents\XL\Expences2008.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= False, CreateBackup:=False

Application.DisplayAlerts = True
Application.StatusBar = False
MsgBox "Finished Saving."
End Sub

regards
FSt1

 

[Don Guillett]

I would never save a file to removable media. Save the file and use COPY or
FILECOPY to copy to the flash.

 

[JMay]

So can this be done withing Excel VBA - Would you do it differently from
FSt1's code?

 

[Chip Pearson]

If I understand correctly, you can use either of the following
procedures to copy the workbook ThisWorkbook to a flash drive,
assuming you know the drive letter of the flash drive.


Sub AAA()
Dim SaveFileName As Variant
Dim FlashDrive As String
FlashDrive = "K:\"
SaveFileName = Application.GetSaveAsFilename(FlashDrive, _
"Excel Files (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm")
If SaveFileName = False Then
Debug.Print "cancelled"
Else
ThisWorkbook.SaveCopyAs SaveFileName
End If
End Sub

Sub BBB()
Dim FlashDrive As String
FlashDrive = "K:\"
With ThisWorkbook
.ChangeFileAccess xlReadOnly
FileCopy ThisWorkbook.FullName, FlashDrive & "\" & _
ThisWorkbook.Name
.ChangeFileAccess xlReadWrite
End With
End Sub

As an alternative, you can display a Browse Folder dialog to the user,
let him pick the root of the flash drive and SaveCopyAs to that
folder. E.g.,

Sub CCC()
Dim FolderName As String
FolderName = BrowseFolder("Select the flash drive's root.")
If FolderName = vbNullString Then
Debug.Print "cancel"
Else
ThisWorkbook.SaveCopyAs FolderName & "\" & ThisWorkbook.Name
End If

End Sub

The BrowseFolder function is described at
http://www.cpearson.com/excel/BrowseFolder.aspx

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ypukpete]

Thanks guys for all your help,
I tried FSt 1's code but had problems getting it to work correctly (must
have been doing something wrong)...but thanks for your help FSt1. I tried
Chip's first method and it is just what I wanted.
Wishing you all a merry christmas.