How to put HTML code into Excel VBA as plain text?

Colin Hayes

unread,

Jan 11, 2003, 10:04:10 AM1/11/03

to

Hi all

I need some technical assistance. Does anybody know how to put HTML code
into Excel VBA as plain text? I'm putting a short piece of HTML into VBA
because I want to auto fill an excel column before concatenating with
variable text in a second column. Unfortunately , the VBA code is giving
an error because it's recognizing the HTML as code rather than as text -
anybody know a way round this? Essentially , when I paste the HTML into
the VBA or indeed in to a cell on a work sheet I want it to be inert ,
and I don't want excel to react to the coding itself.

Is this possible?

Best Wishes

Colin

Myrna Larson

unread,

Jan 11, 2003, 12:39:09 PM1/11/03

to

If you intend that the VBA code paste the HTML instructions into the worksheet, then you just
put that code inside of double quotes and assign it to a variable.

Jon Peltier

unread,

Jan 11, 2003, 10:27:12 PM1/11/03

to

Colin -

Here is a short macro I use to open a text file (i.e., an html file) and put
each line of html into a new cell in Excel:

'' ============================================================
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
Dim 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
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <6w4FNXBq...@chayes.demon.co.uk>, Colin Hayes said...

Colin Hayes

unread,

Jan 12, 2003, 5:08:45 PM1/12/03

to

Hi Jon

Thanks! That's a really neat piece of coding and works a treat.

My problem is now being able to paste the HTML into the VBA coding so
that it will in turn paste it into the worksheet. At the moment , I'm
pasting it in and it's trying to interpret it , rather than just
accepting it as plain text. I'm beginning to think it can't be done.

Here's some sample HTML which I would like to have the VBA paste into a
cell in the worksheet and then autofill for a set number of lines :

p>
<TABLE BORDER="4" BORDERCOLOR="#99CCFF" CELLPADDING="5" CELLSPACING="5"
WIDTH="668" ALIGN="CENTER">
<tr>
<td width="675" bordercolor="#0000FF" bordercolorlight="#0000FF"
bordercolordark="#000080" align="center" bgcolor="#FFFFCC">   UK Stateside 
1962 Sheet Music original + Pic - Condition is excellent

! Learn how to play the music. Ideal for Framing! 
 </td> </tr> <TR> <TD BGCOLOR="#FFFFCC" width="636"> 
  Rare item
in nice condition.
 
<hr noshade size="1" color="#F2F2F2"> <center>PAYMENT
INFORMATION</center>
<center>I accept the following payment
methods:</center>
<center>

<img src="http://lotwatch.co.uk/freead/images/d_credit.gif" alt="I
accept Credit Cards" border="0" vspace="0"> <img
src="http://lotwatch.co.uk/freead/images/d_debit.gif" alt="I accept

Debit Cards" border="0" vspace="0">
<img src="http://lotwatch.co.uk/freead/images/d_nochex.gif" alt="I
accept NOCHEX" border="0" vspace="0"> <img
src="http://lotwatch.co.uk/freead/images/d_cheques.gif" alt="I accept
Cheques/P.O." border="0" vspace="0"> <img
src="http://lotwatch.co.uk/freead/images/d_paypal.gif" alt="I
accept PayPal" border="0" vspace="0">

</center>
<hr noshade size="1" color="#F2F2F2">
<center>
 
 




 Check
My Rating - Bid With Confidence!   </TABLE>

When I try to paste it into the VBA it just won't accept it.

If you can help further I'd be in your debt.

Best Wishes

Colin

In article <#aW3$peuCHA.1656@TK2MSFTNGP09>, Jon Peltier
<jonpe...@yahoo.com> writes

You can browse and buy direct from my full list of items at these addresses :

http://www.chayesmusic.com

or :

http://www.netsounds.com/chayes

or:

http://chayes.musicstack.com

To DOWNLOAD the full catalogue click here :

http://www.chayes.demon.co.uk/chayes_full_catalogue.exe

Best Wishes ,

Colin Hayes.

TEL / FAX : (UK) (0)208 804 9181

David McRitchie

unread,

Jan 12, 2003, 8:47:22 PM1/12/03

to

Hi Colin,
To paste into a cell you would paste it into the formula bar.

To do the same with VBA,
There is a limit of about 23 continuation lines, can't find this
limit in help for vbe

Paste what you want to copy into Excel into cell A1 not address line
your example will fill in cells A1:A48

in cell B1 paste the following formula
="""" & SUBSTITUTE(A1,"""", """""") & """ & _"

place A1:A48 into name box and then Ctrl+D
(same as using fill-handle)

Dim PasteBigHTML()
Dim v(10) as string
v(1) = ...paste B1 to B23 here
and remove last underscore
v(2)= ...paste B24 to B46 here
and remove last underscore

V(3) = ...paste B46 to B48 here
and remove last underscore

activecell.value = v(1) & v(2) & v(3)
end sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Colin Hayes" <Co...@chayes.demon.co.uk> wrote in message news:5dJVWLAt...@chayes.demon.co.uk...

> Hi Jon
>
> Thanks! That's a really neat piece of coding and works a treat.
>
> My problem is now being able to paste the HTML into the VBA coding so
> that it will in turn paste it into the worksheet. At the moment , I'm
> pasting it in and it's trying to interpret it , rather than just
> accepting it as plain text. I'm beginning to think it can't be done.
>
> Here's some sample HTML which I would like to have the VBA paste into a
> cell in the worksheet and then autofill for a set number of lines :

[clipped]

Colin Hayes

unread,

Jan 13, 2003, 1:37:08 PM1/13/03

to

Hi David

Thanks for that. I tried it out and it worked fine! I'm really grateful
for tour help on this. I've been agonising over this for ages.

I've now spent some time incorporating the VBA routine generated by your
routine into my VBA coding and all works like a dream!

Isn't technology wonderful?

Thanks again

Colin

In article <u#IR5YquCHA.2796@TK2MSFTNGP12>, David McRitchie
<dmcri...@msn.com> writes

>"Colin Hayes" <Co...@chayes.demon.co.uk> wrote in message news:5dJVWLAtd
>eI+...@chayes.demon.co.uk...

>> Hi Jon
>>
>> Thanks! That's a really neat piece of coding and works a treat.
>>
>> My problem is now being able to paste the HTML into the VBA coding so
>> that it will in turn paste it into the worksheet. At the moment , I'm
>> pasting it in and it's trying to interpret it , rather than just
>> accepting it as plain text. I'm beginning to think it can't be done.
>>
>> Here's some sample HTML which I would like to have the VBA paste into a
>> cell in the worksheet and then autofill for a set number of lines :
>[clipped]
>
>

David McRitchie

unread,

Jan 13, 2003, 2:17:55 PM1/13/03

to

Hi Colin,
Really glad to hear that, I thought I was completely off base.

"Colin Hayes" <Co...@chayes.demon.co.uk> wrote....