I have file in a directory called J:\Sales called 090511 SUMMARY.xls.
I want a macro to do two things -
1. Find last weeks file. So search J:\Sales for all files ending in SUMMARY.xls and open the one with the most recent date in front of the SUMMARY.xls.
2. Save the file as last weeks file plus seven days i.e (090511 + 7 days = 160511) DDMMYY format + SUMMARY.xls
So i end up with a new file called 160511 SUMMARY.xls
Could someone help please?
Last edited by timbo1957; 05-17-2011 at 05:50 AM.
You'd better use the ISO-standards: yyyymmdd or yyyyww
I have looked that up but how do I put it into a macro?
or
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by snb; 05-16-2011 at 10:05 AM.
Sorry too advanced for me. I need to see it within a sub routine for me to be able to understand how it is going to work.
Hello timbo1957,
This macro will look the file with the latest date in the directory, based on the naming specified, open the workbook, and save a copy of the workbook with seven days added to the date. The date format is "ddmmyy". Let me know you have any problems or need further assistance.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi Leith,
Thanks for the macro. It isn't picking up the filename this part of the code is blank when I step through it. So the rest of the macro isn't running.
When I step through it and hover over FileName in the above line of code the Filename = ""Please Login or Register to view this content.
The only exisiting file in the directory is 090511 SUMMARY.xls.
I don't know if this is anything to do with it but the full filepath is
"J:\Weekly Sales Summaries\D&C\Fab Report\FRONTLIST BACKLIST SALES\2011\05 MAY 11"
I cut it down to something simple for the forum.
Regards
Tim.
Hello Tim,
Can you show me what a typical file name (with the full path added) looks like?
Hello Tim,
Sorry, I see it in your previous post. Yes, the file path does matter. I have changed the macro to include the path you specified. Try it now and let me know.
Please Login or Register to view this content.
Hi Leith,
The full filepath plus filename would be -
"J:\Weekly Sales Summaries\D&C\Fab Report\FRONTLIST BACKLIST SALES\2011\05 MAY 11\090511 SUMMARY.XLS"
Tim
Hello Tim,
See post #9 for the correction.
Hi Leith,
Same problem when I step through it and hover over FileName in the above line of code the Filename = ""Please Login or Register to view this content.
Tim
Hello Tim,
Then something is wrong with the file name or path. Add line to your macro above the "FileName" line.
Please Login or Register to view this content.
If the message box shows 090511 SUMMARY.xls then there is a problem with the macro. If it is blank then file does not exist or the path is incorrect.
Hi Leith,
The message box shows 090511 SUMMARY.
Tim.
Hello Tim,
Okay, change the offending line to this...
Please Login or Register to view this content.
This should work.
Hi Leith,
Macro worked but saved the file as 050615 SUMMARY instead of 160511 SUMMARY.
Tim.
Hello Tim,
The macro saves a copy of the active workbook as the new name. The original copy stays open. If you want the original to be saved as the new name make the this change...
Please Login or Register to view this content.
Hi Leith,
No I want a copy I don't want to lose the original but the date of the new file is wrong. I have just been stepping through it these show up when I hover over your code
Please Login or Register to view this content.
d should be 16
m should be 05
y is right 11
Is it being thrown out by the missing leading zero in 090511?
Tim
Last edited by Leith Ross; 05-17-2011 at 05:25 AM. Reason: Added Code Tags
Hello Tim,
I'm batting a thousand in the toilet here. I typed the variable fnDate wrong. It is declared as double it should be string.
Please Login or Register to view this content.
Hi Leith,
Now I get Type Mismatch here
Please Login or Register to view this content.
Tim.
Last edited by Leith Ross; 05-17-2011 at 05:25 AM. Reason: Added Code Tags
Hello Tim,
Found another problem. You will need to make this change as well. Afterwards, the macro should run correctly.
Please Login or Register to view this content.
Brilliant Leith,
Runs perfectly.
Can I ask one extra favour?
Now I have a copy of last weeks workbook. The copy 160511 SUMMARY is closed and last weeks file 090511 SUMMARY is open. using the code that is already there would it be possible to add a samll piece of code to close 090511 SUMMARY (the open workbook) and open the new one 160511 SUMMARY?
Many Thanks
Tim.
Hello Tim,
Change the last "If/Then" statement using the code below...
Please Login or Register to view this content.
Hi Leith,
This last piece of code tries to save the Personal.xls workbook where the macro is stored rather than the file 090511 SUMMARY?
Tim.
Hello Tim,
Replace your macro code with code here. This should save the original workbook and not your Personal.xls.
Please Login or Register to view this content.
Hi Leith,
That didn't work because Set Wbk = Nothing
TimPlease Login or Register to view this content.
Hello Tim,
Looks good to me and if it works correctly then use it. Thanks for hanging in there through all my mistakes. I haven't had a day like this in quite while. Guess I was overdue. If your satisfied that all your questions have been answered then please mark this post as solved.
Thanks for all the help Leith very much appreciated. I have marked post as solved.
Tim.
I know your problem is resolved but I just want to shed some light on the failure of the DIR command when using #'s
This failed
should workPlease Login or Register to view this content.
Help saysPlease Login or Register to view this content.
In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files. On the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks