Data connection refresh on Certain Days

Yeah you can include an If Statement in the code, if <> a day you want then Exit Sub.

Can you not do that with an event handler? if trying to refresh and WED then say they cant and exit?

Workbook open event IS a form of event handling. Unfortunately I am on my phone and cannot play with the ideas I have at the moment.

Take a look at this.
http://stackoverflow.com/questions/2...-data-in-excel

Originally Posted by mikeTRON

Workbook open event IS a form of event handling. Unfortunately I am on my phone and cannot play with the ideas I have at the moment.

Take a look at this.
http://stackoverflow.com/questions/2...-data-in-excel

Yes, I just meant that the refresh event seems to require initialization, and that initialization is setup as a macro you manually run (on that page). I was simply saying that initialization should be moved to the Workbook Open event to automate it so it affects everyone all the time without having to remember anything.

Just to make sure I was right, I checked, and yes, that bit of code at the end that can just be plugged directly into the Workbook Open event and it works fine (and I checked without it, and it doesn't work, then).

So, here you go. Follow the direction the page I linked to tells you to, (you don't need to create the data connection), but use this code for the Class Module (first section):

Please Login or Register  to view this content.

When it tells you to create the Module, ignore that. Instead, double-click ThisWorkbook on the left pane (Project Explorer) for the workbook you're in, and paste this (replacing the empty sub that's already there):

Please Login or Register  to view this content.

This may need to be tweaked if you have multiple tables or if they're on sheets other than the first one. Mike will have to help with that, though, because that's a bit beyond my skill set. Well, to change it to another sheet, just change "Sheets(1)" to the number of your sheet, but I imagine you'll want it to work for all sheets, and that requires iterating through them. I can do that, but I don't know how to incorporate the results with the first section of code.

Result: if you try to refresh on Tuesday, Wednesday, or Thursday, it refreshes like normal. If you try to refresh on other days, a message box informs you "You can't refresh today." and nothing happens.

Originally Posted by Xander9009

Yes, I just meant that the refresh event seems to require initialization, and that initialization is setup as a macro you manually run (on that page). I was simply saying that initialization should be moved to the Workbook Open event to automate it so it affects everyone all the time without having to remember anything.

Just to make sure I was right, I checked, and yes, that bit of code at the end that can just be plugged directly into the Workbook Open event and it works fine (and I checked without it, and it doesn't work, then).

So, here you go. Follow the direction the page I linked to tells you to, (you don't need to create the data connection), but use this code for the Class Module (first section):

Please Login or Register  to view this content.

When it tells you to create the Module, ignore that. Instead, double-click ThisWorkbook on the left pane (Project Explorer) for the workbook you're in, and paste this (replacing the empty sub that's already there):

Please Login or Register  to view this content.

This may need to be tweaked if you have multiple tables or if they're on sheets other than the first one. Mike will have to help with that, though, because that's a bit beyond my skill set. Well, to change it to another sheet, just change "Sheets(1)" to the number of your sheet, but I imagine you'll want it to work for all sheets, and that requires iterating through them. I can do that, but I don't know how to incorporate the results with the first section of code.

Result: if you try to refresh on Tuesday, Wednesday, or Thursday, it refreshes like normal. If you try to refresh on other days, a message box informs you "You can't refresh today." and nothing happens.

Nice! That should be enough to lock them out.

My biggest concern with workbook events is there always seems to be some little thing that causes it to break or some random bug that then I am responsible for handling instead of just telling the people, DO NOT DO THIS. So I have been trying to stay away from it lately LoL.