Hi, all.
I am trying to locate files with certain extension, such as, jpg from a designated folder.
I am using Excel2007, so I can't use FileSearch.
For each file that the program finds, I want to write the full filename into a spreadsheet.
Thank you for all your help in advance.
This will put a list of JPG from the path you designate in the highlighted section into sheet1.
If you want the full path in the listing, then change this one line:
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by JBeaucaire; 09-24-2009 at 02:35 PM.
_________________
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 JBeaucaire.
But the macro is not outputting any file names in the spreadsheet.
Is there a steps that I need to take in order for this macro to work?
Thank you.
Sorry for not waiting longer.
I am in a short timeline and would greatly appreciate the fast help.
Does anyone have a suggestion on why the above code does not work?
OR does anyone have another code for the suggestion?
Thank you all for your help.
Hello KrNpRiDe,
Here is another version. You can specify the folder path, file type, and where you want to output the file names.
ExamplePlease Login or Register to view this content.
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!)
As I indicated before the only thing you need to do is edit the path (marked in red) to the files you want listed before you ran the macro. Did you do that?Originally Posted by KrNpRiDe
How to use the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Edit the path to the files you want listed
6. Get out of VBA (Press Alt+Q)
7. Save your sheet
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
I just ran it again and it lists an entire directory, no problem.
Thank you Leith Ross and JBeaucaire.
I have tried both codes and does not work.
JBeaucaire, your program runs without error, but does not output anything to the spreadsheet. The following is the code that I use to run.
Please Login or Register to view this content.
Leith Ross, Your code is giving me an error:
Run-time error '91': Object variable or With block variable not set
I have modified the code a bit to suit my need, but it seems the error is setting the range.
The following line is giving me the error.
Dst = Sheet1.Range("A1")
Any solution to this issue will be appreciated.Please Login or Register to view this content.
Once again, thank you so much for your help.
When you go into the VBEditor and use F8 to step through my macro one line at a time, and you pass this line:
...hover your mouse over the fName variable and what does it show? It should list the first jpg file in that directory.Please Login or Register to view this content.
When I run it, I can see all 10 jpgs in my folder, one at a time of course.
Something else you can check, make sure Excel is changing directories properly. When you pass this line of code:
...switch back to the sheet and do a CTRL-O to open the current directory, are you in C:\Pictures? Can you filter for *.jpg and see .JPG files in this folder?Please Login or Register to view this content.
Thank you for the quick response.
When I am stepping through the macro, I can see that the fName stores the proper values.
But it is not writing the name into the spreadsheet.
Thanks.
JBeaucaire, I was able to get an output.
But it will only output, after stepping through the micro until the first output and then run the macro.
Is there a way to fix that error, without having to step through the process everytime?
I want to be able to run the program, without having to step through everytime I want to run this macro.
Thank you.
If the macro works by stepping through the code, then it should work on its own. I can't imagine what might be interfering, it's pretty simple code.
You could try commenting out (removing) all the lines of code having to do with OldDir and see if that was having any odd effects.
Anyone else reading this see anything in Excel 2007 that might dislike any of the code? I've used these exact commands on many Excel 2007 macros.
Thanks, JBeaucaire.
I will try to erase that part of the code.
Thank you all for great and fast responses.
Hello KrNpRiDe,
I am in agreement with JB that this related to 2007 as both his code and mine work correctly in 2003. Not having Excel 2007, I can't troubleshoot any problems in the code. To get an answer faster, you could create another thread addressing this specific problem. If you do, you should include a reference back to thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks