Hi All,
I want to use vba to open a file where the name changes slightly each week it comes in. Let's say the file name is f:/Folder A 05212009
I can do this to open it:
But, next week when the file name is Folder A 05282009, it won't open. Is there any way to write the code so that it picks up this pattern and can open it each week?
Please Login or Register to view this content.
I greatly appreciate any help you can give me!
Last edited by souljive99; 05-26-2009 at 05:47 PM. Reason: added code tag
Try something like this...it will search ThePath you designate for the filenames in the format you named. It will start with Today's date and work backward until finds a file and opens it...then it will stop.
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 theicon 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!)
Thank you very much, but this isn't working. Can I give you the exactly file names? The folder is C:\Documents and Settings\trevor\Desktop and the file name is "test 05.26.2009.xls"
I like the idea of starting with today's date and running back through, but it doesn't seem to be working....
That's because your original post demonstrated a filename format that does not match your need. Always best to start with ACTUAL need rather than something you'll have to interpret.
Here's the code amended to your current example. I've highlighted the portions in red where you could make adjustments:
Please Login or Register to view this content.
It just sits with the hour glass and when I click Escape then debug, the part with "If Err <> 0 Then" is highlighted
Try running through the code in the VBEditor using the F8 key to issue one command at a time... When it highlights that line of code, put your mouse over the Err variable and see what the current value is.
I just used that code 3 times in a row to open varying files off of my computer. You could also triple check the path you gave me.
Cool! This worked when I tried it again...Thank you so much...Actually have a follow up - can I use that same method to look into the most recent folder? The date format is YYYY_MM_DD
Basically, I'd like to open up a folder like the following:
C:\Holdings\2009_05_26
Thanks again for your help.
Open it when? For what purpose? As part of this macro?
You already see the formatting in the macro I've presented. You can play with ThePath variable the same way.
Please Login or Register to view this content.
Do you do separate loops for the path and the string, like this? or is this wrong...
Please Login or Register to view this content.
Last edited by souljive99; 05-26-2009 at 05:42 PM. Reason: added code tags
souljive,
Please edit your post to add code tags.
Entia non sunt multiplicanda sine necessitate
Sorry, I'm new...Is this how you tag codes?
Do you do separate loops for the path and the string, like this? or is this wrong...
Please Login or Register to view this content.
Last edited by souljive99; 05-26-2009 at 05:42 PM. Reason: added code tags
No, it's not. The forum rules explain it, and so does the How To ... link in the menu bar.
Sorry...hope this is right.
Anyway, I am not sure if you have do this loop for the path and the string or once for both...if someone could take a look at this and let me know what I am doing wrong I would really appreciate it. Thanks.
Please Login or Register to view this content.
SJ, stop adding new posts with the same code. The moderators want you to go back and add those code tags to the PREVIOUS posts. You need to fix all of them, even the code in post #1.
==========
Also, you took my code idea and moved it around. What I gave you in post #8 was in a different order than what you put in post #9. You can't use the variable TheDate before you've even given it a value...
And your question really confuses me. You're telling me that would have FOLDER names with dates on them and then files INSIDE the folder that don't go WITH the same date? Isn't that intrinsically problematic?
I apologize...I'm new to this, I'm trying to post things the right way. I think I understand now. Sorry for the error - I really do appreciate all the help.
What I am trying to do is look for the most recent folder that has a date in it, say :
F:\holdings\2005_05_21
Then, in that folder I want to find the specified file, which is in this format:
F:\holdings\2005_05_21\portfolio 05.21.2009.xls
Of courses, those dates change every week. I want to open that file so I can change it around with a macro and save it again with a different name.
As I said before - I really appreciate all the help and sorry for the multiple posts.
souljive, you broke the code, thank you.
Did you mean to demonstrate year in 2005 for the folder and a year of 2009 for the file? I'm going to presume that was an error and just make them the same:
Please Login or Register to view this content.
Last edited by JBeaucaire; 05-26-2009 at 07:56 PM.
I'm using the f:\ drive. Do I need to use a Change Directory function or should it work? I copied your script exactly and its not picking it up.
Find the two references to the C:\ drive in the macro and change them accordingly.
In the vba script, when I put the curser over the words TheDate it says "TheDate = 4/28/1996" and when I put the curser over Date it says "Date = 2/2/09" I think this may be why it's not working. Do you know what could be wrong? Thanks.
In your VBEditor, press CTRL-G to bring up the Immediate window. Then enter these two lines of code one at a time into the window and see if the msgbox that pops up has the correct date or not:
If you want to work on this in realtime, my Skype ID is devtuxx7069. Just click the green checkmark across from name under the Location: info.Please Login or Register to view this content.
I'm sure there's something simple tweaked on your system somewhere, the code is pretty basic.
Unfortunately I can't use skype on this computer.
This is the code I am using:
These are the full file names:Please Login or Register to view this content.
folder - "C:\holdings\2009_05_10"
file - "portfolio 05.10.2009.xls"
When I did the immediate msgbox to bring up today's date, it worked (it brought up 5/26/2009)
Whenever I run the macro the hourglass just continues to run and when I hit escape and debug, the If Err <> 0 part is highlighted...any ideas?
Sorry SJ. The code looks fine. It opens a file for me when I create the folder and file on my system.
The only unknown is your computer.
I think I figured out the problem but I don't know the solution...
When I put the date format as YYYY_MM_DD it picks up the date as 2009 5 26
Any idea on how to fix this so it picks it up as 2009_05_26 (with the underscores and the 0 in front of the 5)
OK, something about that pesky underscore doesn't want to evaluate inside the TEXT function, so I cheated. I used another character (¢) and then used a substitute function to change it INTO an underscore. Problem solved.
Please Login or Register to view this content.
Thanks! That works well...only problem is that the hour glass remains on the screen after the file is opened...so I can't continue the script to alter the opened excel sheet. Shouldn't the loop until...part do that?
Interesting. It stops on my system. We can add a "stop" command I suppose. Once more...:
Please Login or Register to view this content.
Hello,
This is a really useful conversation. I have learned a lot from reading it. I am also relatively new and have a related question I thoink could be answered realtively easily (or so I hope!).
I am trying to not only open a file that has a date component that is important, but also has a time stamp that I want to ignore.
For example, the file from today could either be 20120904_0900.xls (if it was run at 9AM) or 20120904_0843 (if it was run at 8:43 AM). I would like to open it whatever time it gets produced. I know it has something to do with the * function (like *.xls to open or copy every excel sheet in a folder) I have seen so often on this site but I cannot quite figure out exactly how to do it.
Any help would be greatly appreciated.
Thanks!
Shannon
Hello slaneues, and welcome to the forum.
Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
Sorry! I am relatively new and forgot some of the rules. I found a solution to this, but thanks anyway!
Shannon
Last edited by JBeaucaire; 09-05-2012 at 03:24 PM. Reason: Removed whole post quotation
Is there any way that we can set up the code skip the message box that says "Path\FileName.xls cannot be found. Check your spelling or try a different path." so it will auto loop until it opens, otherwise if it is a monthly document you have to hit ok 30 times until it opens
Scott,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hello,
I am also trying to do the same thing. But instead of an .xls file it a PowerPoint one. I am using a macro to add a PowerPoint file as an object.
The file's name is also a date. What is the equivalent of WorksheetFunction.Text(TheDate, "YYYY_MM_DD") function in VBA for PowerPoint?
I would appreciate your help.
halaami8
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks