VBA Public Sub procedure not executing, Variables pulling from Active Directory

[aef1]

Hi All,

Just created some macros in Word....They open up Word templates that
are located on a server. Each office has its own server with the same
filepath....The only difference is the server name.

Template location

Brighton office = \\br-fs-01\vbatemplates$\Templatename.dot
Oxford office = \\ox-fs-01\vbatemplates$\Templatename.dot

etc

The code to open up the Templates is contained in the Sub procedures. I
have tried creating a Public Sub procedure and calling the code in each
Sub routine, but it is not working...the code doesn't execute.


But it does work if I write the code out in each Sub procedure. The
code looks to Active Directory to find the user location and opens the
local server copy of the template:


Public Sub OpenLocalCopyOfTemplate()

Set objAdSys = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://servername.com/" & objAdSys.UserName)
objUser.GetInfoEx Array("property1", "Property2"), 0

If objUser.Get("PhysicalDeliveryOfficeName") = "Brighton" Then
strFileServer = "\\br-fs-01"
End If

If objUser.Get("PhysicalDeliveryOfficeName") = "Oxford" Then
strFileServer = "\\ox-fs-01"
End If

If objUser.Get("PhysicalDeliveryOfficeName") = "London" Then
strFileServer = "\\lo-fs-01"
End If

End Sub

Does anyone know where the code should be written or how to filter the
strFileServer variable through to the Sub procedures?

Many TIA,

Net

 

[Jay Freedman]

It looks like what you want to do is change your Sub to a Function that
returns a string containing the server name. Try this

Public Function OfficeServer() As String
Dim objAdSys As Object
Dim objUser As Object
Dim strFileServer As String

' put all of the code from your Sub here, except the
' lines Public Sub OpenLocalCopyOfTemplate()
' and End Sub

OfficeServer = strFileServer
End Function

Then, in the other subroutines where you actually construct the full
template name, call this function:

Dim strTemplateName As String
Dim myDoc As Document
strTemplateName = OfficeServer & "\vbatemplates$\Templatename.dot"
Set myDoc = Documents.Add(Template:=strTemplateName)

Here's some reading for you:
http://www.word.mvps.org/FAQs/MacrosVBA/ProcArguments.htm
http://www.word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[aef1]

Thank you so much Jay. It worked really well and I have now used the
same principle with other code in the macros.

Thanks also for the reading. Very useful!

Net

 

[Jay Freedman]

I'm glad it worked out. Thanks for letting us know.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.