pullmyefinger
Programmer
I am looking for a way to automatically Eject a flash/usb drive after backup. The backup goes to drive C: in a hardcoded path,
then uses a for-each next to find the usb drive and copy to a hardcoded folder, then resaves to C: for subsequent saves.
Andy (one of the programmers on here) sent me a huge macro that I wouldn't even begin to understand all of it. Thank you for
that info Andy, but shouldn't there be something much simpler?
The usb drive is a local drive (usually F and not a network drive. I suck at networking so hopefully there is a local drive
equivalent to RemoveNetworkDrive() or some type of EJECT command with a path?
If not, should I change the USB drive to a shared resource so i can use the network drive command with the Bforce, Bupdateprofile, etc?
I just want to be able to pull the drive out of the port and not have to go thru the click and manually eject process. No big deal to
do it, but I want to be able to work with drives way more than i can now.
Code Follows:
Sub flashtest()
' Backup to Flash Drive, then unmount flash drive automatically
Dim Ans, fs, cvl, d, dc, drvltr, drvpath1, drvpath2, drvpath3, savedtoc
Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
savedtoc = 0
'fs=vb crap, cvl=current volume label, d&dc=vbcrap/for-each
'drvltr=text drive letter, drivepath*=paths for each bkp drive
'savedtoc is if you already saved to C: and another hard drive exists >C:,
'resave back to C: because of the order of the For-Each-Next (C,D,E,F,G,etc.)
Ans = MsgBox("Plug in Flash Drive & Click OK", vbOKOnly, "Flash Backup")
Application.DisplayAlerts = False 'DISABLES Alert Prompts (auto file overwrite)
For Each d In dc
cvl = Dir(d.driveletter & ":", vbVolume)
If d.drivetype = 1 And (cvl = "16" Or cvl = "32" Or cvl = "64") Then 'Removable - Flash Drive
If d.IsReady Then
drvpath2 = d.driveletter & ":\XL\flashtest.xls"
ActiveWorkbook.SaveAs (drvpath2)
Ans = MsgBox("saved to flash", vbOKOnly, drvpath2)
End If
End If
If d.drivetype = 2 And cvl = "7" Then '2-Hard Disk
drvpath1 = d.driveletter & ":\Users\A\My Documents\XL\flashtest.xls"
ActiveWorkbook.SaveAs (drvpath1)
Ans = MsgBox("saved to Drive C:", vbOKOnly, "Save C:\..")
savedtoc = 1
End If
Next
If savedtoc = 1 Then
ActiveWorkbook.SaveAs (drvpath1)
End If
If you have any suggestions or any help, I would appreciated it!
then uses a for-each next to find the usb drive and copy to a hardcoded folder, then resaves to C: for subsequent saves.
Andy (one of the programmers on here) sent me a huge macro that I wouldn't even begin to understand all of it. Thank you for
that info Andy, but shouldn't there be something much simpler?
The usb drive is a local drive (usually F and not a network drive. I suck at networking so hopefully there is a local drive
equivalent to RemoveNetworkDrive() or some type of EJECT command with a path?
If not, should I change the USB drive to a shared resource so i can use the network drive command with the Bforce, Bupdateprofile, etc?
I just want to be able to pull the drive out of the port and not have to go thru the click and manually eject process. No big deal to
do it, but I want to be able to work with drives way more than i can now.
Code Follows:
Sub flashtest()
' Backup to Flash Drive, then unmount flash drive automatically
Dim Ans, fs, cvl, d, dc, drvltr, drvpath1, drvpath2, drvpath3, savedtoc
Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
savedtoc = 0
'fs=vb crap, cvl=current volume label, d&dc=vbcrap/for-each
'drvltr=text drive letter, drivepath*=paths for each bkp drive
'savedtoc is if you already saved to C: and another hard drive exists >C:,
'resave back to C: because of the order of the For-Each-Next (C,D,E,F,G,etc.)
Ans = MsgBox("Plug in Flash Drive & Click OK", vbOKOnly, "Flash Backup")
Application.DisplayAlerts = False 'DISABLES Alert Prompts (auto file overwrite)
For Each d In dc
cvl = Dir(d.driveletter & ":", vbVolume)
If d.drivetype = 1 And (cvl = "16" Or cvl = "32" Or cvl = "64") Then 'Removable - Flash Drive
If d.IsReady Then
drvpath2 = d.driveletter & ":\XL\flashtest.xls"
ActiveWorkbook.SaveAs (drvpath2)
Ans = MsgBox("saved to flash", vbOKOnly, drvpath2)
End If
End If
If d.drivetype = 2 And cvl = "7" Then '2-Hard Disk
drvpath1 = d.driveletter & ":\Users\A\My Documents\XL\flashtest.xls"
ActiveWorkbook.SaveAs (drvpath1)
Ans = MsgBox("saved to Drive C:", vbOKOnly, "Save C:\..")
savedtoc = 1
End If
Next
If savedtoc = 1 Then
ActiveWorkbook.SaveAs (drvpath1)
End If
If you have any suggestions or any help, I would appreciated it!