Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Full File path (2003)

    I have created a template which has the following 2 formulas in one of the sheets:

    =INDEX('Project Summary'!$A$1:$IN$2500,VALUE($X$1),VALUE(X1))
    =SUMIF('Project Summary'!$A:$A,$A2,INDIRECT("'Project Summary'!"&M$3&":"&M$3))

    I the use the following code to open the template and save it as the monthly consolidation file:
    strSaveConsolidatedFileAddress = Mid(ThisWorkbook.FullName, 1, InStr(1, ThisWorkbook.FullName, "Recharges") + 9) & "Consolidated Files.xls"
    Workbooks.Add Template:= _
    Left(ThisWorkbook.FullName, InStr(ThisWorkbook.FullName, "Fin2") - 1) & _
    "Alex's TemplatesConsolidation File.xlt"
    ActiveWorkbook.SaveAs (strSaveConsolidatedFileAddress)

    When I do the task manually the the index and sumif formulas remain linked within the same workbook and look exactly as above.
    When I run the macro, VBA translates the index and sumif formulas to include the full file path to the destination directory and saved file name, which, even though the path and name is correct causes all the formulas to revert to #Val

    How can I overcome this?

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

    Re: Full File path (2003)

    I'm afraid I can't reproduce the error in Excel 2002. I created a small template with similar formulas, and ran a macro that creates a new workbook from the template and saves it. The formulas were left unchanged.
    Perhaps someone with Excel 2003 can test this.
    It might help if you could post (stripped down and zipped copies of) the template and the workbook with the code.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full File path (2003)

    Hans

    A copy of the template is attached.
    If you need to recreate the file structure mine is as follows:

    Templates are held in:
    U:AdminManagement ServicesFinanceAlex's Templates

    Consolidations are performed in:
    U:AdminManagement ServicesFinanceFin2005Man AcctsPeriod 08 - MayRecharges

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full File path (2003)

    When I opened the version that is sitting on my drive, to was clean and linked internally.

    Sometimes, even though I save a clean copy, it is changed during the save process. Other times it saves and then re-opens as a clean copy but then changes back to including the full file path when the macros is run. I have failed so far to work out what the trigger is that changes the file path to a full listing.

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

    Re: Full File path (2003)

    The template you posted (Consolidation File.xlt) already has formulas that refer to an external file (Consolidation Files.xls). These external references remain, of course, if you create a new workbook from the template and save it.

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

    Re: Full File path (2003)

    If you copy a worksheet from one workbook to another, it may cause external references. Say that you have a workbook Book1 with sheets Sheet1 and Sheet2. Some formulas on Sheet2 refer to cells on Sheet1.

    If you select Sheet1 and Sheet2 in Book1, then copy them to another workbook Book2, the formulas in the copied Sheet2 will refer to the copied Sheet1 in Book2, in other words the references are internal to Book2.

    But if you select only Sheet2 and copy it to Book2, the formulas will refer to the original Sheet1 in Book1, even if you then also copy Sheet1 from Book1 to Book2. In other words, you have created an external reference by copying the sheets separately.

    Could this explain the behavior you see?

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full File path (2003)

    Hans

    That was not the problem. However I have come across one anomoly. I have 2 copies of the template, one on my D drive for using at home and one on the U drive for work. All he code to open files is referenced relative to the root directory so that I can run the files at home as well as at work.

    When I was working in the U drive and accidentally opened the template on the D drive the anomoly occurred and the file opened as "consolidation File 1".
    When I am working in the U drive and I open the copy in the U drive the file opens properly.

    Perhaps that was the problem?

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

    Re: Full File path (2003)

    All I know is that the template you attached definitely contains links to an external workbook.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Full File path (2003)

    Many of the names are linked to an external file (26/89), many reference invalid sheets (45/89) and some are just errors (14/89).

    Most of the formulas in rows 1 and 2 in the 'Cost summary' Sheet are external links.

    Steve

Posting Permissions

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