Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 "ypukpete" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 "ypukpete" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to a flash drive
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when I put my flash drive in, the computer does not give me drive | New Users to Excel | |||
how do I copy an existing file to a flash drive | Excel Discussion (Misc queries) | |||
How do I copy excel file to a flash drive? | Excel Discussion (Misc queries) | |||
Can I save to hard drive AND my flash drive at the same time? | Excel Discussion (Misc queries) | |||
How do I use flash drive in Excel.(selected drive is not in use) | Excel Discussion (Misc queries) |