Hey -- I'm a new member. I know many of these things have been been covered in pieces, sporadically, but I cannot get them to work together. I have no ruled out the fact that I might be an idiot, but would appreciate any insight.
I am a beginner, so apologies for any rudimentary explanations or simplistic explanations on my part.
THE SITUATION
I have a newsfeed plug-in attached to an Excel Workbook called "GoodEvening". the plug-in auto-updates it with real-time metrics, but only upon the workbook (GoodEvening.xlsm) beign opened/refreshed.
In GoodEvening.xlsm, I have a macro/vba that says the following -- if GoodEvening is idle for 3 min, it saves and closes it. 2 minutes is approx the amt. of time the workbook need to be updated by the plug-in.
Const idleTime = 180 'seconds
Dim Start
Sub StartTimer()
Start = Timer
Do While Timer < Start + idleTime
DoEvents
Loop
Application.DisplayAlerts = False
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub
MY FIX IDEA
1. Create a Task Schedule that opens GoodEvening at 6AM (EST)
2. Enable Content automatically
3. GoodEvening.xlsm connects through plug-in and updates
4. After the update, if Cell B2, the column B (column name = Gophers) has ANY VALUE IN IT (pre-arranged formula I've set up in field F1), email me with the following message.
"Good Morning, Josh. [value in cel A2] now has [value in cel B2] Gophers in it." My email address is [josh@aol.com]
5. After 3 minutes of idle time, the macro saves and closes the workbook GoodEvening
*this should all happen whether or not my PC is asleep
**ideally, I'd love a log of the activity (i.e. whether or not an email has been sent, what time GoodEvening opened/saved/closed), any errors and why
THE PROBLEMS
a. Aside from the StartTimer VBA (above), I cannot get my task scheduler to open the workbook 'GoodEvening'. It keeps showing an error.
b. I cannot find an open_workbook macro that works. Admittedly, I don't understand how to construct one from scratch.
c. While the current StartTimer macro works, it has locked my ability to edit the macro while workbook 'GoodEvening' is open.
d. I cannot get an email VBa/macro to work at all
I'm using Excel 2010, Windows 7.
Any help or insight would be wildly appreciated.
I am a beginner, so apologies for any rudimentary explanations or simplistic explanations on my part.
THE SITUATION
I have a newsfeed plug-in attached to an Excel Workbook called "GoodEvening". the plug-in auto-updates it with real-time metrics, but only upon the workbook (GoodEvening.xlsm) beign opened/refreshed.
In GoodEvening.xlsm, I have a macro/vba that says the following -- if GoodEvening is idle for 3 min, it saves and closes it. 2 minutes is approx the amt. of time the workbook need to be updated by the plug-in.
Const idleTime = 180 'seconds
Dim Start
Sub StartTimer()
Start = Timer
Do While Timer < Start + idleTime
DoEvents
Loop
Application.DisplayAlerts = False
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub
MY FIX IDEA
1. Create a Task Schedule that opens GoodEvening at 6AM (EST)
2. Enable Content automatically
3. GoodEvening.xlsm connects through plug-in and updates
4. After the update, if Cell B2, the column B (column name = Gophers) has ANY VALUE IN IT (pre-arranged formula I've set up in field F1), email me with the following message.
"Good Morning, Josh. [value in cel A2] now has [value in cel B2] Gophers in it." My email address is [josh@aol.com]
5. After 3 minutes of idle time, the macro saves and closes the workbook GoodEvening
*this should all happen whether or not my PC is asleep
**ideally, I'd love a log of the activity (i.e. whether or not an email has been sent, what time GoodEvening opened/saved/closed), any errors and why
THE PROBLEMS
a. Aside from the StartTimer VBA (above), I cannot get my task scheduler to open the workbook 'GoodEvening'. It keeps showing an error.
b. I cannot find an open_workbook macro that works. Admittedly, I don't understand how to construct one from scratch.
c. While the current StartTimer macro works, it has locked my ability to edit the macro while workbook 'GoodEvening' is open.
d. I cannot get an email VBa/macro to work at all
I'm using Excel 2010, Windows 7.
Any help or insight would be wildly appreciated.
