MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extract embedded objects from Excel to HD

  Asked By: Lisa    Date: Mar 21    Category: MS Office    Views: 5747

I routinely get worksheets that have many embedded objects in them
such as other spreadsheets, zip files, word docs, etc.

I need to create a macro (or any other way if someone is aware of)
that will go through the sheet, take each embedded object
individually, and extract and and save it to a folder on the hard
drive with the name it has in the sheet in correct file format.

I have searched the internet for hours and have seen many code
postings with people doing this with embedded objects in Word, but
none in Excel. Apparently word seems to have some functionality for
identifying embedded objects that Excel doesn't contain, or so I
gather from my research.



2 Answers Found

Answer #1    Answered By: Freya Brown     Answered On: Mar 21

I think there are a few sites that will show you research on the subject.
E.g. have a look at this thread

The person seems to have solved a very similar problem to yours. He wasn't
particularly pleased with the solution, but did feel it warranted

Answer #2    Answered By: Dep Tran     Answered On: Mar 21

Next, let me tell you that I am NOT a programmer. I have taken some
programming classes and I do enjoy fooling around with programming but
only to the extent that it can solve a practical problem that I or a
co-worker may have that will increase productivity.

I am actually an accountant. The link you provided appears to be a
programmatic solution related to the C language. I will need a
solution that is composed an executed through VB as it would be the
only way I know to implement it in the manner that I need it.

I found the following code which works for text and zip objects  only.

Private Sub CommandButton1_Click()
Dim EmbedObj As OLEObject

For Each EmbedObj In ActiveSheet.OLEObjects
With EmbedObj
'See if it is Embedded
If .OLEType = xlOLEEmbed Then
Select Case .progID
'Check the type
Case "Package"
'.Verb xlVerbOpen
.Verb xlVerbPrimary
Case Else
'Not dealt with
End Select
'Linked. Get the filename ?
End If
End With

End Sub

I found another one which I can't find now to post that seemed
promising but I kept getting a "Filename" error and couldn't fix it. I
created my own that will automatically open and save  an excel  file and
then close the excel file  but it only works for excel files  and you
have to actually click on the file and I used an input box for the
user to enter the filename.

What I want to happen is:

1. The user opens a sheet  with various forms of embedded  files.
2. The user runs the macro.
3. macro  finds all embedded files and stores them in their native
format with the filename listed in the container in a predefined location.

I've seen versions of this question asked all over the place. Quite
frankly, I'm stunned that it's seems to be such a hard problem to
solve. It would seem to me, this would be something that would be
simple to do, but I guess not.

Didn't find what you were looking for? Find more on Extract embedded objects from Excel to HD Or get search suggestion and latest updates.