How to save a variable range as an HTML file?

[Father Guido]

Hi,

I'm using Excel 2002, I have figured out how to select a variable
range, but how do I manage to save that same range as an html file?

When I first recorded the macro it put in the range A1:Q21, but the
Q21 part needs to adjust to match the variable selection. Any/all
help is appreciated.

Thanks!!

Norm


1 Application.Goto Reference:="R65536C1"
2 Selection.End(xlUp).Select
3 ActiveCell.Offset(0, 16).Range("A1").Select
4 Range("A1", ActiveCell).Select
5 With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
6 "D:\Profiles\ndillon\Desktop\NBSS15\NBSS15TC.html", "Sheet1",
7 "$A$1:$Q$21", xlHtmlStatic, "NBSS15TC_26753", "")
8 .Publish (True)
9 .AutoRepublish = False
10 End With


Lines 1-4 select the variable range
Lines 5-10 are supposed to save the selection as an html file

 

[Tim Williams]

Try this:


With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\Documents and Settings\Tim\Desktop\Page.htm", _
Selection.Parent.Name, _
Selection.Address(), _
xlHtmlStatic, "divExcelExport", _
"TestTitle")
.Publish (True)
.AutoRepublish = False
End With

But typically there is no need to select anything for this type of
operation.
You could equally well do something like this:

Dim rng As Range
Set rng = ThisWorkbook.Sheets("Sheet1").Range("G11:L12")

and substitute "rng" for "selection" in the code above.



Tim

 

[Father Guido]

Thanks Tim, I'll give it a shot at work tomorrow!

Norm

~Try this:
~
~
~With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
~ "C:\Documents and Settings\Tim\Desktop\Page.htm", _
~ Selection.Parent.Name, _
~ Selection.Address(), _
~ xlHtmlStatic, "divExcelExport", _
~ "TestTitle")
~ .Publish (True)
~ .AutoRepublish = False
~ End With
~
~But typically there is no need to select anything for this type of
~operation.
~You could equally well do something like this:
~
~ Dim rng As Range
~ Set rng = ThisWorkbook.Sheets("Sheet1").Range("G11:L12")
~
~and substitute "rng" for "selection" in the code above.
~
~
~
~Tim
~
~
~
~~> Hi,
~>
~> I'm using Excel 2002, I have figured out how to select a variable
~> range, but how do I manage to save that same range as an html file?
~>
~> When I first recorded the macro it put in the range A1:Q21, but the
~> Q21 part needs to adjust to match the variable selection. Any/all
~> help is appreciated.
~>
~> Thanks!!
~>
~> Norm
~>
~>
~> 1 Application.Goto Reference:="R65536C1"
~> 2 Selection.End(xlUp).Select
~> 3 ActiveCell.Offset(0, 16).Range("A1").Select
~> 4 Range("A1", ActiveCell).Select
~> 5 With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
~> 6 "D:\Profiles\ndillon\Desktop\NBSS15\NBSS15TC.html",
"Sheet1",
~> 7 "$A$1:$Q$21", xlHtmlStatic, "NBSS15TC_26753", "")
~> 8 .Publish (True)
~> 9 .AutoRepublish = False
~> 10 End With
~>
~>
~> Lines 1-4 select the variable range
~> Lines 5-10 are supposed to save the selection as an html file
~>
~