Time delay macro looping weekly

Star Destroyer

New Member
Joined
Mar 18, 2009
Messages
14
Hi all,

Is it possible to write a macro that will sever links in a workbook at 2pm every Wednesday (links from 70 other workbooks)(!) and then auto run a restore links macro?


I can write a 'restore links' macro to run manually on a button press, but am lost as to the other link.

Your wisdom would be greatly appreciated.

Cheers all,

Mark
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Are you only looking for a way to set up the macro so that it runs at a certain time or do you need more than that? If you just need to have it run at a certain time you can use the OnTime Method.
 
Upvote 0
Cheers all. The Ontime method will work fine for the purpose. Thanks aswell for the link jbeaucaire for future reference.

Just one more thing. Whilst I use the Ontime method and can set it to run on Wednesday, is there a way that I can loop it to run every Wednesday?

Apologies if this is very straightforward and a case of me not seeing the woods for the trees, but I am trying to sharpen my axe so to speak, and am fairly new to all of this.

Thanks again,

Mark
 
Upvote 0
First the workbook needs to be open for the OnTime Method to work.

You could write code that runs every 24 hours (using ontime) and if the day of the week = Wednessday at 2pm then it would call the breaklink proc that you wrote. They key here is that the workbook is always open though. If you open and close the workbook daily then you could write code that uses an event such as open workbook event to start your code automatically (assuming macros are enabled) that would then check to see if it is Wed. and if it is then it would call the ontime proc that would execute right at 2.

There may be a much easyier way to do this but this is one way that might work.
 
Upvote 0
Thanks Millhouse,

I will need to use the Open Workbook event.

My problem is simply not knowing how to write the VBA code to check if it is a Wednesday. I can prompt the workbook to do it for one Wednesday on a specific date, but not to repeat.

If you can help with this I would be really grateful

Cheers man,

Mark
 
Upvote 0
Code:
Sub Test()
    Dim theDate As Date
    theDate = Now
    If Weekday(theDate) = vbWednesday Then
        MsgBox "Wednesday"
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top