How to paste HTML code as text into a worksheet?

[c mateland]

Excel 2003

In Internet Explorer, I view the source code of my Web page in Notepad,
then copy that text and paste it into an Excel worksheet.

Excel gives me only two choices in Paste Special (text and unicode),
both of which causes a problem. They both paste as interpreted HTML
code and so my worksheet tries to render as a Web page. I don't want
that.

So after pasting and after the rendering, I click the paste option
button that appears, and choose "Use text import wizard". In the wizard
I choose "Fixed Width" and click Finish. The HTML code now appears in
my worksheet correctly as HTML code (text).

I want this clipboard-pasting routine in VBA, but can't figure it out.
It won't record the paste option button, so that's no help.

How do I paste using VBA as described above or using another paste
method where I get all the source code text on my clipboard into a
worksheet as text?

Many thanks,
Chuck

 

[Ron de Bruin]

Hi c mateland

Save the Notepad file (txt)
On this page there is a code example to open the txt file in Excel with VBA

http://www.rondebruin.nl/csv.htm

See this part
Workbooks.OpenText Filename

Record a macro when you do it manual to see the code you need

 

[c mateland]

Is there any way to do this straight from the clipboard without first
saving in Notepad as a txt file?

This routine is in the middle of a larger macro. While focus is on
Notepad, it's easy to select all and copy and close Notepad using VBA,
but I'm not sure how I would use VBA to save the txt file while the
focus is on Notepad (save as, navigate to folder, name file, save).

Any solutions?

Thanks,
Chuck

 

[Ron de Bruin]

Try this Chuck


You can change this line to the Workbooks.OpenText Filename........................
to import it automatic



Sub Create_TXT_File()
Dim myFileName As String
Dim FileNum As Long

myFileName = "C:\Data\ron.txt"

FileNum = FreeFile
Close FileNum
Open myFileName For Output As FileNum

Print #FileNum, GetSource("http://www.rondebruin.nl")

Close FileNum

Application.Dialogs(xlDialogImportTextFile).Show

End Sub


Function GetSource(sURL As String) As String
'Tim Williams
Dim oXHTTP As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", sURL, False
oXHTTP.send
GetSource = oXHTTP.responsetext
Set oXHTTP = Nothing

End Function

 

[Ron de Bruin]

Change the path of the txt file it create

myFileName = "C:\Data\ron.txt"