Hi there, I've been looking for a way to run a code when a new file appears in a folder.
Anyone have any ideas?
Thanks
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Make code run when new file appears in folder
0
Answers
0
The background theme of your question says that files don't "appear" in folders. They appear on the list of files Windows Explorer occasionally creates and the timing of that appearance depends upon the timing of the update of that list.
You can use VBA to monitor the number of files in a folder and raise a flag when that number changes. The question of who creates that file becomes central to such a plan. If it's created by external sources the only choice you may have is to check periodically. On the other hand, if the file is created by your own system you might check at a specific instance within your workflow.
If the file is created on your own system the next question would be if it's an Excel file. You have full control over the Excel application on your computer and can instruct it to notify you when it creates a file of particular specifications.
Discussion
Hi, thanks for your response, my computers are set up on a LAN, the other computer writes the file which is a .LIF and can be opened by excel, it is not protected, I have full control over the excel application on my computer and I can instruct it to notify you when it creates a file of particular specifications. I can see then in windows file explorer, when they are written by the computer they are always save in one folder, and always have a different name. There is no set time gap from when the last one to the most recent was created, and I need my excel workbook to pick up the new file immediately after it is created by the computer and saved. I already have codes to open the most recent file, copy out the data and paste it into a sheet on my workbook, but just no way for it to run automatically.
Am I able to use VBA to monitor the number of files in a folder and raise a flag when that number changes. This shouldn't be a problem but there are other file types in the folder I want to monitor, I believe the computer creates them all at the same time but is there a way to just count the number of .LIF files, if not it should still work
Thanks, Dave
Am I able to use VBA to monitor the number of files in a folder and raise a flag when that number changes. This shouldn't be a problem but there are other file types in the folder I want to monitor, I believe the computer creates them all at the same time but is there a way to just count the number of .LIF files, if not it should still work
Thanks, Dave
Dave1245 (rep: 8) May 30, '20 at 3:31 am
Hi Dave,
I think you understand everything perfectly: There is no way to notify you immediately. But you can chek as often as you like, even every second, and the result should be pretty nearly the same as "immediate notification". It's a simple On Timer program trigger that you can find on the Internet, even on this very site, I believe. Write a macro to run through the files in the folder under observation and perhaps pick out the lost recent one with LIF extension by its creation date. If you have a record of the creation date of the last one you already downloaded you should be all set to go.
Variatus (rep: 4889) May 30, '20 at 5:05 am
I'm afraid you've lost me there, how can I get it to check at regular intervals? I did think about make the code run every x seconds/minutes, but I thought this just would'nt be very efficient. I made a few searches for On Timer program but didn't find anything.
I'm still a beginner to VBA and using Excel in this way, so wouldn't be able to write the code to run through the files and see if a new .LIF file appears.
I already have a code to find the most recent file in the folder and it will open it. Is there a way to modify it so that it can do what I need? Not sure if it could do anything useful though?
I can post the open newest file code if you want to have a look
I'm still a beginner to VBA and using Excel in this way, so wouldn't be able to write the code to run through the files and see if a new .LIF file appears.
I already have a code to find the most recent file in the folder and it will open it. Is there a way to modify it so that it can do what I need? Not sure if it could do anything useful though?
I can post the open newest file code if you want to have a look
Dave1245 (rep: 8) May 30, '20 at 5:56 pm