Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Pittsburgh
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro (Excel 2002)

    In an Excel Workbook, I have macros to open other workbooks, all located in the same directory. These files are backed up and reinstalled in other computers, in the same directory under "My Documents".....the problem is that the file being opened yyyy is now located under xxxx (the user name), as in the following Cocuments and SettingsxxxxMy Documentsyyyyy
    So I can copy/backup to another computer all the workbooks ... but must now rewrite the macros to open yyyy file as the directory has changed. Can I substitute something for xxxx in the macro directory to allow the file to be opened.....

    stumped

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (Excel 2002)

    The expression

    CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

    returns the complete path of the My Documents folder of the user running the code. So you can use something like this:

    Dim strPath As String
    Dim strFile As String
    strPath = CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")
    ...
    strFile = strPath & "" & ...

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Pittsburgh
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel 2002)

    Thanks Hans,

    I used the following (From your Suggestion) <img src=/S/bash.gif border=0 alt=bash width=35 height=39> :

    Dim strPath As String
    Dim strFile As String
    strPath = CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")
    strFile = strPath & "" & "vvvvvvv"
    Workbooks.Open Filename:=strFile

    This worked fine, you saved me a lot of time and trials...

    Will

Posting Permissions

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