Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am in word 2007 and need to modify a global word template to look to a user's appdata\roaming\microsoft\templates folder for company specific templates.
    I have the following code defining the path
    Public Const strForms As String = "c:\users\%username%\appdata\roaming\microsoft\Tem plates\companyforms\"
    I've tried to use Environ$("Appdata") & "\...companyfirms\"
    %appdata% & "\...companyfirms\"

    Then, the ribbon points to that location to pull the templates
    Sub OpenLtr(control As IRibbonControl)
    Documents.Add Template:=strForms & "timesheet.dotx", _
    NewTemplate:=False, DocumentType:=0
    frmTime Show
    Unload frmTime
    End Sub

    I just can't figure out how to define the variable for appdata regardless of the user name. The company has an outside tech firm managing their network and insists that I can't point to c:\prog files\msoffice\office12\templates.... so that is not an option.

    Help anyone?
    Thx

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Maybe...

    Function GetSpecialFolders(ByRef vFolder As Variant) As String
    Dim WSHShell As Object
    Set WSHShell = CreateObject("WScript.Shell")
    GetSpecialFolders = WSHShell.SpecialFolders(vFolder)
    Set WSHShell = Nothing
    End Function

    Sub WhatWhereHow()
    MsgBox GetSpecialFolders("Templates")
    'also try "Programs"
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    commercial add-in

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    TechTrain,

    Am I missing something? {Wouldn't be the first time} Why are you trying to point to the user's C: drive for company specific templates? They should be on a server in a single place, e.g. one copy for all, and Word should be installed to point the "Workgroup Templates" to that location.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    RG: Putting the company templates on a consistent location on each user's C: drive can be a valid way to go, as well. One benefit of doing it that way is that users will continually have access to the company templates, whether they are currently working on or off the network.

    But either way, the template path should be consistent for all users.

    techtrain: Besides the complication of having to return an environmental variable in order to determine the correct template path, I think putting the templates into a user-specific location means that the documents will lose their connection to their originating templates, when they are passed among users.

    Do you observe this problem at your company? - that is, if User A creates a new document and then User B goes to work in it, does User B lose access to things like macros or Ribbon customizations, that are associated with the originating template?
    (based on your code sample, it looks like you have no code in the timesheet template, but instead are keeping code in a global template, so maybe the issue is moot...)

    Gary

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Quote Originally Posted by RetiredGeek View Post
    Am I missing something? {Wouldn't be the first time}
    Gary,

    I warned you!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #6
    Star Lounger
    Join Date
    May 2003
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The code for all templates lives in a company global template that is in the startup. There are a few reasons I can't point the workgroup templates to a network path
    1) Users move with their laptops and there are network connectivity issues (don't ask about that!) so I want them to have local access to the templates at all times
    2) I can't put them in C:\prog file\ms off\office 12\templates because users don't have admin right on their machines so I can't script it to place the company templates to that folder without visiting all 100 users
    3) i can't edit workgroup templates on the fly if they are pulled from network. Presently, the templates are on a network share k:OfficeTemplates. If I need to modify a template and a user has it attached to an open document, I can't access it right away.

    Soooo, I have a login script that copies all office templates to the local machine. Additionally, it copies a good copy of the officemaster.dotm to the startup.

    I thought that using Environ$("APPDATA") & "\roaming\microsoft\Templates\OfficeTemplates"
    would work....but it doesn't!

    Thanks for any more insight!

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Quote Originally Posted by techtrain View Post
    I thought that using Environ$("APPDATA") & "\roaming\microsoft\Templates\OfficeTemplates"
    would work....but it doesn't!
    Techtrain,

    I think you problem is with the extra Roaming - you just want to append "\Microsoft\Templates\Office Templates"!
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    Star Lounger
    Join Date
    May 2003
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG:
    Where is my extra roaming? It's monday morning. I need a little more help :-)
    I don't want it to go to appdata\local, I need to specify appdata\roaming.
    thx

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Techtrain,

    Look at the graphic again. Environ$("APPDATA") return value includes the \Roaming. Your code then appends a string starting with \Roaming so you wind up with C:\Users\[USERID]\AppData\Roaming\roaming\microsoft\Templates\Offic eTemplates.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #10
    Star Lounger
    Join Date
    May 2003
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG
    I think I need one of those frosty mugs with your emoticon!
    This is the old code
    Public Const strForms As String = "K:\OfficeTemplates\"

    This is what I can't get:
    Public Const strForms As String = "Environ$(%APPDATA%)" & "\Microsoft\Templates\OfficeTemplates\"

    If i use ("Appdata") I get an unexpected end of statement error. This is the only iteration that does not give me a compile error

    Thanks for your patience

  12. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by techtrain View Post
    Code:
    Public Const strForms As String = "Environ$(%APPDATA%)" & "\Microsoft\Templates\OfficeTemplates\"
    I think you don't want quotes around Environ$(%APPDATA%) or Environ$("APPDATA") since that actually is a function you need to run rather than static text. Hard to tell from the context...

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Techtrain,

    When you use Environ$ you need to enclose the variable name in " " not % %.
    Her you go have at it!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  14. #13
    Star Lounger
    Join Date
    May 2003
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can I not use the environ variable in a constant expression? I'm ready to give up!
    Thanks

  15. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by techtrain View Post
    Can I not use the environ variable in a constant expression? I'm ready to give up!
    Hmmm, I think that might be true, that the value assigned to a constant must be fixed at the time you write the code. Hence the name constant.

    Use a variable instead?

    Code:
    Dim strForms as String
    strForms = ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •