How to get address of CurrentRegion for using to publish variable range

[FatherGuido]

Hi,

Windows 2000 & Excel 2002.

I'm trying to publish the current region as html when selected when I'm
in cell A1. Named ranges, current region etc. are NOT acceptable
references, only col/row ranges, so I'm trying to get the address of the
variable Current Region. My range will always start in A1 and go to
column AB, but the row will change.

The efforts below were provide by Tom Ogilvy previously, but none of
them worked for me.

__________________________________

Dim rng as Range
set rng = Worksheets("NBSS1101 CR List").Range("A1").CurrentRegion

Workbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\docs\Departments\2l54\Page.htm", _
Sheet:="NBSS1101 CR List", _
Source:=rng.Address, _
HtmlType:=xlHtmlCalc).Publish
End Sub

___________________________________

Workbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\docs\Departments\2l54\Page.htm", _
Sheet:="NBSS1101 CR List", _
Source:=Range("Test").Address, _
HtmlType:=xlHtmlCalc).Publish
End Sub

___________________________________

Workbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\docs\Departments\2l54\Page.htm", _
Sheet:="NBSS1101 CR List", _
Source:="Test", _
HtmlType:=xlHtmlCalc).Publish
End Sub
___________________________________

I'm getting desparate for a solution. If you can help please feel free
to do so.

Thanks!

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

 

[Patrick Molloy]

It might be useful if you could explain WHY exactly Tom's
code doesn't work for you.

The CurrentRegion property returns the ordinal address
Range("A1").CurrentRegion.Address
eg A1:G10
you could use
..Range("A1").CurrentRegion.Rows.Count
and
..Range("A1").CurrentRegion.Columns.Count
to extract the size.
If you are in A1 and you table starts in say C1, then you
won't get the table in c1 as part of the current region.
Excel starts in the active cell & looks for a table where
the active cell is within the table....the same method it
uses when you try a "sort" for example.

Patrick Molloy
Microsoft Excel MVP

 

[Tom Ogilvy]

possibly

Dim rng as Range
set rng = Worksheets("NBSS1101 CR List").Range("A1").CurrentRegion

Workbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\docs\Departments\2l54\Page.htm", _
Sheet:="NBSS1101 CR List", _
Source:=rng.Address(0,0), _
HtmlType:=xlHtmlCalc).Publish
End Sub

Will produce something like "A1:AB30" which matches what is in the help
example for this Add method.

 

[FatherGuido]

It might be useful if you could explain WHY exactly Tom's
code doesn't work for you.

The CurrentRegion property returns the ordinal address
Range("A1").CurrentRegion.Address
eg A1:G10
you could use
.Range("A1").CurrentRegion.Rows.Count
and
.Range("A1").CurrentRegion.Columns.Count
to extract the size.
If you are in A1 and you table starts in say C1, then you
won't get the table in c1 as part of the current region.
Excel starts in the active cell & looks for a table where
the active cell is within the table....the same method it
uses when you try a "sort" for example.

Patrick Molloy
Microsoft Excel MVP

Thanks Patrick, will give it a try tomorrow. I can't remember the exact
error I got with Tom's code, but all three examples he posted gave me an
error box.

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

 

[FatherGuido]

possibly

Dim rng as Range
set rng = Worksheets("NBSS1101 CR List").Range("A1").CurrentRegion

Workbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\docs\Departments\2l54\Page.htm", _
Sheet:="NBSS1101 CR List", _
Source:=rng.Address(0,0), _
HtmlType:=xlHtmlCalc).Publish
End Sub

Will produce something like "A1:AB30" which matches what is in the help
example for this Add method.

Thanks Tom, I will give this a try tomorrow.

Norm


Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good