ExcelBanter - Literally displaying imported cells with <HTML> and <BODY> tags

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Literally displaying imported cells with and tags (https://www.excelbanter.com/excel-discussion-misc-queries/3467-literally-displaying-imported-cells-%3Chtml%3E-%3Cbody%3E-tags.html)

[email protected]

Literally displaying imported cells with and tags

Hi,
I'm exporting contents to excel from a web application. Each row looks
like this:

"3101" "American Express" "Fort Lauderdale" "FL" "Deployed" "Windows
2001R1" "<![CDATA[<HTML
<BODY
<P
<BIMPORTANT:</B We have updated server. You must click the button
again to receive your account information.
<P
More text goes here
</P
</BODY
</HTML]]"

However, when the file is opened in Excel only the text in the last
column is displayed (the tags and all the other cells are missing). How
do I get excel to just print all columns and the literal contents
without trying to do anything smart? I know if I get rid of the <HTML
and <BODY tags it works, but unfortunately I need to leave them in -
and they need to show up as <HTML and <BODY.
Any ideas?

thanks,
Orlando

Jon Peltier

Orlando -

Here's an ancient piece of code I use for this:

Sub inputTXTfile()
' input text file line by line without interpreting html tabs
' open new sheet first and select home cell

Dim linein As String, fh As Integer, FileName As String, lineNum As Double

FileName = Application.GetOpenFilename
If FileName = "False" Then Exit Sub
Application.ScreenUpdating = False
lineNum = 0
fh = FreeFile
Workbooks.Add

Open FileName For Input As fh
Do Until EOF(fh)
Line Input #fh, linein
ActiveCell.Offset(lineNum, 0) = linein
lineNum = lineNum + 1
Loop
Close #fh

Application.ScreenUpdating = True
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wrote:

Hi,
I'm exporting contents to excel from a web application. Each row looks
like this:

"3101" "American Express" "Fort Lauderdale" "FL" "Deployed" "Windows
2001R1" "<![CDATA[<HTML
<BODY
<P
<BIMPORTANT:</B We have updated server. You must click the button
again to receive your account information.
<P
More text goes here
</P
</BODY
</HTML]]"

However, when the file is opened in Excel only the text in the last
column is displayed (the tags and all the other cells are missing). How
do I get excel to just print all columns and the literal contents
without trying to do anything smart? I know if I get rid of the <HTML
and <BODY tags it works, but unfortunately I need to leave them in -
and they need to show up as <HTML and <BODY.
Any ideas?

thanks,
Orlando

[email protected]

Hi Jon,

Thanks for replying. I'll go figure this out, but if you have a second
can you explain where the Application and ActiveCell objects come from?
I was just writing directly to Response.Write, setting the content type
to excel, and letting Windows just open the file naturally from the
association. It looks like you're directly manipulating excel, which is
an avenue I haven't investigated before.

Thanks again,
Orlando

Dave Peterson

Jon's code is a macro that's run from inside excel.

Application is Excel (itself) and Activecell is a range object within excel.
Activecell represents the cell that's selected (or if the selection is
multi-cells, it represents the "white" one in that selected area).

You may want to read David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

Hi Jon,

Thanks for replying. I'll go figure this out, but if you have a second
can you explain where the Application and ActiveCell objects come from?
I was just writing directly to Response.Write, setting the content type
to excel, and letting Windows just open the file naturally from the
association. It looks like you're directly manipulating excel, which is
an avenue I haven't investigated before.

Thanks again,
Orlando

--

Dave Peterson

All times are GMT +1. The time now is 07:02 PM.