+ Reply to Thread
Results 1 to 2 of 2

Open other excel files with specific text in the file name

  1. #1
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Open other excel files with specific text in the file name

    Good Afternoon,

    I have created a "data warehouse" where data from various sources is dumped into an excel workbook (each workbook has 31 sheets and is names after the month and year it relates to for example march 2013.xls) I run various reports that cross reference data from each of these warehouse. for example I have a sales workbook with sheets Feb through to Jan pulling the data from the "warehouse". The problem I am finding is that some of the lookups take a bit of time to run and some reports can take upto 5 minutes to open and refresh the formula's therein.

    What I'd like to do is have each report open the data warehouse workbooks when it opens so that the lookups and formula's update faster and then close them when calculations are complete. As I'm rather new to VBA and learning as I go, I'm trying to use the code below that i use in another report as a base to start on.

    Please Login or Register  to view this content.
    Ideally it would know that the current year is 2013 so would open Feb 2013.xls through to Dec 2013.xls as well as Jan 2014.xls and then close them all after the workbooks have recalculated.

  2. #2
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Open other excel files with specific text in the file name

    Hi,

    I've managed to cobble together the code below, but I'm looking for ways to speed it up as it's taking around 2 minutes for the process to complete and the workbook to fully open.

    Please Login or Register  to view this content.
    Using this function
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1