Macro auto run on file creation.

Closed
Posts
4
Registration date
Tuesday October 13, 2015
Status
Member
Last seen
October 13, 2015
-
Posts
4
Registration date
Tuesday October 13, 2015
Status
Member
Last seen
October 13, 2015
-
Hello,

I have an excel file created automatically every day, I want to be able to run a macro against the file when it's opened.

I can automate the file opening at a specified time to run a macro against it but the file is newly created daily so any auto open events would be overwritten. How can I automate the macro running against the file?

Thanks


1 reply

Posts
4
Registration date
Tuesday October 13, 2015
Status
Member
Last seen
October 13, 2015

In case anyone else runs into this problem, this is the scenario and how I combatted it:

We have a file emailed to us daily at 06:00, the file is automatically stripped from the email and saved to a specified folder with a default name, the file is written as a new file daily.

We needed to run a macro against the file, change some data, save it as a new file in a different location and have another process upload it to a FTP server.

This all needs to happen without any manual intervention, stage 1 and 3 were already accomplished, we could strip the attachments and have them renamed / uploaded as required. The problem we had was with the Macro we needed to run against one particular file.

We solved this by keeping open the personal-macro file in the background and adding the application.ontime option:

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

inside of the my_Procedure sub enabling the macro to run at 17:00 each day opening the specifically named workbook and processing the data, all without the need to manually intervene.