Macro: Setting a dynamic range for Save as Web Page

[Chris Beall]

Excel 2000.

After creating a new worksheet with a macro, I want to save the contents
as an HTML page. I'm new to VB, so I've been using the macro recorder,
then tweaking the output.

Here's the problem. I'm trying to save just a range of cells, from A1
to the xlCellTypeLastCell, containing data pasted in by the macro. If I
just record what I'm doing manually, ActiveWorkbook.PublishObjects.Add
uses an absolute range, like $A$1:$J$1253. The $A$1 is OK, but the
second value will vary from time to time as the dimension of the pasted
data changes.
So I tried jamming in $A$1:xlCellTypeLastCell, which caused VB to barf.
But I knew I could use a named (single-cell) range, so I did a GoTo
Special -> Last cell and added a range name to that cell, then used that
as the starting point for a range selection. The macro recorded this as:

Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveWorkbook.Names.Add Name:="Last_cell", RefersToR1C1:= _
"=Inventory!R61C10"

But R61C10 is an absolute reference, so that's no good.

What I seem to need is a way to refer to the currently-selected cell
when setting the range name, something like:
ActiveWorkbook.Names.Add Name:="Last_cell", RefersToR1C1:= _
"=Selected"

Does such a thing exist?

Chris Beall

 

[Guest]

hi,
if you have a solid block of data then try this...
Range("A1", Range("A1").end(xldown).offset(0,10)).select
the offset is the number of row -1 and the number of columns -1
another way
Range("A1", Range("A1").end(xldown).end(xltoright)).select
another way
cells(1,1).select
Activecell.currentregion.select

regards
FSt1

 

[Chris Beall]

hi,
if you have a solid block of data then try this...
Range("A1", Range("A1").end(xldown).offset(0,10)).select
the offset is the number of row -1 and the number of columns -1
another way
Range("A1", Range("A1").end(xldown).end(xltoright)).select
another way
cells(1,1).select
Activecell.currentregion.select

regards
FSt1

FSt1,

OK, I think we're VERY close here. The problem is that I have NO
experience with VB and therefore don't quite know how to apply your
suggestions.

The first and third methods you suggest will work to select the desired
block of cells. (The second one fails because there may be empty cells
in the last row beyond the first column).

What I don't know, and haven't been able to deduce, is the correct
syntax to stuff your suggestion into the macro line that actually saves
the data.

Here's what doesn't work, because of the absolute reference to $J$1027:

ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\My Documents\Inventory.html", _
"Inventory", "$A$1:$J$1027", xlHtmlStatic, "Scratchpad_10763",
"Category: Full") _
.Publish (True)

And here's the template from the Object Browser, which is as close to
documentation as I've found, though I don't claim to understand it all:

Function Add(SourceType As XlSourceType, Filename As String, [Sheet],
[Source], [HtmlType], [DivID], [Title]) As PublishObject
Member of Excel.PublishObjects

I've already determined that I can specify the [Source] as
"$A$1:Last_cell", but I couldn't define "Last_cell" without again
getting absolute row numbers.

So, what I need to see is, let's pick your first example above, the
correct syntax for using it in an ActiveWorkbook.Names.Add or directly
in the ActiveWorkbook.PublishObjects.Add.

Sorry to be so obtuse, but VB seems to be a bit unlike anything else
I've seen, so I'm proceeding rather slowly.

Thanks,
Chris Beall