[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Run VBA conditionally on file name

Posted on 2009-05-01
10
Medium Priority
?
371 Views
Last Modified: 2012-05-06
Is there a way to have a macro automatically run if it has a certain file name?
0
Comment
Question by:ict-torquilclark
  • 5
  • 3
  • 2
10 Comments
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24277283
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 24277825
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24277849
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
LVL 1

Author Comment

by:ict-torquilclark
ID: 24277907
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24277958
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 24278090
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 24278236
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
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24278298
That's a simple and neat solution Rorya :-)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24278421
Thank you very much RichardSchollar! :)
(why are we being so formal??)
0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24278439
Do I know you?   ;-)

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question