+ Reply to Thread
Results 1 to 6 of 6

Opening workbooks based on Date in the file's name

  1. #1
    Registered User
    Join Date
    04-18-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Opening workbooks based on Date in the file's name

    Hello everyone,

    I'm trying to develop a macro to amalgamate some data on currency exchange rates that are stored in different files with the dates saved in the file's name.

    The code of the macro I recorded when opening an example of such a workbook is:

    Please Login or Register  to view this content.
    Where: E:\ is the directory the files I want to amalgamate are stored
    "EURUSD Curncy" is the currency pair ie. the Euro against US Dollar
    "08012009" is the date at which the data starts ie. 8th Jan 2009

    What I'd like my macro to do is

    1. Open the file in E: that contain the text "EURUSD Curncy" that contains the oldest data start date

    2. Perform some operation on it (I've got this bit covered )

    3. Close that file

    4. Open the next file in chronological order ie. it should open "EURUSD Curncy 08012009.xls" before "EURUSD Curncy 09022009"

    I hope I've explained this properly enough to give you a foothold into this predicament. If you have any questions or queries, please don't hesitate to post me back.

    Many Thanks in advance for your time,

    Twills.
    Last edited by twills; 05-31-2009 at 10:52 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Opening workbooks based on Date in the file's name

    This is a macro developed recently here for another OP that does basically the same thing. I adjusted the ThePath to the one you wanted, and I also added a POPUP that asks for a cutoff date of some kind, else it will just keep going back in time forever checking dates. Enter an "earliest value" when it asks.

    You can see the spot where you do your macro code:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Opening workbooks based on Date in the file's name

    twills

    Welcome to Exceltip forum

    Please take a couple of minutes and read ALL theForum Rules then wrap your VBA code (Rule 3)
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  4. #4
    Registered User
    Join Date
    04-18-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Opening workbooks based on Date in the file's name

    JBeaucaire,

    Many thanks for your incredibly speedy response. The macro appears to do exactly what I want.
    This will no doubt save me an enormous amount of time faffing about.

    The only problem I have is that I when I run it, I get a Runtime Error '6': Overflow

    When I debug, the yellow line comes up on the following code line:

    Please Login or Register  to view this content.


    Apologies to you and forum members for not wrapping my code in the earlier post- enthusiasm for the task sometimes means protocol takes the back seat - this won't happen again!

    Once again, Many Thanks for your time.

    Twills
    Last edited by twills; 05-31-2009 at 05:07 AM.

  5. #5
    Registered User
    Join Date
    04-18-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Opening workbooks based on Date in the file's name

    JBeaucaire,

    Not to worry, all resolved now!

    Once again, many thanks for your help.

    Best Regards,
    Tim

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Opening workbooks based on Date in the file's name

    Twills, glad to help.

    Remember, a forum moderator asked you to fix the FIRST post and add the code tags to the code in that post as per the forum rules ... be sure to do that, OK?
    ===========

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].




    (Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)

+ 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