Programmatically create html as input to Excel

[scottb2]

I'm trying to use fixed format information in a file (used to be printed) to
Excel 2003. One goal is to try to make the spreadsheet look as close to the
data file as possible. I'm also trying to disturb the user's environment as
little as possible and drive Excel from the program. I'm currently filling
in the proper cells with the right data but have some limitations that I'd
like to overcome.

Can I tell Excel to use a fixed-sized font?

Can I tell Excel to use a red background for selected cells without knowing
the cell number?

A column may not always have numbers (perhaps column headers, etc.) Can I
indicate that if a cell in column 10, 11, or 13 is numeric, it should be
formatted to always have two decimal places? It should still remain a number
and not be converted to text, kind of like using
Format->Cell->Number->Currency.

Sometimes a number (250.00 and -332.11, both with following spaces) will not
be converted to a number. The cell has an error message of "The number in
this cell is formatted as text or preceeded by an apostrophe." What do I
need to do to get rid of the error?

I tried preceeding all numbers in the column with an "=" so the numbers
ended up being "= 250.00" [space follows the equal] or "=-322.11" [minus sign
replaced the space"]. That caused a different error on other cells: "The
formula in this cell differs from the formulas in this area of the
spreadsheet." Interestingly enough, the same column has one value of 6.02
with the 2nd error message and one value of 6.02 without.

The solution to this whole formatting mess may be to format all numbers as
text and let the user format them to be numeric if that's needed.

I know this is a bunch of questions. Thanks for your time.
Scott

 

[JLGWhiz]

I suggest you read the information at this site then re-post.

http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.aspx

 

[scottb2]

Let me run down many of the items in the site you referred me to:

I posted to one newsgroup.
I didn't cross-post or multi-post.
I thought I wrote clearly. Is this the problem?
I'm being nice in my post.
I tried to economize my post. Is this the problem?
No code is needed so I didn't post any.
I didn't use any company slang or lingo.
I used a meaningful subject.
I gave specific error messages.
Top posting wasn't a problem but I'm doing it now.
I used plain text.
I indicated "Excel 2003".

What am I doing wrong that prompted you to tell me about the site suggestions?
Scott

I suggest you read the information at this site then re-post.

http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.aspx

I'm trying to use fixed format information in a file (used to be printed) to
Excel 2003. One goal is to try to make the spreadsheet look as close to the
data file as possible. I'm also trying to disturb the user's environment as
little as possible and drive Excel from the program. I'm currently filling
in the proper cells with the right data but have some limitations that I'd
like to overcome.

Can I tell Excel to use a fixed-sized font?

Can I tell Excel to use a red background for selected cells without knowing
the cell number?

A column may not always have numbers (perhaps column headers, etc.) Can I
indicate that if a cell in column 10, 11, or 13 is numeric, it should be
formatted to always have two decimal places? It should still remain a number
and not be converted to text, kind of like using
Format->Cell->Number->Currency.

Sometimes a number (250.00 and -332.11, both with following spaces) will not
be converted to a number. The cell has an error message of "The number in
this cell is formatted as text or preceeded by an apostrophe." What do I
need to do to get rid of the error?

I tried preceeding all numbers in the column with an "=" so the numbers
ended up being "= 250.00" [space follows the equal] or "=-322.11" [minus sign
replaced the space"]. That caused a different error on other cells: "The
formula in this cell differs from the formulas in this area of the
spreadsheet." Interestingly enough, the same column has one value of 6.02
with the 2nd error message and one value of 6.02 without.

The solution to this whole formatting mess may be to format all numbers as
text and let the user format them to be numeric if that's needed.

I know this is a bunch of questions. Thanks for your time.
Scott

 

[Tim Williams]

How are you getting the information into Excel ? Are you using a macro or
some other method ?

ActiveCell.Font.Name = "Courier" 'proportional font
ActiveCell.Interior.Color = vbRed shading

ActiveSheet.Columns(10).NumberFormat = "0.00"

'replace trailing/leading spaces and single quotes
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
c.Value = Trim(c.Value)
c.Value = Replace(c.Value, "'", "")
Next c

Your best bet is to turn on the macro recorder and do some formatting, then
look at the code.

Tim

 

[scottb2]

Tim,

The data file used to be output sent directly to a printer. It is now sent
to a hard disc file to be read online. The current program, written in
assembler and invoked by IE for example, reads the data file and writes
either HTML or text lines to stdio back to the browser which displays the
data.

I'm enhancing this program to have Excel be a third target. The HTML
CONTENT-TYPE it creates invokes the Excel application. The program also
formats the data into columns by using <table><td>line1 data</td><td>line2
data</td>...</table>. The formatting seems to work very well except for the
problems I listed at the first post.

If you're wondering why assembler, the shop has an entire environment built
around assembler code running under Linux. There's a lot of history. So,
running VB code isn't an option. This is why I'm asking how to do this from
the perspective of an HTML file as input to Excel.

From HTML (as input to Excel), I don't know how to force the use of a fixed
sized font, etc.

Some of the problems I listed may be Excel problems when using HTML as input
(number formatting issues). Maybe Excel just doesn't do what I'm needing
(using the font I want).

Thanks,
Scott

How are you getting the information into Excel ? Are you using a macro or
some other method ?

ActiveCell.Font.Name = "Courier" 'proportional font
ActiveCell.Interior.Color = vbRed shading

ActiveSheet.Columns(10).NumberFormat = "0.00"

'replace trailing/leading spaces and single quotes
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
c.Value = Trim(c.Value)
c.Value = Replace(c.Value, "'", "")
Next c

Your best bet is to turn on the macro recorder and do some formatting, then
look at the code.

Tim

I'm trying to use fixed format information in a file (used to be printed)
to
Excel 2003. One goal is to try to make the spreadsheet look as close to
the
data file as possible. I'm also trying to disturb the user's environment
as
little as possible and drive Excel from the program. I'm currently
filling
in the proper cells with the right data but have some limitations that I'd
like to overcome.

Can I tell Excel to use a fixed-sized font?

Can I tell Excel to use a red background for selected cells without
knowing
the cell number?

A column may not always have numbers (perhaps column headers, etc.) Can I
indicate that if a cell in column 10, 11, or 13 is numeric, it should be
formatted to always have two decimal places? It should still remain a
number
and not be converted to text, kind of like using
Format->Cell->Number->Currency.

Sometimes a number (250.00 and -332.11, both with following spaces) will
not
be converted to a number. The cell has an error message of "The number in
this cell is formatted as text or preceeded by an apostrophe." What do I
need to do to get rid of the error?

I tried preceeding all numbers in the column with an "=" so the numbers
ended up being "= 250.00" [space follows the equal] or "=-322.11" [minus
sign
replaced the space"]. That caused a different error on other cells: "The
formula in this cell differs from the formulas in this area of the
spreadsheet." Interestingly enough, the same column has one value of 6.02
with the 2nd error message and one value of 6.02 without.

The solution to this whole formatting mess may be to format all numbers as
text and let the user format them to be numeric if that's needed.

I know this is a bunch of questions. Thanks for your time.
Scott

 

[Tim Williams]

Create a small example report in Excel using the formatting you want - then
export to HTML
and look at the source of the file. That should give you some idea of how
to go about this.

In the past I've used HTML exported from Excel as a template and just
replaced tokens with the actual content.

Tim

Tim,

The data file used to be output sent directly to a printer. It is now
sent
to a hard disc file to be read online. The current program, written in
assembler and invoked by IE for example, reads the data file and writes
either HTML or text lines to stdio back to the browser which displays the
data.

I'm enhancing this program to have Excel be a third target. The HTML
CONTENT-TYPE it creates invokes the Excel application. The program also
formats the data into columns by using <table><td>line1 data</td><td>line2
data</td>...</table>. The formatting seems to work very well except for
the
problems I listed at the first post.

If you're wondering why assembler, the shop has an entire environment
built
around assembler code running under Linux. There's a lot of history. So,
running VB code isn't an option. This is why I'm asking how to do this
from
the perspective of an HTML file as input to Excel.

From HTML (as input to Excel), I don't know how to force the use of a
fixed
sized font, etc.

Some of the problems I listed may be Excel problems when using HTML as
input
(number formatting issues). Maybe Excel just doesn't do what I'm needing
(using the font I want).

Thanks,
Scott

How are you getting the information into Excel ? Are you using a macro or
some other method ?

ActiveCell.Font.Name = "Courier" 'proportional font
ActiveCell.Interior.Color = vbRed shading

ActiveSheet.Columns(10).NumberFormat = "0.00"

'replace trailing/leading spaces and single quotes
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
c.Value = Trim(c.Value)
c.Value = Replace(c.Value, "'", "")
Next c

Your best bet is to turn on the macro recorder and do some formatting,
then
look at the code.

Tim

I'm trying to use fixed format information in a file (used to be
printed)
to
Excel 2003. One goal is to try to make the spreadsheet look as close
to
the
data file as possible. I'm also trying to disturb the user's
environment
as
little as possible and drive Excel from the program. I'm currently
filling
in the proper cells with the right data but have some limitations that
I'd
like to overcome.

Can I tell Excel to use a fixed-sized font?

Can I tell Excel to use a red background for selected cells without
knowing
the cell number?

A column may not always have numbers (perhaps column headers, etc.)
Can I
indicate that if a cell in column 10, 11, or 13 is numeric, it should
be
formatted to always have two decimal places? It should still remain a
number
and not be converted to text, kind of like using
Format->Cell->Number->Currency.

Sometimes a number (250.00 and -332.11, both with following spaces)
will
not
be converted to a number. The cell has an error message of "The number
in
this cell is formatted as text or preceeded by an apostrophe." What do
I
need to do to get rid of the error?

I tried preceeding all numbers in the column with an "=" so the numbers
ended up being "= 250.00" [space follows the equal] or "=-322.11"
[minus
sign
replaced the space"]. That caused a different error on other cells:
"The
formula in this cell differs from the formulas in this area of the
spreadsheet." Interestingly enough, the same column has one value of
6.02
with the 2nd error message and one value of 6.02 without.

The solution to this whole formatting mess may be to format all numbers
as
text and let the user format them to be numeric if that's needed.

I know this is a bunch of questions. Thanks for your time.
Scott