VB code Get 2 latest file names from Directory

**rizmomin** · 12-31-2014, 02:30 PM

Hello:

Please refer to attached file.

I have check box in column A cell I1:I2 and the result for check box is in cell I1:I2 as TRUE OR FALSE.

If I1 or i2 is TRUE then I need to fetch the 2 latest file name from directory as shown in K1:K2.

Example:
I have lots of file in C:\Store1\.
If I1 is TRUE then need to go to the directory C:\Store1\ and get 2 newest file and put the file name in L1 and M1
If I2 is TRUE then need to go to the directory C:\Store2\ and get 2 newest file and put the file name in L2 and M2

Let me know if you have any questions.
Thanks.

Riz

**apo** · 12-31-2014, 06:10 PM

Hi..

Something like this..

Assumes it is Excel files you're looking for..

Please Login or Register  to view this content.

**rizmomin** · 12-31-2014, 06:37 PM

Hi :
Thanks a lot, but will have pdf file.
Please correct the code so that it can pick up the newest pdf file from the list of files.
Riz

**apo** · 12-31-2014, 06:39 PM

Just change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

in both places...

**rizmomin** · 12-31-2014, 06:50 PM

Hi apo:

I tried and it would not work.
I have 2 directories and some files in each dir.
Need to pick 2 newest files from each dir and paste in the excel file.
Not sure if i am doing something wrong.
Please help.
Thanks
Riz

**apo** · 12-31-2014, 09:30 PM

Hi...

It works fine here.. so it must be something minor..

I tried and it would not work.

Can you expand on that..

What is it doing/not doing?

What are you seeing when viewing the Locals window when you are stepping through the code (F8)..?

**Leith Ross** · 01-01-2015, 03:03 PM

Hello Riz,

The Check Boxes use a single macro to return the two most recently created files. This can be changed to the date last modified or accessed. When the Check Box is true it will return the tiles names.

The location of the folder and the file names are dependent on the upper left corner of the Check Box. I have made sure that your Check Boxes are in cells $I$1 and $I$2. If you add any more, please be sure the upper left corner of the drag-drop outline is in cell $I$3 etc.

Module3 Macro Code

Please Login or Register  to view this content.

**rizmomin** · 01-01-2015, 10:00 PM

Hi Leith:

Superb work..
Need a small modification in the code.
If check box is false then related cells where the file names are stored needs to clear.
Please let me know if any questions.
Thanks a lot.
Riz

**Leith Ross** · 01-02-2015, 12:27 AM

Hello Riz,

Happy New Year! I thought you might want to do that. This workbook has that change added. A few other changes were also made to speed up the sorting of the files by date.

Let me know if you have any issues with this version.

Revised Macro Code for Module3

Please Login or Register  to view this content.

**rizmomin** · 01-02-2015, 08:50 PM

Hi Leith:

Sorry i could not get back earlier as I was travelling.
Just got back and tried your file.
I configured to my directory and it is malfunctioning for some reason.
I have lots of file (over 200) files in a folder and by clicking the checkbox it showed up the files which did not exist.
I just realized that it would be even better if it looks for the text which will be cell A15.
For example : if A15 is 12-31-2014, only look for file with the name 12-31-2014 and may be list all files with 12-31-2014 starting in cell L1:Z1
Once again i am sorry if i had confused you and i am hoping that this clear explanation.
Thanks a lot for your great help as always.
Please let know if you have any questions.
Riz

**apo** · 01-02-2015, 09:46 PM

Have you tried to debug the code i gave in post #2..?

Feedback would be nice.

It simply puts all the required files into a 1 Dimensional array (sorted from newest to oldest).. and then assigns the first 2 elements in that array to your required cells..

You need to debug by stepping though with F8 in the VB Editor and make sure the locals Widow is open.. have you done that?

**rizmomin** · 01-02-2015, 09:54 PM

Hi Apo:
This is what happens.
When i click on check box, it gives Runtime error, Subscript out of range.
When I debug with F8, It highlights at

