Copy image from Word to Excel

[HighBomber]

Hello,

I am converting older word documents to a new excel template.

I had a previous thread relating to these documents. You can find it here:
http://www.vbaexpress.com/forum/showthread.php?p=267401#post267401

I do not know the name of the image in the file. It is the only image in the word file other than the header. Is there a way I can copy this image over to the Excel template?

Thank you,

[Frosty]

You can take a look at the .Shapes collection or the .InlineShapes collection... if it's truly the only image, then you can reference it as something like ActiveDocument.Content.Shapes(1) or ActiveDocument.Content.InlineShapes(1)

But I suspect it may be something a little different than that.

[HighBomber]

Thanks Frosty. When I try "ActiveDocument.Content.Shapes(1)" I receive the error "Method or data member not found" and when I try "ActiveDocument.Content.InlineShapes(1)" I receive the error "The requested member of the collection does not exist."

[Frosty]

Well, that's how you reference... But you've got to do something. You could try .Cut, or .Copy... And then deal with getting it into excel. You're not really giving us a lot to work with in your description. No sample doc, no code you're currently using.

[HighBomber]

My apologies, but I can't give an example of the documents. There is nothing special about them, though. One is a word document containing an image and the other is an excel document.

Here is the code I just tried to use:

Private Function copyImage(cell As String)
Workbooks("TemplateName").Sheets("SheetName").Range(cell) = ActiveDocument.Content.InlineShapes(0).Select
End Function


This code returns the error "The requested member of the collection does not exist." I receive the same error if I try the document object:

Private Function copyImage(cell As String)
Workbooks("TemplateName").Sheets("SheetName").Range(cell) = WordDoc.Content.InlineShapes(0).Select
End Function


It would seem in can't find the image in .InlineShapes(*). I may try creating an instance of InlineShapes and trying a ForEach loop.

All suggestions greatly appreciated.

[Frosty]

Well, first off... You can post a sample document. It just takes a little time to set it up so there is no client specifIc data in it. When you say there's nothing special... That's meaningless data. What would you consider special? There are many ways to achieve the same printed out look in a document. Just because you use x-method and someone else uses y-method doesn't make either formatting approach "special"

However, if you don't want to take the time to work up an approximation sample document that you want code to work on, I will only give you suggestions, and not code. This is not to penalize... But I don't like spending the time it takes to throw multiple ways of achieving something until one works on your mystery doc.

So first suggestion...
You're looking at two different applications (word and excel), you need to work on getting working code to take info from word and putting it into excel. There are lots of examples. Even some recent threads here. Forget shapes for the moment, and go find that code. Post what you find and what doesn't work about it

[HighBomber]

I am sorry Frosty. I will see what I can do about getting a dummy document that can serve as an example on what I am trying to accomplish.

If you need more information on these documents please look into the thread I linked in my first post. My macro already has text and checkbox values being copied from the Word document to the Excel spreadsheet. This image is the last item that needs to be copied for this project to be complete.

Again, the problem is how to copy an image (Sometimes floating, sometimes not) into an excel document cell.

I also should mention I have tried:

Private Function copyImage(cell As String)
Workbooks("TemplateName").Sheets("SheetName").Range(cell) = WordApp.Selection.Copy
End Function
when the image is highlighted in word, however, that only seems to return an ASCII character in the spreadsheet.

Thanks for the help.

[Frosty]

I had already looked in that thread, and didn't see anywhere a sample of the entirety code you're working with. Are you using Option Explicit? Are you using module level variables? Because even the code you just posted shouldn't work unless you're using module level variables. And troubleshooting module level variables requires seeing the entire module.

Variables outside of individual routines (module level variables and public variables) are devilish to deal with for the beginning programmer. Because without understanding the concept of "scope" regarding programming, you will hamstring your own ability to troubleshoot your functions. Instead, you should focus on learning how to pass variables between functions and use the Locals window while stepping through the code.

Also, the problem with your descriptions "nothing special" and "sometimes floating, sometimes not" is that there is no readily available code to deal with that kind of nebulous description. That's why a dummy document with sample set ups becomes easier to help you with-- because you don't seem to (and I say this not as an insult, just to be direct) have the depth of understanding about Word formatting to be able to accurately describe the situation so that I can give you a good coding solution. The only thing I can do is give you 14 different types of coding solution in the hopes that one sticks. That's a lot of time to do that.

So I prefer to help you learn how to do it yourself, or, barring that approach, have you prepare a dummy document which we could reliably run the code on in such a way that it would also work on the sensitive document.

Or someone else might pop into the thread with another idea... but those are mine:
1. Post all the code you're using, not just single line functions that don't work.
and/or
2. Post a sample document.

In regards to your one line function which doesn't work... I highly doubt you can set the contents of a cell equal to whatever you have selected in Word. And certainly you can't set it equal to the .Copy *method* (which takes the contents of the range and puts a copy into the clipboard). You will very likely have to use the .Paste or .PasteSpecial functionality within Excel. Have you tried recording a macro, in Excel, to successfully paste in the image you had previously copied in Word? That will give you a hint of how to go.

I'm not trying to be harsh, but there's a lot of misunderstanding here. It may feel like you're close to this project being done, but I think you're missing a lot of basic concepts so that you will end up coming back and asking the same questions again for perhaps other projects, or in order to troubleshoot why something isn't working.

[HighBomber]

Thanks Frosty!

Using the clipboard worked very well. My mistake earlier was:


ActiveDocument.Content.Shapes(1)


...when it should have been...


ActiveDocument.Shapes(1)


My final solution was using the clipboard and using:

WordDoc.Shapes(1).Select
WordApp.Selection.Copy
Workbooks(TemplateName).Sheets(SheetName).Paste Destination:=Workbooks(TemplateName).Sheets("WPS").Range(cell)


The macro doesn't seem to care if the image is floating or not.

Thanks again,

[Frosty]

Hmm... The .Shapes collection can be finicky, but I'm surprised there is a difference between doc.shapes and doc.content.shapes.

You can simplify by removing the need to select, I think.

WordDoc.Shapes(1).Copy (or .ShapeRange.Copy). I'm not at a computer, but there should be a way that you can copy it without needing to select it

[fumei]

Here is the code I just tried to use:


VBA:
Private Function copyImage(cell As String)
Workbooks("TemplateName").Sheets("SheetName").Range(cell) = ActiveDocument.Content.InlineShapes(0).Select
End Function
VBA tags courtesy of www.thecodenet.com

This code returns the error "The requested member of the collection does not exist." I receive the same error if I try the document object:
As indeed it should give that error. There is no index 0.