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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Bill,
Thanks! Currently it looks like there arent any problems, I'll do some more testing just to be sure.
Thanks! Currently it looks like there arent any problems, I'll do some more testing just to be sure.
ASKER
Everything seems to be working! Thank you very much!
ASKER
Thank you very much! Script worked as intended!
Great, glad that was helpful.
»bp
»bp
ASKER
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
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
~bp
ASKER
Oh, this should be correct now
FR1.xls
FR1.xls
ASKER
Oops, it was being saved as an HTML, my bad
FR.xls
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.
»bp
' Open output html file
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True, -1)
»bp
ASKER
Thank you, that fixed the problem!
Great!
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