Please Login or Register  to view this content.

Once again sorry for late response as I was travelling.

Riz

**Leith Ross** · 01-02-2015, 10:02 PM

Hello Riz,

Your last post is substantial different from the example you posted originally. If you have an updated sample workbook you should post it.

Will only one folder be searched or several? If several folders are to be searched please include the paths or were the paths can be found.

**rizmomin** · 01-02-2015, 10:14 PM

Hi Leith:
Please refer to attached file.
As you can see i have added 1 more column for Folder Name after Dir.
I will configure the Dir and Folder as per my needs but the attached structure should be ok.
Let me know if you have any questions.
Thanks.

**Leith Ross** · 01-02-2015, 10:57 PM

Hello Riz,

Thanks for the updated workbook.

If I understands this, the file name is in A15 and if the check box is checked then the macro will search the folder path using cells in columns "K1:L6"

What information is to be copied into the cells in columns "M1:N6"?

**rizmomin** · 01-02-2015, 11:10 PM

Hi Leith:
There is slight misunderstanding.
File name can be any thing.
What we need to look within the file name will the text in cell A15.
For example below are the file names in the dir C:\Store3\ and folder FolderA

12-10-2014-ABC
10-14-2014-BCD
12-31-2014-XYZ
12-31-2014-ABCD
06-20-2014-TYU
12-31-2014-FGH

In this case:
12-31-2014-XYZ
12-31-2014-ABCD
12-31-2014-FGH

will be put in cell M3,N3,O3...since C:\Store3\is in row 3.
This way i can add any text in Cell A15 and also this will shorten the search.

I hope this clears the confusion.
Feel free to let me know if any further questions.

Thanks a lot for your great master help.

Riz

**rizmomin** · 01-02-2015, 11:20 PM

Hi Leith:

The value in Cell A15 will be text : 12-31-2014 or somthing like that.
Thanks
Riz

**Leith Ross** · 01-03-2015, 02:40 PM

Hello Riz,

Sorry for the late reply. There we several emergencies I had to deal with after my last post.

Here is the new workbook and code. Test it out and let me know if this what you need.

Please Login or Register  to view this content.

**rizmomin** · 01-03-2015, 04:20 PM

Hello Leith:
Thanks a lot..seems to work
I will test more little later.
Thanks a lot. I really appreciate a lot.
I will let you more after further testing.
Riz

**Leith Ross** · 01-03-2015, 04:22 PM

Hello Riz,

You're welcome. Look forward to hearing about the results.

**rizmomin** · 01-04-2015, 01:46 AM

Hello Leith:

After further testing below is the result.

As you can see in the excel sheet, we have row 1 with C:\Store1\FolderX. If I click the checkbox at cell I1 then i get correct result.
Now i have some files in C:\Store2\FolderY. When i click box in cell I2 in row2, then it should add the file names with the criteria set in cell A15 in cell M2 to Z2.
Currently what it is doing is it is filing up the same file names as in C:\Store1\FolderX in cell M1 and N1.

Please create 2 dir as C:\Store1\FolderX and C:Store2\FolderY.
Put some files with say 12-31-2014-test1_Store1, 12-31-2014-test2_Store2 and then try and see what i mean what is happening.

Please let me know the result and help resolve this issue.

I hope this is clear and let me know if any questions.

Thanks a lot in advance for your help provided and looking forward to hear from you.

Riz

**Leith Ross** · 01-04-2015, 02:01 PM

Hello Riz,

This workbook will create a folder using the contents of cells K and L that are in the same row as the Check Box. All files that match or partially match the file name in cell A15 will be copied into cells M:Z starting with the file that was created most recently.

Here is the updarted code.

Please Login or Register  to view this content.

**rizmomin** · 01-04-2015, 03:02 PM

Hi Leith:

This works great and this will save me tons of time and manual error.
I may need you further to add extra feature in the same project.
I will let you know if that is ok with you.
Once again your expert help in VB programming is always appreciated.

