Link to home
Start Free TrialLog in
Avatar of Bob Tian
Bob Tian

asked on

Excel to HTML macro with inserting lines

Hello,

I have an XLS file,called X that I want to be in HTML. In addition, I want to add some HTML lines on the top and on the bottom of the .html file. I have a HTML file called, H. I want to take a fixed number of lines from X and put them in the XLS after making it into HTML. So it will be something like this:

1. X data has a table
2. H has HTML that I want to include when making X into X.html

The file should be like this:
I want lines 1-176 of H.html into X,
then have X.XLS table data,
then lines 14657 to 14786 of H.html

The lines I need from H.html does not change over time, if that helps.
Avatar of Bill Prew
Bill Prew

You need to be a bit more clear on the question.  It sounds like you want to take two files, an X.xls file, and a H.html file, and merge data from the to produce a new M.html file, is that correct?  And the M.html file will contain data in tables from both H.html and X.xls?

Where do you you want to run this process from, a VBA macro inside X.xls, or someplace else?

Please provide a sample of all three files mentioned above, X.xls, H.html, and M.html so that we can see exactly the data involved and the output you desire.  This will be needed for testing too.


»bp
Avatar of Bob Tian

ASKER

Yes, I want to merge data to create a new HTML. The table in X.xls is what I want, while H.html has html lines that I want to add into the new M.html file, since the X.xls file only has a table. X.xls table is changing and can have more data in it, the sample has a few data.
I do not need the table data from H.html.

The final file will look like this:

<html lines 1-176 from H.html/>
<table data from X.xls/>
<html lines 14657 to 14786 from H.html/>

The lines that I want from H.html do not change, if it helps, could just copy those lines and add an insert to the macro

Ideally the macro can be portable, since the X.xls file is an export from our website, we hope to be able to use the macro on any updated X.xls file when we export as xls.
X.xls
H.html
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey Bill,

Thanks! Currently it looks like there arent any problems, I'll do some more testing just to be sure.
Everything seems to be working! Thank you very much!
Thank you very much! Script worked as intended!
Great, glad that was helpful.


»bp
Hey Bill,

I seem to be having trouble with french excel tables, whereas the english ones work fine. The problem I'm getting is that it doesnt loop through the whole table and just stops at cell #90 with the name "Uracile (moutarde à l’) ou ses sels". The english ones work perfectly, but when it comes to the french tables it doesnt even finish running the script. I've attached the file below.
FR1.xls
That was an HTML file you attached, did you mean to attach the XLS file?

~bp
Oh, this should be correct now
FR1.xls
Oops, it was being saved as an HTML, my bad
FR.xls
Try changing the output file to force unicode mode by adding the -1 as below, that should allow extended characters to be handled better.

' Open output html file
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True, -1)

Open in new window


»bp
Thank you, that fixed the problem!
Great!