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?


--
Danimagus
------------------------------------------------------------------------
Danimagus's Profile: http://www.excelforum.com/member.php...o&userid=23894
View this thread: http://www.excelforum.com/showthread...hreadid=375298

Automatic Saving as Web Page
 

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

--
Danimagu
-----------------------------------------------------------------------
Danimagus's Profile: http://www.excelforum.com/member.php...fo&userid=2389
View this thread: http://www.excelforum.com/showthread.php?threadid=37529

Automatic Saving as Web Page
 

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

Automatic Saving as Web Page
 

I'm out of the country right now, and won't be back at work for anothe
few days but will post the code I came up with when I do return.
believe it was inspired largely by Ozgrid's site. Run a google searc
in the meantime with ozgrid in the search terms

--
Danimagu
-----------------------------------------------------------------------
Danimagus's Profile: http://www.excelforum.com/member.php...fo&userid=2389
View this thread: http://www.excelforum.com/showthread.php?threadid=37529

Automatic Saving as Web Page
 

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