Riz

**Leith Ross** · 01-04-2015, 04:15 PM

Hello Riz,

You're welcome. If and when you need help adding features, let me know. I would be glad to help.

**rizmomin** · 01-05-2015, 12:01 AM

Hi Leith:

Please refer to attached file.
As I mentioned to you earlier that I may need you further to add some feautures.
I have Store Names in Column A and respective email in Column B
What i need to do is select the check box.
Once you select the check box, acccording to your smart work, we will get File name in column M,N, O, ....
Now as we know the email address in column B, the files needs to be emailed to respective store.
The body of the email is as shown in cell B20:B22
The signature of the email is as shown in cell B24:B26

I would also like to know how to copy the check box so that the result does not mess up.

Let me know if you have any questions.

Thanks.

Riz

**Leith Ross** · 01-05-2015, 12:07 AM

Hello Riz,

I'll know more once I open the workbook but this sounds straight forward. The emails can be automated using Outlook.

I will code a routine you can run to place next check boxes in a cell so you want have to worry about proper alignment.

**rizmomin** · 01-05-2015, 12:17 AM

Hi Leith:

Thanks a lot for all your help as this will help me a lot.
Also would like to copy the file names which have been emailed from A50 download. Feel free to let me know if any questions.
I will later do conditional formatting to check if the file has been emailed in case for multiple attempt to email.
Thanks

Riz

**Leith Ross** · 01-06-2015, 05:06 PM

Hello Riz,

Sorry about the delay. My wife has the flu, her father has bronchitis, and our clothes washer broke. I tried to fix it yesterday but it was too rusted. We and bought a new one. Now, I am waiting for the delivery people to come.

Amidst all the chaos, I have managed to get the workbook changes made. The emails are displayed rather than sent so you can tell me what needs to changed without flooding your inbox during testing.

To add a new CheckBox to a cell use the hot key combination Ctrl+Shift+A. This will size the CheckBox to the cell's dimensions and attach it to "Macro1"in the workbook. The caption is "Send". You can change this if you want to. The macro is in the new module Ädd_Forms_CheckBox_To_Cell. The email is sent using the macro in the module Send_Email.

Test it and let me know what needs to added or changed.

**rizmomin** · 01-06-2015, 07:02 PM

Hi Leith :

Thanks a lot for all your help, I am hoping that your family is feeling better now from illness.
I have tried your work, however, this is what happens.
I have loaded 3 files with criteria set in Cell A15 to the assigned directory in K1 and folder L1.
Now when i click on the checkbox in cell I1, it gives error as in attached pic.
This error is module "Send_Email" :

Please Login or Register  to view this content.

Please check and let me if i am doing something wrong.
Thanks a bunch for all your help.
Riz

**rizmomin** · 01-06-2015, 07:49 PM

Hi Leith:
Would be even better o have a 1 separate button to send email.
First i will click on check boxes. Once the whole grid is filled (which ever row click box is selected), I will visually confirm the files.
Then i will press SEND Button go thru and email to respective person.
Please let me if any misunderstanding/
Thanks.
Riz

**Leith Ross** · 01-06-2015, 09:49 PM

Hello Riz,

The original layout had a single file name in A15. Has this changed...

I have loaded 3 files with criteria set in Cell A15 to the assigned directory in K1 and folder L1.

If so, please include an example of this new layout.

**rizmomin** · 01-06-2015, 10:08 PM

Hi Leith:
No, nothing has changed.
i think you misunderstood me.
What i meant was that i have put 3 files to match the criteria in cell A15.
Example : A15 = 12-31-2014.
I added 3 files with file names starting 12-31-2014.
By clicking the check box, these 3 files will be listed in cellM1,N1 and O1.
This is exactly I wanted.
The issue is that when i click the check box it gives error as mentioned earlier.
Also it would be best if i select the checkbox and once completed, i will confirmed the files which needs to be emailed as they will be listed in M1,N1,O1,M2,N2,O2m etc...
Once confirmed I will need a button to press which will send email.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-06-2015, 10:18 PM

