Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro Help: Relative Path

    Hello. I have a template which calls in another file using a macro running "Insert>Object>Text fromFile../"

    The pathway is absolute, originating from C: my user name, etc. I need a relative pathway. See code below. please help.

    Sub Insert_Picture_Table()
    '
    ' Insert_Picture_Table Macro
    '
    '
    ChangeFileOpenDirectory _
    "C:\Users\joseph.leroy\Desktop\CompRigInspForm\Res ource\"
    Selection.InsertFile FileName:="picTable2.dotm", Range:="", _
    ConfirmConversions:=False, Link:=False, Attachment:=False
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Joey,

    Welcome to the lounge.

    This should do the trick.

    Code:
    Sub Insert_Picture_Table()
    '
    ' Insert_Picture_Table Macro
    '
    '
      Dim zPath as string
    
      zPath = Environ("UserProfile") 'Get  C:\Users\username
    
    ChangeFileOpenDirectory _
     zPath & "\Desktop\CompRigInspForm\Resource\"
    Selection.InsertFile FileName:="picTable2.dotm", Range:="", _
    ConfirmConversions:=False, Link:=False, Attachment:=False
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JoeyL (2011-07-20)

  4. #3
    New Lounger
    Join Date
    Jul 2011
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you RetiredGeek, that worked great. Is there a way to do it so that the location of the template could be anywhere, so long as the Resources folder is a direct child folder?

    For example "/../Resources/picTable2.dotm"

    I intend to create a zip file, and just instruct users to extract the contents. I dont think the desktop is the ideal place, and would like if they could just extract to whereever they like, so long as they keep the folder structure of the zip file intact.

    Thanks again!

    -Joey

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Joey,

    Why not have the user extract the folder to their documents folder then you can just change the code to read zPath & "\Documents\..."

    Sorry but w/o user actions or an install program I don't know how to let them put the files anywhere.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JoeyL (2011-07-20)

  7. #5
    New Lounger
    Join Date
    Jul 2011
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Okay RetiredGeek, that is a good solution. Thank you again for the assistance! You rock!

Posting Permissions

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