Automatic Saving as Web Page

Hello all--Judging by the subject headings I see in this forum, I can tell that my problem is a relatively simple one. Well, hopefully, anyway.

I suppose I ought to simply say what I'd like to have Excel do and perhaps some of you could tell me how that might be accomplished.

Currently, I've successfully created a network of spreadsheets, where the value of one cell is pulled and copied into another cell in another spreadsheet (that value is then used to create a graph). The goal is to export that spreadsheet (the one containing the pulled value and resulting graph) as an html document, and to have this happen automatically.

So far, I've created a macro that saves any number of spreadsheets to html whenever it's run, but this isn't quite good enough. I'd like to have this document save as html automatically, whenever there is a change to one of the source spreadsheets (the ones from which cells are copied).

Does anyone know how this might be done?

I've managed to get the files to save as html after either a certain amount of time or at a certain time. Now, immensely awesome a feat as this is, I'd still like to create a macro that causes files to be saved as html whenever they're changed and saved in the conventional sense. Suggestions?

How did you do this as I am looking for the exact thing you are.

"Danimagus" wrote:

>
> I've managed to get the files to save as html after either a certain
> amount of time or at a certain time. Now, immensely awesome a feat as
> this is, I'd still like to create a macro that causes files to be saved
> as html whenever they're changed and saved in the conventional sense.
> Suggestions?
>
>
> --
> Danimagus
> ------------------------------------------------------------------------
> Danimagus's Profile: http://www.excelforum.com/member.php...o&userid=23894
> View this thread: http://www.excelforum.com/showthread...hreadid=375298
>
>

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.