Exporting embedded OLE objects to file

[atvaluesoftware]

I have an Access database embedded in my Excel spreadsheet as an OLE
(created by going to Insert > Object... > Create from File). How would
I now export this embedded OLE to an independent mdb file?

The process for doing it right now is to simply click on the OLE with
the mouse and copying it to clipboard in Excel. Then, I go to Windows
Explorer and click Paste. (Windows has an internal way of streaming the
copied OLE object in the clipboard to an actual file!)

Is there any macro to simulate this? Specifically, the hard part is
pasting in Windows Explorer.

 

[atvaluesoftware]

Extra info:

(Methods I've tried, unsucessful)

One way in which I did it was to copy the OLE to the clipboard then go
to the File > Open window. Then sendkeys tab to the white file part,
sendkeys Ctrl+V to paste the file. This works, but the sendkeys is
unreliable.

Another way I tried was to copy the the OLE to clipboard then use API
on Windows Explorer. I tried running RunMenuByString (common example
function found online) but for some reason the API will not work in
Windows Explorer, it did work in Notepad (?). Is Windows Explorer
protected somehow from API calls?

 

[okaizawa]

Hi,
Here is an exsample:

Sub Test()
'copy oleobject
ActiveSheet.OLEObjects("Object 1").Copy

'paste to activeworkbook's path
CreateObject("Shell.Application") _
.Namespace(ActiveWorkbook.Path) _
.Self.InvokeVerb "Paste"
End Sub

InvokeVerb Method
http://msdn.microsoft.com/library/e...l/reference/objects/folderitem/invokeverb.asp

 

[atvaluesoftware]

What about pasting the object with a particular filename? With the code
given, the object is pasted correctly. However, by default, the
filename used in the pasting is the filename that was specified during
the Insert > Object > Create from File process (initial insert of the
object).

(For explicit example: If the path for Insert > Object > Create from
File is C:\av.mdb, the output will be always been av.mdb when I use
your code)

Is there anyway to change this default filename used in the paste?

If not, is there anyway to get the current "file source" (filename) of
an OLE object?