publish as webpage in excel 97

[Guest]

hi all

i have created a macro that at the end publishes as a web page this works
ok if using excel 2000. but with excel 97 it throws an error.i dont have
excel 97 on my machine this is only at work. i see that publish as webpage
is not available i the save as option when doing this manually in excel 97

i have the code

'gets range to create as webpage
Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
'add in formatting for days off
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Day off"""
Selection.FormatConditions(1).Interior.ColorIndex = 40
pubHtml = MsgBox("Do you want to create this as a webpage for easier
viewing?", vbYesNo, "Create as webpage")
If pubHtml = vbYes Then GoTo startPublish
If pubHtml = vbNo Then GoTo startPublish1
Exit Sub
startPublish:

HtmlPubName = InputBox("Enter the name that you want this file saved
as." & vbLf & "" & vbLf & "The file extension 'HTM' will be added for you" &
vbLf & "Please remember this should not contain any spaces", "File Name")
If HtmlPubName = "" Then
MsgBox "Can not be empty, Html page not created, will now
exit!", vbOKOnly, "Empty Name"
Exit Sub
End If

'here is where the problem starts with the different versions
'but not sure if this code in the first part of the If statement will work
on excel 97

If Application.Version < "8.0" Then
ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="H:\" & HtmlPubName & ".htm", _
Sheet:="Rota", _
Source:=Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Address,
_
HtmlType:=xlHtmlStatic).Publish
Range("A1").Select
Else
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="H:\" & HtmlPubName & ".htm", _
Sheet:="Rota", _
Source:=Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Address,
_
HtmlType:=xlHtmlStatic)
.Title = "Rota for " & agent
.Publish (True)
End With
End If
Range("A1").Select
Workbooks(myrotabook & ".xls").Save

MsgBox "Your webpage " & HtmlPubName & ".htm " & vbLf & "can be viewed
at H:\" & HtmlPubName & ".htm ", vbOKOnly, "Web Page saved" ' Define
message.
Exit Sub


i dont have admin rights to add in any files that may make this work on
office 97
i suppose if this is not available then i will just have to create an error
to say that this cant be done in this version
for example MsgBox "Sorry this version does not support Creating a
Webpage", vbOKOnly, "Unsupported Feature"
any ideas on the syntax to do this



thanks in advance

kevin

 

[Tom Ogilvy]

there is no PublishObjects object in Excel 97. You would have to ftp the
files to your location.

 

[Guest]

thanks tom
but i am just wanting to create the file to the root of the local H:\ drive
for viewing

but as i understand this can not be done in xl97 with out an add in am i
correct?

kevin

 

[Tom Ogilvy]

There is no support in Excel 97 for the publishobject object. There is an
addin that allows you to save as HTML and you can specify where to put the
files. Support for programatic use of this addin did not arrive until SR1
of Excel 97. It uses a completely diffenent syntax than the built in
support in xl2000 and later.

You can download the addin (it also contains documentation) at

http://www.microsoft.com/downloads/...06-10A0-42B6-83B2-B21B83E66BD9&displaylang=EN