Excel's VBA <> Word's VBA?

  • Thread starter Thread starter Sonny Maou
  • Start date Start date
S

Sonny Maou

I program using VBA for Word. I've imported some forms and code from a
Word project into Excel, but there are lots of issues. Is there a web
page I can go to to help distinguish the many differences between VBA
for Excel and VBA for Word?

Thanks.
 
Sonny,

The actual VBA used by Excel and Word is the same. The difference
lies in the object models of the Excel and Word applications.
Aside from books about Word and Excel programming, you can use
the Object Browser to see the object models.

Perhaps you could start by asking specific questions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip said:
Sonny,

The actual VBA used by Excel and Word is the same. The difference
lies in the object models of the Excel and Word applications.
Aside from books about Word and Excel programming, you can use
the Object Browser to see the object models.

Perhaps you could start by asking specific questions.
Click to expand...

Thanks, Chip. I wanted to RTFM before I got into specific questions...
 
Chip said:
Perhaps you could start by asking specific questions.
Click to expand...

Chip, the following code works in Word. Why doesn't it work in Excel? If
they are the "same VBA," why doesn't the constant wdUserTemplatesPath
work in Excel like it does in Word? How would the code below need to be
written to work in Excel?

Public Function dirPath(x)
Dim temp
If x = "user" Then
temp = Options.DefaultFilePath(wdUserTemplatesPath)
Else
temp = Options.DefaultFilePath(wdWorkgroupTemplatesPath)
End If
dirPath = temp
End Function

Thanks! :)
 
Joe said:
Hi Sonny,

First, the constants defined in the Word library are not defined in the Excel library and vice versa.
Click to expand...

Well, that blows, since those same constants would seem to be shareable
at least within Office apps.
Second, Options.DefaultFilePath has no meaning in the Excel Object Model. What are you trying to do?
Click to expand...

Firstly, I'm trying to get the "default file path" for user templates
and workgroup templates. Now that I look around Excel's Tools>Options
dialog box, I see that there is no place to specify these locations. :/

Ultimately, I'm trying to convert some document management code that has
been developed for Word documents into worksheet management code that
works in Excel... looks like it's going to be a long haul. >:(

Thanks for your help! :)
 
Sonny: For the same reason a Ford key won't work in a Chevy. While the
engines both burn gas, the "keys" to make them run are different. So, too,
Excel and Word are different in what they do and how they do it, and the
commands needed to instruct the different programs to function. The prefix
"wd" is a dead giveaway that the instruction or parameter is specific to
Word.

Ed
 
Ed said:
Sonny: For the same reason a Ford key won't work in a Chevy.
Click to expand...

Well, a Ford engine will work in a Chevy, with a little mod... maybe
that's a better analogy. or maybe not. :/
engines both burn gas, the "keys" to make them run are different. So, too,
Excel and Word are different in what they do and how they do it, and the
commands needed to instruct the different programs to function. The prefix
"wd" is a dead giveaway that the instruction or parameter is specific to
Word.
Click to expand...

Yeah, I was hopin' for some real live compatibility between two
Microsoft Office apps. heh. Some may say I'm a dreamer... :)

Of course, I see the need for specific and necessarily incompatible
objects to represent each app's "docs"... I'm just bein' a whiner for a
moment. Thank you. :P
 
You can certainly automate on Office application from another.
They work together quite nicely in that respect. In VBA, go to
the Tools menu, choose References, and select the Microsoft Word
object library.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
For excel:

MsgBox Application.DefaultFilePath
MsgBox Application.NetworkTemplatesPath
MsgBox Application.TemplatesPath

And the only place I've seen where the Network templates path can be set by the
user is within word--but excel respects that setting.
 
Dave said:
For excel:

MsgBox Application.DefaultFilePath
MsgBox Application.NetworkTemplatesPath
MsgBox Application.TemplatesPath

And the only place I've seen where the Network templates path can be set by the
user is within word--but excel respects that setting.
Click to expand...

Thank you, Dave... my first step toward cross-application compatibility! :)
 
I just spent hours searching this group and came up with a way of
setting it programmatically in Excel VBA, and am posting it here in
all the threads I can find so no one else has to spend hours
searching. Works for Office 2000, not sure about the rest. Here it is.

Sub SetNetworkTemplatesPath()
'********************************************************************
' Purpose:
' Set Excel's NetworkTemplatesPath by way of setting
' Word's WorkgroupTemplatesPath
'********************************************************************

'// Must check Tools / References / Microsoft Word 9.0 Object Library
Dim oWord As Object
Set oWord = New Word.Application

'// see what Excel has to begin with
MsgBox ("Excel's Network Path Was: " & _
vbCrLf & Application.NetworkTemplatesPath)

'// set Word's path to your LAN path
oWord.Options.DefaultFilePath(wdWorkgroupTemplatesPath) = _
"W:\Office2K\WorkgroupTemplates"

'// Excel follows suit!
MsgBox ("Excel's Network Path Is: " & _
vbCrLf & Application.NetworkTemplatesPath)

End Sub
 
Back
Top