Hello Riz,

Are the files on your hard disk or a network drive?

**rizmomin** · 01-06-2015, 10:21 PM

Hi Leith:

They will be on Hard Drive Location example : C:\Store1\FolderA as this is in cell K1 and L1.
Please let me know if there is still confusion.
Thanks a lot..
Riz

**rizmomin** · 01-06-2015, 10:27 PM

Hi Leith:

Please check the attached pic which shows the error when check box is clicked.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-06-2015, 10:27 PM

Hello Riz,

No confusion just can't exp[lain the error. I thought you might be accessing a network drive or had mapped a drive. Often times this can cause problems when accessing data due to permissions, passwords, etc.

The code runs for me with no problems whether I have a single attachment or seven.

**Leith Ross** · 01-06-2015, 10:31 PM

Hello Riz,

Why are all these files missing their file extensions?

**Leith Ross** · 01-06-2015, 10:50 PM

Hello Riz,

Not sure if you are planning to email any Zipped files but I did discover one anomaly. Zip files are not listed. Technically a Zip file is a folder and not a file. I'll work on this problem also.

I tried removing the extensions from a few files to see if that caused the email problem but it didn't. I think it is time to punt.

When weird unexplainable problems like this happen, I defragment my registry. That usually clears the problems.

**rizmomin** · 01-06-2015, 10:53 PM

Hi Leith:
No Zipped files...
All files is very likely to be pdf files.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-07-2015, 10:21 PM

Hello Riz,

This workbook has a Command Button to send the emails. It tests the Check Boxes. If checked then the files from column "M" to the last column with a file name in it will be attached to the email address in column "B".

I tested this on several different folders on my computer using both local and remote disks. There were no problems. Try this out and let know what happens.

**rizmomin** · 01-08-2015, 12:49 AM

Hello Leith:

Thanks a lot for hard work.
Now every thing works except do not see attachment in email.
Please have a look at 2 pics i am uploading.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-08-2015, 04:16 AM

Hello Riz,

There is documentation of attachments to be sent not showing up in Outlook 2007/2010/2013 but for PDF files. Since your files do not have extension, I don't know if this applies to your situation.

I failed to find any settings in Outlook that would cause this problem. It is possible to hide an attachment by setting an argument value in the Add method for an Attachment object but the macro does not exercise this option.

Are you using Outlook as your primary email client?

**rizmomin** · 01-08-2015, 09:24 AM

Hi Leith:

Yes I am using Outlook 2013.
Now, when I started this project, some one helped me initially and attached file with the macro code would work and i would receive email.
Please have a look and implement this code so that it would work.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-08-2015, 12:59 PM

Hello Riz,

I believe I know what was causing the problem. I had the Outlook 14 library loaded for reference and did not remove it before saving the workbook. The macro uses late binding to create the email through Outlook. I suspect there was a conflict between what was saved and what was loaded at run time. Outlook 2013 is version 15 and Outlook 2010 is version 14. Give this workbook a try and hopefully the problem will be solved.

**rizmomin** · 01-08-2015, 01:40 PM

Hi Leith:
Still same problem.,...no attachment.
Not sure what seems to be the problem.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-08-2015, 02:45 PM

Hello Riz,

Which version of Windows and Office are using?

**rizmomin** · 01-08-2015, 03:05 PM

Hi Leith:

