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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message oups.com...
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

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 wrote:
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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message oups.com...
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

Try this Chuck


Application.Dialogs(xlDialogImportTextFile).Show

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message ups.com...
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 wrote:
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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message oups.com...
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

Change the path of the txt file it create

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this Chuck


Application.Dialogs(xlDialogImportTextFile).Show

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message ups.com...
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 wrote:
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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"c mateland" wrote in message oups.com...
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