Run VBA conditionally on file name

Is there a way to have a macro automatically run if it has a certain file name?
LVL 1
ict-torquilclarkAsked:
Who is Participating?
 
Rory ArchibaldCommented:
I would create a separate workbook containing the code from Personal.xls with a line to open the workbook you want added to it. Call that code from the Workbook_Open event of the new workbook and have the Scheduled task run that workbook instead of the created one.
Regards,
Rory
0
 
RichardSchollarCommented:
You could check to see what the file name inside the Workbook_Open event and then run a piece of code if file name is "X" for example.  Note however, that this relies on the user enabling macros when the workbook is open (as any VBA code relies on the user doing this if their security settings are set to anything other than Low):

Richard
'following code in ThisWorkbook module
 
Private Sub Workbook_Open()
If ThisWorkbook.Name = "YourSpecificFilename.xls" Then 'amend as appropiate
  Call YourSubName  'amend as appropriate
End If
End Sub

Open in new window

0
 
ict-torquilclarkAuthor Commented:
I am unsure weather this will achive what I need it to. The probalem is that every night the spreadsheet in question is overwritten with ther new one. Because the spreadsheet is overwritten does this mean that thre code will not work?

Thanks
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
RichardSchollarCommented:
If the spreadsheet in question is overwritten by another spreadsheet that does not contain the code, then there will then be no code to execute.  Please could you explain in detail what it is you want to achieve.
0
 
ict-torquilclarkAuthor Commented:
I have some statistics software that creates a .xls file every morning at 1:00am. The filepath and filename are always the same each time the file is created (and so the previous spreadsheet is overwritten). I have created a schedualed task to open the spreadhseet at 1:30 am. I have also created some code in the personal maco workbook so it is available each time a new spreadsheet is created to format the data and then email it to the required people. What I need to do is for excel to check the spreadsheet upon it opening and run the code (from the personal macro workbook) if the filename is "stats.xls".

I hape that makes sense.

Please ask if you have any more questions.
0
 
RichardSchollarCommented:
Is the computer unattended ie you want the file opening and checking to happen automatically with no user involvement?  Is it Windows Task Scheduler you're using to open the specific file?
0
 
ict-torquilclarkAuthor Commented:
The comnputer is unattended. I want to make the process compleatly automated. Windows is using Schedualed tasks (start > all programs > accessories > system tools > schedualed tasks) to open the file.
0
 
RichardSchollarCommented:
That's a simple and neat solution Rorya :-)
0
 
Rory ArchibaldCommented:
Thank you very much RichardSchollar! :)
(why are we being so formal??)
0
 
RichardSchollarCommented:
Do I know you?   ;-)

That 'a' on the end was a typo!  No idea how I managed that...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.