The Blog of Jens Bodal

Manipulate an Excel (OLE) embedded object from within Word 2007 using VBA

I am not fully done accomplishing what I wanted to do, but since this took me the better part of 2 hours to figure out I figured I would post my findings to my blog in the hopes of Google finding them to help out others…

Basically I have an embedded Excel worksheet in my word document, which I want to be able to dynamically change depending on the value of a checkbox.  For example, if the checkbox is ticked, I want to hide a row in in the excel checkbox.  This was a lot more difficult than I thought it would be…

First, to manipulate the excel object, I need to find out its InlineShape index number, then use this code to activate it:

ActiveDocument.InlineShapes(9).OLEFormat.Activate

From there would have been as easy as just using VB to hide the row, but the entire function needs to be called thus: ActiveDocument.InlineShapes(9).OLEFormat.Object.ActiveSheet.Rows(“2:2″).EntireRow.Hidden = True

This obviously needed to be simplified so I came up with this:

Now this would be all fine and dandy, but document will definitely be changed at some point in the future, thus the chances of my InlineShape index number dereferencing itself was pretty much guaranteed.  I finally figured out how to use a bookmark to reference the object, so that no matter what happens (short of someone messing up the bookmarks of course), the index number for the object will stay constant.

To set the object as a bookmark, simply select the object, go to insert and insert bookmark, then add a unique name.  For my bookmark, it was named ‘isnTest’, without the ’s.  What this now does is creates another index of inlineshapes that starts with 1, which basically means that this bookmark and the index 1 for InlineShapes will always refer to my object.

Here is my final solution for hiding a row in an embedded excel document in word 2007:

Set xlObj = ActiveDocument.Bookmarks(“isnTest”).Range.InlineShapes(1).OLEFormat

xlObj.Activate

xlObj.Object.ActiveSheet.Rows(“2:2″).EntireRow.Hidden = False

Creating a MS Word template which populates the document name for Save As with what you want

It took me awhile to figure this out so I thought I might put my findings out on the web for all to see.

I was creating a word template that basically just had 5 areas where information needed to be input.  The document name was always a combination of the first box and the second box, and previously the way this would be done is to copy/paste a previous document to a new document, change the needed fields, update the date, then manually type in the name of the document after clicking save as. 

So here is what I did:

1.  Copy/pasted previous document to new document

2. Deleted information which needed to be changed with each document

3. Where I deleted the information I added a new form field (View=>Toolbars=>Forms) by clicking the “ab|” button on the form toolbar. 

4. Once I had all my forms put in place in the document, I needed the first and second one to have unique bookmarks that would never change so that I could populate the save as box.  This is done by going into the properties of those two fields (click the button with the hand and the note card OR doube-click your new field) and in the bookmark box I changed the name from Text# to a unique name, such as bkField1. 

5.  Now that I had unique bookmarks I needed to put in the macro that would populate the Save As box.  Go into Tools=>Macro=>Visual Basic Editor (or press Alt+F11).  Once there double click under Project/Microsof Word Objects the icon called “ThisDocument”.

6.  A text field should show up, and all you need to do is copy/paste this:

Sub FileSaveAS()
    With Dialogs(wdDialogFileSaveAs)
        Cons1 = ActiveDocument.FormFields(“bkField1″).Result
        Cons2 = ActiveDocument.FormFields(“bkField2″).Result
        .Name = Cons1 & Cons2
        .Show
    End With
End Sub

Basically “bkField1″ and “bkField2″ are the two unique bookmarks I created, and I store these to the variables Cons1 and Cons2 respectively.  These variables are then concatenated to form what populates the Save As box with whatever is in Field1 followed by Field2.  For instance if my first field contained the word Jens and my second field contained the word Bodal, when I click Save As the default name to save would be JensBodal.doc.

7.  All the complicated stuff is done.  Now I go back to the forms toolbar and deselect the Form Field Shading icon (the letter “a” surrounded by a blue shadow) and protect the form by clicking the icon with a lock.  Now save your document with whatever name you like, just make sure you save it as a word document template.

Now all you need to do when creating new documents with that template is open up that new document template, fill in the fields of the form by tabbing through them or just selecting them, and when you click save as the document will automatically default the Save As field with Field1Field2.

Well I have officially made it to London, and finally have a bit of time to update on what’s happened so far. I arrived on Tuesday with my dad and since then have traveled to and seen a vast amount of the city.

I’m not in the mood to type out a long drawn out story so I’ll will suffice with:

-I had originally figured out a place to live and thought all I had to do was move in upon arriving. Well it turned out to be a very very shady place to be and the landlord was away on “holiday” for awhile and his wife was the one who I ended up meeting with. Long story short she wanted money up front and didn’t have anything for me to sign or a receipt to give, nor did she even have a front door key. After they drawyer fell out of the wardrobe closet, I told her to have a good day.

-After a bit of stress and at least 15 pounds in phone calls, I now have a new place to stay with 1 gal and 2 guys, within a 30 minute walk to where I will be working (perfect!).

-I’ve posted some new pictures, I don’t have internet at my own place at the moment thus the brevity in my update

Cheers!

IRC Chat Added!

I now have a link on the main page to a simple java irc chat applet that connects to irc.ircube.org #ACLT.  I used a real easy to use site called Mibbit to get it going.

http://www.jensbodal.com/irc/

Website Created!

I have purchased hosting and unlimited storage space from Blue Host for ~$7 a month.  The plan is to use this website and blog to document my trip to Europe and as a way for friends and family to keep up with me.