Thread: VBA excel - word (2003)
2008-04-02, 19:33 #1
- Join Date
- Feb 2002
- Fort Collins, Colorado, USA
- Thanked 0 Times in 0 Posts
VBA excel - word (2003)
I know that you guys often provide a VBA solution so I hope you can help me. I need to do this repeatedly:
1. Working in excel say ExcelDoc.xls, which has a block of cells on each tab, consisting of text in cells and perhaps a picture as well
2. Create and open a Word file, WordDoc.doc
3. In ExcelDoc.xls, select the block and copy to clipboard (ctrl-A on each tab will do it; the range could also be named but I don't think that is necessary)
4. Jump to Worddoc.doc and paste special as enhanced metafile
5. Repeat 3 and 4 until done.
I can do this manually just fine, but I need to automate it. The macro recorder won't do it. I'm hoping someone out there can give me a VBA routine that can. The routine would preferably reside in ExcelDoc.xls and run from there.
It would be really good if I had a way to save each pasted "image" as a stand-alone file, blockN.emf. I presently do this by pasting the block into Visio and then SaveAs EMF file in Visio. That also works great but I need to do it without Visio.
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2008-04-02, 19:53 #2
- Join Date
- Mar 2002
- Thanked 16 Times in 16 Posts
Re: VBA excel - word (2003)
Try the code in the attached text file. It uses late binding, so you don't have to set a reference to the Microsoft Word 11.0 Object Library.