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.
--
ypukpete

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


"ypukpete" wrote:

> 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.
> --
> ypukpete

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ypukpete" <(E-Mail Removed)> wrote in message
news:9D9F52C3-0A38-4938-9CB2-(E-Mail Removed)...
>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.
> --
> ypukpete

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

"Don Guillett" wrote:

> I would never save a file to removable media. Save the file and use COPY or
> FILECOPY to copy to the flash.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "ypukpete" <(E-Mail Removed)> wrote in message
> news:9D9F52C3-0A38-4938-9CB2-(E-Mail Removed)...
> >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.
> > --
> > ypukpete
>
>

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)


On Mon, 22 Dec 2008 17:59:00 -0800, ypukpete
<(E-Mail Removed)> wrote:

>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.

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.
--
ypukpete


"Chip Pearson" wrote:

> 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)
>
>
> On Mon, 22 Dec 2008 17:59:00 -0800, ypukpete
> <(E-Mail Removed)> wrote:
>
> >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.
>