Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


I don't think I changed anything, but...

I don't think I changed anything, but...

I don't think I changed anything, but...

Runtime error 464; Object Required. Oops..

*** This is the Original Flashback Sub written to save a worksheet
to the hard drive and whatever USB drive is connected. I am now
getting a run-time error that says "Object Required" on the line
marked with asterisks.

Thank you again to strongM for writing and letting me use the
EjectByName Subroutine!


' Backup to Flash Drive, then back to the hard drive(s) for next Open

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)

'Paste Date backed up as Paste Special/VALUES

Worksheets("this month").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Selection.Font.Bold = True
Selection.Font.Italic = True
Selection.Font.ColorIndex = 14
Selection.Font.Size = 12

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\b.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\Documents\XL\b.xls"
ActiveWorkbook.SaveAs (drvpath1)
Ans = MsgBox("saved to Drive C:", vbOKOnly, "Save C:\..")
savedtoc = 1
End If

If savedtoc = 1 Then
ActiveWorkbook.SaveAs (drvpath1)
End If

Application.DisplayAlerts = True 'Prompts Enabled

**** Run-time error 1004 occurs at the line below ****
**** "Object Required" is the message. I don't remember
**** changing anything in either routine.

If (d.DriveType = 1 And cvl = "16") Then
Call EjectByName("16")
End If

If (d.DriveType = 1 And cvl = "32") Then 'Removable - Flash Drive
Call EjectByName("32")
End If

If (d.DriveType = 1 And cvl = "64") Then 'Removable - Flash Drive
Call EjectByName("64")
End If


End Sub


Static Sub EjectByName(strVolumeName As String)

'Donated to me by strongM from tek-tips.com

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 'Removeable and Mounted

If myDrive.VolumeName = strVolumeName Then

Set USBDrive = myShell.Namespace(ssfDRIVES).ParseName(myDrive.Path)

USBDrive.InvokeVerb "Eject"

End If
End If

End Sub

Any help would be appreciated. I do understand that the line with the error may not be
the problem. I don't remember changing any Configuration Settings either.

RE: I don't think I changed anything, but...

Could you edit your post and use TGML CODE tag to format your code?

---- Andy

There is a great need for a sarcasm font.

RE: I don't think I changed anything, but...

For me there is no problem with standalone EjectByName procedure, also d is proper drive. Check if your project keeps variables after saving: in VBE Tools>Options dialog mark "Notify Before State Loss" option in "General" tab and run the code again.
As you use early binding in EjectByName procedure, you should have references to Scripting and Shell32 libraries, so you can declare and instantiate objects in the first procedure in the same way as in second.


RE: I don't think I changed anything, but...

The runtime error is 464, object required.

I did do the "Notify Before State Loss" in the
General Tab. It made no difference.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close