I am looking for a way to automatic 2

[pullmyefinger]

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!

 

[JackTheC]

I once used RemoveDrive.exe Command line utility in a program. Just call it from within VB. Works great.

https://betanews.com/2011/11/24/use-command-line-to-safely-remove-usb-drives/

Example:

Shell "C:\Users\Jack\Downloads\removedrive\Win32\RemoveDrive.exe F:"

or

Shell "C:\Users\Jack\Downloads\removedrive\Win32\RemoveDrive.exe MyUSBdrive"

 

[pullmyefinger]

Hopefully I can concatenate a character string to give the path that the Shell command requires. I read the shell command last night and there are some switches in there that may help.

By "friendly name" I assume they mean Volume Label??

 

[JackTheC]

Yes example 2 "MyUSBDrive" in the example is the volume name / label. If the drive letter changes, the name will stay the same, so in your case maybe the best option. Thanks for the star.

 

[guitarzan]

pullmyefinger: for future reference, it looks like you are using vbscript, not VBA... if so, better to post to this forum:
forum329

 

[strongm]

>it looks like you are using vbscript

Well, apart from

Application.DisplayAlerts = False

and

ActiveWorkbook.SaveAs

Which implies VBA in Excel. And here's a VBA solution (example, not production code):

[blue][green]' Assumes you have set references to Microsoft Scripting Runtime and Microsoft Shell Controls and Automation[/green]
Public Sub EjectByName(strVolumeName As String)
    Dim fso As New FileSystemObject
    Dim USBDrive As FolderItem2
    Dim myShell As New Shell
    Dim myDrive As Drive

    For Each myDrive In fso.Drives
        If myDrive.DriveType = 1 And myDrive.IsReady = True Then  [green]'removeable and mounted[/green]
            If myDrive.VolumeName = strVolumeName Then
                Set USBDrive = myShell.Namespace(ssfDrives).Parsename(myDrive.Path)
                If USBDrive.Type = "USB Drive" Then [green]' Check it really is a USB drive[/green]
                    USBDrive.InvokeVerb "Eject"
                End If
            End If
        End If
    Next
End Sub[/blue]

 

[pullmyefinger]

' Assumes you have set references to Microsoft Scripting Runtime and Microsoft Shell Controls and Automation


Where do I find this information other than what I have already set?

I am using the visual basic editor is all I know. I have not done all that much with this.

 

[strongm]

Menu at the top of the IDE editor

Tools>References... and then add the two references I mentioned.

 

[pullmyefinger]

I did all that, called it from my original coding and it looks like nothing happened.
System tray still says "Eject Ultra Backup".

Is there a way to tell in code if the drive is actually unmounted?
Where do I look for coding references? VBA or MS Scripting?
Never worked on anything in this area, so I am clueless.

 

[strongm]

Presumably you've stepped through the code to verify that

USBDrive.InvokeVerb "Eject"

is actually running?

 

[pullmyefinger]

Yes, but I missed some things in my haste.
What I did was ro run another macro first from a command button.
This macro runs fine but does not eject the flash drive.
After I have all the backups saved, I

Call EjectByName(16) where "16" is the volume label of usb (F drive.

I put a msgbox in there before and the sub is called,
but it Skips all the Dim statements

If USBDrive.Type = "USB Drive" Then ' Check it really is a USB drive
USBDrive.InvokeVerb "Eject"
End If

Eventually it takes the IF statement and Never Does do the Eject like you said.

The more I try this, the more I realize I don't understand.

When I look in the tray to see what the results are, it says

Eject Ultra Backup
16 F:

That is why I asked for links to find out what I need to know. I don't know what needs changed, added, etc.

 

[SkipVought]

but it Skips all the Dim statements

True. Dim statements are not executable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pullmyefinger]

Strongm: take a couple statements out, substitute others and your sample works.. Thank You..

 

[kjv1611]

Thanks for posting that VBA example code. That's something I've thought about toying with in the past, and now I've got an example I can use to push that further along.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[pullmyefinger]

Good, glad someone else has a use for it. Use Strongm's version of the code, but take the IF-Endif out around "usbdrive.invokeverb "EJECT".

it will remove the flash drive faster than a child running from the dinner table after seeing veggies...

 

[strongm]

> take the IF-Endif out around "usbdrive.invokeverb "EJECT".

Truish. It is a belts and braces check to ensure we don't dismount something we should not. Alternative to removing it completely, we could actually check what the return value of USBDrive.Type actually is for your drive, and modify the If clause appropriately. (my assumption would be that in pullmyefinger's case, the eject was not running because their flashdrive was not reporting itself to be a "USB Drive")

 

[pullmyefinger]

yes, that would make the IF false and the eject would never work.