Setting NetworkTemplatesPath programmatically in Excel 2000 VBA

L

Ladislav Ligart

I just spent hours searching this group on this issue and came up with
a way of setting it programmatically in Excel VBA, and am posting it
here in hopes no one else has to spend hours searching. :)

ISSUE:
Excel 2000's NetworkTemplatesPath property is Read-only and you can't
set it in VBA.

WORKAROUND:
You can either hack the Registry (so I'm told), or simply go into Word
and choose Tools / Options / File Locations and set "Workgroup
templates" path. Excel will then use this value for its "Network
Templates Path"

SOLUTION:
Programmatically access Word's object model in Excel and set Word's
"Workgroup templates" path. This works for for Office 2000, not sure
about the rest.

CODE:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top