Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HTML Code from Excel Cell into Webpage 2

Status
Not open for further replies.
Phudsen

Phudsen

Technical User
Mar 7, 2003
136
A2
Hi,

I have an excel sheet which has two columns.

Column 1: PageName
Column 2: HTMLCode

PageName has information like: OurServices.htm which is the name of the HTML page.
HTMLCode clumn has actual HTML Code like:


<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<p><b><font size="5">Main Page</font></b></p>
<p> </p>
<p><b>Main Menu</b></p>
<p>Link 1</p>
<p>Link 2</p>
<p>Link 3</p>

</body>


Is there a macro that can:

1- Pick-up Page Name from Cell A2 (e.g: ContactUs.htm)
2- Pick-up HTML Code from Cell B2
3- Create a WebPage called ContactUs.htm in an external folder

Thank you
Paulin
 
Sort by date Sort by votes
Take a look at the FileSystemObject (aka fso).
You may have to reference the Microsoft Script Runtime.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Hi PHV,

Can it be done using FileSystemObject?

Thanks
 
Upvote 0 Downvote
How about something like this

i = 1
Do Until IsEmpty(Cells(i, 1))
HTML_FILE_NAME = Cells(i, 1).Value
HTML_FILE_CONTENT = Cells(i, 2).Value
Open "C:\" & HTML_FILE_NAME & ".html" For Output As #1
Print #1, HTML_FILE_CONTENT
Close #1
i = i + 1
Loop

Starts on row 1 and iterates down writing html files with the content in column B until there is an empty cell in column A. The pages are save with the name in column A.
 
Upvote 0 Downvote
Hi dayarwood,

Thank you for your reply, it looks promising:

1- Shall I put the code in Excel as a Macro (VBA Module)
2- Can I save the pages in a special folder in C:, let's say I created a folder with the name HTMLPages in driver C:\ the root directory?

Thank you
Paulin
 
Upvote 0 Downvote
Hi dayarwood,

FANTASTIC

Now it is working like a charm, I made little modification for two reasons:

1- I want the files to be saved in a folder
2- The page name already has .htm

See the modified code which is fantastic:


Sub ToHTML()
i = 1
Do Until IsEmpty(Cells(i, 1))
HTML_FILE_NAME = Cells(i, 1).Value
HTML_FILE_CONTENT = Cells(i, 2).Value
Open "C:\HTMLPages\" & HTML_FILE_NAME For Output As #1
Print #1, HTML_FILE_CONTENT
Close #1
i = i + 1
Loop

End Sub


Thank you dayarwood, Thank you , Thank you
Paulin
 
Upvote 0 Downvote
Hi dyarwood,

Would you please explain the following in the code?

1- & HTML_FILE_NAME For Output As #1
2- Print #1, HTML_FILE_CONTENT
3- Close #1
1,1 is equal to A1 right?

Thanks
Paulin
 
Upvote 0 Downvote
Open "C:\HTMLPages\" & HTML_FILE_NAME For Output As #1


I will break this down

To open a file to write to use

<b>Open</b>

Then you are required to give it a file name

<b>"C:\blahblahblah.html"</b>

However you already have defined the name in the variable HTML_FILE_NAME (which was got from the spreadshet). To combine a string with a string in a variable use <b>&</b>

eg "C:\" & HTML_FILE_NAME gives

(if HTML_FILE_NAME = "myfile.html")

"C:\myfile.html"

As Output means open a new file (overwrite any existing with that name) and write to it.

FYI There is also append which does not overwrite existing files.

#1 is the file number of the file

Print #1 HTML_FILE_CONTENT

You have already put the html string (from the spreadsheet) into the variable HTML_FILE_CONTENT

To write this to the file you use <b>Print</b>.

Then you must specify which file to write to (you may have more than one open) so use the <b>#1</b>

Then you specify the string to write, in this case it is the string contained within the variable HTML_FILE_CONTENT

Finally if ou have opened a file using this method you must close it.

To do this you use <b>Close</b>

Then you must specify the file to close <b>#1</b>.

Let me know if you need more.
 
Upvote 0 Downvote
Hi dyarwood,

Then when the loop continues #1 becomes #2 and so on?

How to make this Macro available to every excel spreadsheet I create, instead of creating the macro every time.

Thank you for your astonishing support dyarwood.

Paulin
 
Upvote 0 Downvote
When the loop occurs because you have closed the file with Close #1 the new file can use #1.

The is a FreeFile function ( i think). I can check that for you tomorrow as I have not used it much but I have it at my work.

If you save this macro in the PERSONAL.xls workbook it can be used for any file you create. (provided the sheet you want it to work with is active)
 
Upvote 0 Downvote
Thank you dyarwood, thanks a million

Paulin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
564
remeng
remeng
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
507
strongm
strongm
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
1K
dhookom
dhookom
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
199
strongm
strongm
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
333
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top