Using Windows 8.1 and office 2013.
I had uploaded a file little earlier (#43) when i started this project and some one in this forum help me with teh code and that code works.
Need to implement that code with your layout.
Please have a look and let me know if any questions.
Thanks a lot for your help.
Riz

**Leith Ross** · 01-08-2015, 03:16 PM

Hello Riz,

Thanks for that info. Windows 8 is is most likely the problem here. I will do more research specific to Windows 8.

The code created for you and the macro I wrote are basically the same. The only differences are the cell referencing and my macro uses HTML message body and not plain text message body.

I can modify the macro to use plain text and see it that works.

Some Anti-Virus software will prevent Outlook 2013 from attaching and sending files. You may want to check which Add-Ins you have installed for Outlook.

If you are able to work with Outlook without any problem then the issue might be due to add-ins. In order to disable the add-ins follow the steps given below:

· In Outlook, click on File< Options< Add-ins.

· Under Manage select COM Add-ins.

· Click on GO options beside Manage, uncheck all the add-ins and click Ok.

· Try to open Outlook and check if it works fine.

If the issue occurs in safe mode too then create a new profile, configure the email account in new profile and check if it works fine in new profile. Refer the link mentioned below to create new profile:

How to create profile and set up an e-mail account in Outlook

**rizmomin** · 01-08-2015, 03:27 PM

Hi Leith:

I disable all Add-Ins and restarted the outlook and still the same problem.
Please let me know if you find something i need to test as you research.
Thanks
Riz

**rizmomin** · 01-08-2015, 04:13 PM

Hi Leith:
I believe i found the problem.
The problem is with the file name. It needs extension.
I manually added .pdf to the file name (attached pic) and it works.
Please see if the file extension can be added.
Please let me know if you have any questions.
Thanks a lot!!!!!!!!!!!!!!!!!!!.
Riz

**Leith Ross** · 01-08-2015, 04:15 PM

Hello Riz,

Were the files saved without the PDF extension originally?

**rizmomin** · 01-08-2015, 04:24 PM

Hi Leith:

No...they were already pdf file.
I have not changed any thing.
Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-08-2015, 04:30 PM

Hi Leith:

Let me take it back.
I am attaching the pic for the folder with the actual file name.
I am hoping that this answer your questions.
Looks like the pdf extension is not there, all it says is Adobe Acrobat document.
Please let me know if you have any questions.
Thanks.
Riz

**Leith Ross** · 01-08-2015, 04:36 PM

Hello Riz,

It looks like the the default setting of hiding file extensions affects the Shell Controls and Automation functions. Set Windows to show the file extensions.

Here is an article explaining it step by step for Windows 8.
How to Show File Extensions in Windows

**rizmomin** · 01-08-2015, 04:50 PM

Hi Leith:

You are my men....Super thinking.
Works, however, i will test further and see any other problem.
I would like to ask couple of favors.

1. Upload the new version with no other unneeded codes for this project.
2. i would like to add one more feature if only possible from your side

As belows:

Once we press SEND Email button, i need the copy of each files names which have been emailed starting in cell A51 and down.
Lets say i have
M1 = Test1.pdf
N1 = Test2.pdf
O1 = Test3.pdf
M2 = Test10.pdf

Then
A51 = Test1.pdf
A52 = Test2.pdf
A53 = Test3.pdf
A54 = Test10.pdf

This copy has to be in the next available row.
I will then use the conditional formatting to change the color in the grid M1:O13 so that i will know if particular file is emailed or not.
.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-08-2015, 05:18 PM

Hello Riz,

This workbook has the extraneous code removed and copies the sent file names starting at cell "A51" and down.

**rizmomin** · 01-08-2015, 05:24 PM

Hi Leith:

Looks like you have fulfilled my requirement and i really appreciate your GREAT help in this little project.
I would test further with the live data and see if any issues.
If there is then i will let you know.
Once again, thank u so much for help.
Riz

**Leith Ross** · 01-08-2015, 05:37 PM

Hello Riz,

You're welcome and I'll keep my fingers crossed for the live test.

**rizmomin** · 01-10-2015, 06:20 PM

Hi Leith:

This one seems to work fine without any problem.

I would like to ask to have a look at my new simple tread "Excel VB Code to Run Sub routine in Outlook " which I have posted.
Would appreciate if you can have a look at that in your time and let me know if there is easy solution for that.
Thanks
Riz

VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Re: VB code Get 2 latest file names from Directory

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Reading in files from directory......does this code also alphabetize the file names

Bookmarks

Bookmarks

Posting Permissions