Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable File Names (Excel 97-SR2)

    I'm trying to build a summary spreadsheet model that needs to look at a variety of spreadsheets. These spreadsheets will always be in the same location. The following works if you leave Excel to build the Links:-

    ='CATAExcel[Test File 2.xls]Sheet1'!$C$3

    I'm looking for someway of changing the name of the spreadsheet(book) and possibly the worksheet by referencing other cells. E.G:

    =ADDRESS(3,3,,,"[CATAExcelTest File 2]Sheet1")

    Which produces the correct result:

    '[CATAExcelTest File 2]Sheet1'!$C$3

    Which doesn't seem much use since I can't then say something like:

    =INDIRECT('[CATAExcelTest File 2]Sheet1'!$C$3)

    Am I missing the point, or do I finally need to admit defeat and use VBA? If the latter, can anyone point me to a good intro on file handling in VBA. (Done lots of VB and Wordbasic programming a long time ago..)

    Regards
    Peter

  2. #2
    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: Variable File Names (Excel 97-SR2)

    Indirect will work.
    BUT,
    The PROBLEM is that using INDIRECT to get info from another workbook REQUIRES that the other workbook is OPEN. Typing it directly does NOT require this.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable File Names (Excel 97-SR2)

    Hi Steve

    Ahh.... Forgot about the old INDIRECT File Open gotcha.... The design I am envisaging will need to access upto say 100 spreadsheets. OK... Is there a good primer on file handling within Excel 97 VBA?

    Regards
    Peter

  4. #4
    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: Variable File Names (Excel 97-SR2)

    Could you be more specific of what your goals are?

    If you are trying to access 100 different workbooks, I assume there is only particular info from each you might want and that you probably don't need to be able to access ANY of the info.

    You could set up a summary/extract sheet in you workbook, which makes a table of the filename and links all the potential cells of interest (without using INDIRECT - hard coded). ONce you have links in the spreadsheet to all the possible values you would need, your workbook formulas could use MATCH, VLOOKUP, HLOOKUP, INDEX, etc to pull the appropriate data from this table.

    If you want to work with files in VB, I need more specifics to be able to answer what you want to do.
    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable File Names (Excel 97-SR2)

    Hi Steve

    I don't know what I want to do yet... I'm in creative/think mode. What I imagine is someone working in a master spreadsheet being able to enter something like a Project ID in a cell and that Project ID being a part of/the whole file name for an underlying spreadsheet. The master spreadsheet then needs to go interogate the underlying spreadsheet to extract some data.

    The underlying spreadsheet(s) will be Project reporting templates. As such, these template files will be distributed to whichever project/programme manager is overseeing individual projects. Weekly a file for each project/programme that is being worked on will be e-mailed to someone who has the master spreadsheet. This master will have details for all current programmes/projects, but also needs to have the ability to add/remove on the fly.

    The master spreadsheet will be summarising and in some instances consolidating information across all programmes/projects; Start/End Dates, RAG Status, Key Milestones etc. Given that adding removing projects/programmes has to be dynamic I don't really want to hard code filenames.

    Does that help?

    Regards
    Peter

  6. #6
    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: Variable File Names (Excel 97-SR2)

    Sounds like formulas are out.

    Pulldowns to select files could call macro which puts the appropriate "Link" into a cell (or cells) to get the info from the appropriate file. Since you would enter the code via macro (instead of a formula, using INDIRECT) you would NOT have to open the file.

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Variable File Names (Excel 97-SR2)

    Sounds like one might be able to get away with a macro that simply reads the external workbook name in, say, A1 and goes through each of the formulae on the worksheet replacing an existing (default) workbook reference with the one in A1. The same sort of thing could be done for different sheet references within the source workbooks. Of course, you don't really need a macro for this - a simple search/replace would probably do fine too.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable File Names (Excel 97-SR2)

    I think I'll go via the file handling route.. Will give the flexibility I need.

    Anyone got any good threads for file handling in Excel VBA? (Have done lots of programming over the years, just need to understand basic approaches in Excel.)

    Regards
    Peter

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable File Names (Excel 97-SR2)

    Just search the lounge on Workbooks.Open and you should turn up many threads that will help. Here is just one.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable File Names (Excel 97-SR2)

    Thanks Legare!

Posting Permissions

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