+ Reply to Thread
Results 1 to 14 of 14

Excel 2007 : Macro to open file for last available working day

  1. #1
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Macro to open file for last available working day

    Morning
    Little conundrum for some lovely helpful person to help me work out. Thanks in advance by the way.
    I'm currently using the below macro portion to open a .txt file for a previous day's date and delimit text it to columns in Excel, the Xs are in place of the file name, and the date is in the file name too

    Please Login or Register  to view this content.
    As you can see from the code, this is to open a file that is dated for the immediate day before the day the macro is run.

    However, I am coming up with a problem that requires us to constantly change the file name of the file we are running.
    For instance, if we run the macro on a monday, the macro obviously looks for a file dated for the Sunday, when realistically, it should be looking for a file dated for friday. And the same for if it's a bank holiday monday. We run on the tuesday, but the macro looks for a file dated monday, when it would naturally still be the friday one, and it gets even more confusing when you factor in running the file on the first of the month, the macro looks for a file dated 0.

    What I want to know, is if it's possible to get the macro to look for the next/previous actual available date/month/year, rather than the way I currently have it, and if so, how would I go about it?

    Thanks again in advance for any help regarding this.
    Last edited by Nikeyg; 10-11-2011 at 08:18 AM.

  2. #2
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    Sorry for the bump, but does anyone have an idea on how I can get round this?

    I could possibly work it to run the last modified file. As long as it contains 'PFEM-CASH-EOD'.

    EDIT: Actually, that might not work, as I need to use the file for copy and pasting afterwards.

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    You could do it using the WORKDAY function, but why not just keep going back a day until you find an existing file?

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    I did think about doing that, but cannot remember the coding for it to work.
    The macro would need to look for Date -1 first, then if it can't find it try Date-3 and then finally Date-4, but that wouldn't solve the issue with regards to the months changing.

    How would the workday function work within the macro, especially with regards to copying and pasting?

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    The code above should work fine, followed by:

    Please Login or Register  to view this content.

    Should sort out all issues with weekends, bank holidays, etc.

  6. #6
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    Thanks for the help Andrew

    That works brilliantly for opening the sheet and delimiting it.
    But I'm trying to get the macro to select the file at 3 points, 2 to copy particular sets of data, and the last time to close.

    Changing this:
    Please Login or Register  to view this content.
    To this:
    Please Login or Register  to view this content.
    Unfortunately didn't work. sFullName is an invalid qualifier.
    Sorry for being so demanding, but can you please advise how I can get around this?

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    In your code define a workbook object ...

    Please Login or Register  to view this content.

    When you open your text file assign it to the defined workbook...

    Please Login or Register  to view this content.

    Now, when you want to work with (or activate, if you must) that file just...

    Please Login or Register  to view this content.

    However, in VBA it's rarely necessary to activate an object before working with it, you could just (for example)

    Please Login or Register  to view this content.
    Last edited by Andrew-R; 10-11-2011 at 06:55 AM. Reason: Destination:=, not Destination=

  8. #8
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    Hmm

    Doesn't like this bit
    Please Login or Register  to view this content.
    Says it's expecting an end statement where 'Filename' is.

  9. #9
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    Sorry - you need brackets around that statement now.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    No need to say sorry.
    It should be me apologising for not being able to work this stuff out for myself.
    I've chucked the brackets in, but now it's erroring with compile error: Expected Function or variable and highlighting the '.OpenText'

    Really really sorry about this.
    The help is really appreciated.

  11. #11
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    I'm actually going to have to test some of this code I'm giving you, aren't I?

    Bear with me a second...

  12. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    ...nope, no idea why that doesn't work with OpenText. It should, I'm sure.

    Anyway try this instead:

    Please Login or Register  to view this content.

    Not very neat, but it does seem to work.

  13. #13
    Forum Contributor
    Join Date
    08-04-2010
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    110

    Re: Macro to open file for last available working day

    Andrew, you are brilliant.
    If I could 'Rep' all your posts here I would.

    I have still got so much to learn.

  14. #14
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to open file for last available working day

    Phew, glad we got there in the end, sorry it took so long.

    I'm not brilliant - just somebody who finds out every day here that I still have so much to learn as well

+ 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