Discrepancy between Word and Excel syntax?

[prawer]

Hi All,

I should disclaim that I'm quite novice at this, so my approach to writing macros is to record individual steps to determine the appropriate method, then copy and paste.

In any event, I have a macro that creates a word doc from content in an excel file. I copied over the code for inserting a text field from the Word Macro Recorder:

Selection.FormFields.Add Range:=Selection.Range, Type:= _
        wdFieldFormTextInput

And pasted it in my Excel project (with msWord):

With msWord
        .Selection.TypeParagraph
        .Selection.InsertDateTime DateTimeFormat:="M/d/yyyy", InsertAsField:=False, _
            DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
            InsertAsFullWidth:=False
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Dear "
        .Selection.FormFields.Add Range:=Selection.Range, Type:= _     wdFieldFormTextInput
        .Selection.TypeParagraph
End With

When I attempt to run this I get a "wrong number of arguments or invalid property assignment" error at the line that attempts to add the text field. Is this because of a discrepancy in syntax between Word and Excel, or am I making some other mistake?

Any help you can provide would be great. Thanks in advance!

 

[GTO]

Hi Prawer,

We're faced with a bit of guessing here, as we cannot see how you created msWord, and whether its referencing the app or a doc. Presuming you used CreateObject, then we'd need to know how you declared msWord - as an Object or more like Word.Application (and set a reference to Word's library).

By what you described, it sounds as if the object is late-bound, in which case constants like wdFieldFormTextInput will not work. You would need to look while the code is still in Word, and right-click over it. Pick Quick Info, and it will tell you the actual value of the constant, which can then be used in the late-bound code in excel.

Mark

 

[prawer]

Interesting. My declaration of word:

Dim msWord As New Word.Application
    msWord.Visible = True
    msWord.Documents.Add

When I look at the quick info for wdFieldFormTextInput, I see a code in the form "70 (&H46)".

How do I use that? I tried

Type:=70

and

Type:=&H46

but am still getting errors.

 

[GTO]

Okay, you have set a reference to Word's library and are writing early-bound. In that case, using the object's constants (such as: wdFieldFormTextInput) are fine.

You were on the right track in your title. It is a syntax issue, as Excel also has a Selection property. Change the line to:

<font face=Courier New>        .Selection.FormFields.Add Range:=.Selection.Range, Type:=wdFieldFormTextInput</FONT>

This way the dot in front of Selection gets it hooked to the With.

Mark

 

[prawer]

Fabulous. That worked! From my very hazy understanding of what I was doing I didn't realize that the second selection in that line needed to go with with.

Thanks m8.

 

[prawer]

Out of curiosity:

GTO's reference to late/early bound led me to google those terms because I had no idea what they meant.

Now, I'm wondering if after finishing a functional version of this in Excel 2007 I should change the code to late bound? The project is intended as a tool to be deployed to about 18 people who have either Excel 2003 or Excel 2007 and I want to make sure it works for the 2003 folks.

 

[GTO]

IMHO, the short answer is yes. You will, I believe, most likely find two primary issues upon testing in 2003 vs 2007 - one related to early vs late-bound, the other at application versions.

1. Application Versions: You may run into 'hiccups' in either Excel, or in Word when Excel is in control of it, such as in your code. This is likely to be minor, but as you are writing in 2007, you may inadvertantly include arguments that didn't exist in a prior version. For instance (and by memory, so don't take too literally), when using the Find method in Excel, an arg available in 2003 (maybe 2002) is 'SearchFormat '. But if I try that in 2000, kaboom... Of course the same thing can hold true for Word coding written in '07 and run in '03 or earlier. Depending on how much code you'll be writing, and how convenient it is to use 2003, you may find it easier to write in the earlier version, as there's less changes that will go kaboom in a latter version than the other way.
2. Early vs Late: Again IMHO, the biggest draw back to early is that, lets say you write code in 2003 (in excel) and set a reference to Word's 2003 library. When you later run the code in 2007, no problem, as Excel recognizes the old library reference and updates the project's reference to the later library. Then you take the file and run in 2003 again, and suddenly she balks. This is of course because 2003 cannot recognize/deal with the later library, as the reference wasn't "invented" yet.

Mark

 

[prawer]

Looks like I'm running into version problems. My boss ran it in 2003 and got the following error message: "“Compile Error. Cannot find project or library.”

Is that a recognizable error, and one that you can see as having been caused by refernces to unknown packages?

I guess it may also be relevant to note that he ran the macro in a remote session (via Citrix) with a 2003 installation. Does anyone think that might have some effect on the code's ability to run?