Results 1 to 2 of 2

Thread: get values from closed workbook(s)

  1. #1

    get values from closed workbook(s)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I run a report on daily basis that generates a new excel workbook with the name report2013-01-28.xls (obviously the date-part of the file name is changed daily).

    What I'm trying to do now is to create a "master file" to get a better overview in change of trends etc. The master file will be built in a way so that each column represents 1 day. The header of each column will be the current date (for example 2013-01-28).

    I need a macro that, when run, extracts data from certain cells in the file reportXXXX-XX-XX.xls (where is XXXX-XX-XX is the name in the top row). Preferably the source-file should not have to be open.

    I have attached two example-files to show you the idea.

    For example, when I click the macro-button in "masterfile.xls" I would like the macro to understand that the last unpopulated colum is B, and in B1 the value is 2013-01-28, and therefore it would access the file report2013-01-28, and for example, extract the value from the cell B23 and put it in B8 in masterfile.xls.

    Is this at all possible? Currently I'm doing this manually, and needless to say it's a bit time consuming.

    Any help would be greatly appreciated!

    Anton
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I had a similar thing a few years back and found something on the internet that helped, unfortunately I don't have a link to it or know who deserves credit but it was workable for me.

    The files have to be opened to extract data from them but this can be done behind the scene with a minimum of distraction.

    Have adapted what I used for your situation in the renamed xls. You need to go into the macro and set up the path to your report files for this macro to work.

    Hope this helps
    NoS
    Attached Files Attached Files

Posting Permissions

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