Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding location of template file (excel 2000)

    Hi All,

    I have a problem here where I have an application that uses Excel to provide formatted output of data. It uses an xlt file with VBA to process a text file containing the data. The xlt file is stored within our application and downloaded to the local disk along with the datafile. My problem is that I need to be able to find out where the xlt file was run from so I can find the location of the data file. The location varies each time a report is requested, and this behaviour is beyond our control. Excel is launched using the Windows association for xlt files.

    Is there an Excel or System VBA commmand/routine that will allow me to find out where the xlt that was run to create the current instance of Excel was located?

    Cheers,
    Jim

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

    Re: finding location of template file (excel 2000)

    ThisWorkbook.FullName will give you the path and file name of the file in which the currently running code is stored.
    ThisWorkbook.Path will give you just the path.

    (Contrastingly, ActiveWorkbook.FullName and ActiveWorkbook.Path will give the name and path of the workbook that is active when the code is run.)

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding location of template file (excel 2000)

    Hi Hans,

    Thanks for the reply.

    I have already tried this to no avail, ThisWorkbook.FullName lists the xlt filename (without extension) and .Path is blank.
    I think this is because it is based on an xlt template rather than an xls workbook, and opened using the Windows association rather than from within Excel itself. The only way I could get a path was to save the workbook, thus losing the original location containing the datafile.

    Jim

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

    Re: finding location of template file (excel 2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Sorry, I was confusing Excel and Word - in Word, code remains in the template a document is based on; in Excel, code is copied to the new workbook, and there is no connection between the workbook and the template it was made from...

    I never put code into Excel templates myself, since I don't want each workbook created from it to contain code. I put code into an add-in; there the ThisWorkbook.Path works.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: finding location of template file (excel 2000)

    Jim, in Excel there is also:

    Application.TemplatesPath
    Application.NetworkTemplatesPath

    but if the Template is not stored in one of those, you may be out of luck.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding location of template file (excel 2000)

    Hi John,

    The directory containing the downloaded xlt and datafile varies and cannot be guaranteed each time. In fact it is very unlikely to be the same.

    From the sound of it using an xlt is a non starter if Excel makes a copy of it and breaks the link to the original code.

    The only reason I used an xlt was that a previous incarnation used Word to format the data and a dot file was used, which worked fine. I guess it is just another inconsistency between the way the different Office apps work.

    I can use an xls instead of a template, and that retains its path, so work continues...

    Thanks for the help,
    Jim

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: finding location of template file (excel 2000)

    Using an XLS is an obvious solution, or you could force the XLT and text file to be obtained only from the Excel Application.NetworkTemplatesPath, which ordinarily has to be specified by Admin at time of Application installation.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding location of template file (excel 2000)

    Thanks John,

    The application that decides where to download the files is not under my control, and uses the concept of session directories - with good reason ordinarily. The application is a server and the clients connect via Citrix so the concept of sessions is paramount to keep users data apart as multiple users could be requesting reports with different datafiles.

    Initially I had a problem with xls where the working dir was always 'My Documents', but found the setting in Excel to set this, and then just blanked it. Now the working dir follows the document, which is what I need.

    Cheers,
    Jim

Posting Permissions

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