Prev: runtime error code 1004
Next: Getting rid of the green triangles in XL 2010
From: ker_01 on 12 May 2010 19:12
I am pulling data from an automated report. I'm trying to test for every
possible condition, so that my VBA will continue to work for the rest of the
year without additional tweaking.

The target report is produced from an aspx web page (which has an "export to
Excel" option). I do not have control over the source format.

Some data in the exported Excel report is in merged cells. When numbers are
increased (for example, to simulate the growth of YTD numbers) the display
converts to "######" when the digits exceed the cell width for that font
size. When I try to autosize columns by selecting the columns and
double-clicking between the column letters (cursor looks like <-|-> ) which
normally autosizes the columns, nothing happens. I haven't tried to
investigate the root cause; right now I'm assuming it is because of the
merged cells.

I need to make sure that the cell contents are visible, because my code
takes ranges of cells and copies them as a picture to paste into a MS Word
template, at various bookmark locations.

Is there a way in code to either force the autosize in a way that actually
works, or to simply determine whether there are any values being displayed as
"###" so that I can loop and incrementally increase cell width until the
actual value is visible?

I hope that the source aspx page is smart enough to adjust the exported
column widths to adjust for the cell contents, but I'd rather program
conservatively and make sure my VBA handles all possible conditions (at least
the ones I can think of).

Many thanks,
Keith
From: Gary''s Student on 12 May 2010 19:34
Select a cell and run:

Sub PoundTester()
Dim s1 As String, s2 As String
s1 = ActiveCell.Text
If Len(s1) = 0 Then Exit Sub
s2 = Replace(s1, "#", "")
If Len(s2) = 0 Then
MsgBox " activecell is displaying pounds"
End If
End Sub

--
Gary''s Student - gsnu201002


"ker_01" wrote:

> I am pulling data from an automated report. I'm trying to test for every
> possible condition, so that my VBA will continue to work for the rest of the
> year without additional tweaking.
>
> The target report is produced from an aspx web page (which has an "export to
> Excel" option). I do not have control over the source format.
>
> Some data in the exported Excel report is in merged cells. When numbers are
> increased (for example, to simulate the growth of YTD numbers) the display
> converts to "######" when the digits exceed the cell width for that font
> size. When I try to autosize columns by selecting the columns and
> double-clicking between the column letters (cursor looks like <-|-> ) which
> normally autosizes the columns, nothing happens. I haven't tried to
> investigate the root cause; right now I'm assuming it is because of the
> merged cells.
>
> I need to make sure that the cell contents are visible, because my code
> takes ranges of cells and copies them as a picture to paste into a MS Word
> template, at various bookmark locations.
>
> Is there a way in code to either force the autosize in a way that actually
> works, or to simply determine whether there are any values being displayed as
> "###" so that I can loop and incrementally increase cell width until the
> actual value is visible?
>
> I hope that the source aspx page is smart enough to adjust the exported
> column widths to adjust for the cell contents, but I'd rather program
> conservatively and make sure my VBA handles all possible conditions (at least
> the ones I can think of).
>
> Many thanks,
> Keith
 | 
Pages: 1
Prev: runtime error code 1004
Next: Getting rid of the green triangles in XL 2010