Alrightty, back in town now.
So, this is actually *REALLY* simple, and Ozgrid more or less spoon
feeds you the process.
What I eventually set up is a scheduled task that opens a file called
Reports.xls at certain times throughout the day, then closes it a few
seconds later. For example, it opens at 7:30am, then closes at 7:30am +
2 or 3 seconds. Between opening and closing, a macro within Reports.xls
runs that exports various excel spreadsheets as html. Here's the code:
---
In the scheduled task, have:
Run: "C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft
Excel.lnk" "T:\Health Measures\Reports\Reports.xls"
Start in: "C:\Documents and Settings\All Users\Start Menu\Programs"
----
In the thisworkbook section of the thisworkbook section of Reports.xls,
put:
Private Sub Workbook_Open()
Application.OnTime TimeValue("7:30:02am"), "Runupdate1"
Application.OnTime TimeValue("11:00:02am"), "Runupdate2"
Application.OnTime TimeValue("2:30:02pm"), "Runupdate3"
End Sub
(obviously, change the times accordingly)
---
In module1 or whatever, have something like this:
Public dTime As Date
Sub Runupdate1()
Application.OnTime TimeValue("7:30:02am"), "Runupdate1"
Application.DisplayAlerts = False
Workbooks.Open Filename:="T:\Health
Measures\Pricing\Pricing.xls", _
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"T:\Health Measures\Reports\Pricing.htm" _
, "Pricing", "$A$1:$O$21", xlHtmlStatic, "Pricing_30071",
"").Publish (True)
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:00:01"), "Save_Exit"
End Sub
This saves certain cells (the range A1:O21) as html. Again, change
accordingly. Excel closes a second later.
---
In Pricing.xls, for example, I have the following in a standard
module:
Sub ExportAsHTML1()
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"T:\Health Measures\Reports\Pricing.htm", "Pricing", _
"$A$1:$O$21", xlHtmlStatic, "Pricing", "").Publish (True)
ChDir "T:\Health Measures\Pricing"
Application.DisplayAlerts = True
End Sub
This makes the range A1:O21 save as html whenever the document is saved
conventionally.
So, there you have it. Actually wasn't that hard. Let me know if you
have any problems with it, but it's working splendidly over here. Keep
in mind, though, that if Excel is open when the scheduled task is set
to run, whatever you were working on will be closed, because the macro
is actually shutting down excel. Everything should be automatically
saved, but keep that in mind.
--
Danimagus
------------------------------------------------------------------------
Danimagus's Profile:
http://www.excelforum.com/member.php...o&userid=23894
View this thread:
http://www.excelforum.com/showthread...hreadid=375298