MeiR_ct
09-03-2010, 08:18 AM
Hi all.
I'm trying to create detailed html report pages based on excel data.
I know this is a long post, but it's only for the clearness of my request, and for the helpers to understand it easily.
I have some general knowledge in programming, but no exprience with vba, so sorry in advanced for the incorrect code examples I'll provide for explaining my requests.
We are a company that collect data for taxation needs, for municipal councils.
A good example is advertising signs. Here's a little view to how we keep the data in Excel:
ht tp://i33.tinypic.com/2q8dpoi.jpg
First of all, I want the method to "remember" the coulmns by their titles (they'll be always the same), and "know" where to find the needed data.
I guess it'll be a loop running on line 1, and storing in variables the location of the recognized columes, e.g.:
colindex = 1
do while (cell(line 1, colindex).text != "Business Name")
next colindex
loop
BusNameCol = colindex
colindex = 1
do while (cell(line 1, colindex).text != "Sign Content")
next colindex
loop
SignContentCol = colindex
etc. etc.
Now, I need a loop to run through the lines, and create for every businnes owner his own report page.
Every html page will contain the details of the owner and businnes at the top, and a table with the signs data at the bottom (which will be created by another loop, I assume).
As you can see, the refering lines to one businnes might not be in sequence, so the method should "collect" the matching ones, according to current handled "Property ID".
I'm uncertain if I want it to collect from the whole sheet, or from a range of next, lets say 200 lines.
I think there should be a "check" coulmn that the method will put "1" in its cells, near every line it has already used.
>>> Please notice line 12 in the picture, which lacks the owner and property data, and has only the signs data. I still don't know what would I do in this case, I assume it'll need to "collect" according to business name instead of property id.
If you can, please make the code also to prompt a box that tells about this kind of business.
You may found this link useful for creating the html files and writing into them: ht tp://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=436
I've played with this a little bit, but couldn't find a way to print multiple lines (with line break) to the file by the same command. Perhaps it should be:
Print #iFileNum, _
"line 1 line 1 line 1 line 1 line 1" _
& vbCrLf & "line 2 line 2 line 2 line 2 line 2" _
& vbCrLf & "line 3 line 3 line 3 line 3 line 3" _
& vbCrLf & "etc." But that will require a decent work, since I've already created the html code, and meant to copy & paste it somehow into the vba code, and just replacing the needed parts with the variables and quotation marks, something like:
"<html>
<head>
<title>Report page for " + cell(currentLine, OwnerIDCol) + " - " + cell(currentLine, OwnerNameCol) + "</title>
</head>
<body>
<p>Business Name:</p>
<div id=\"BusinessName\">" + cell(currentLine, BusNameCol).text + "</div>
<p>Property ID:</p>
<div id=\"BusinessName\">" + cell(currentLine, PropIDCol).text + "</div>
<p>Owner Name:</p>
<div id=\"BusinessName\">" + cell(currentLine, OwnerNameCol).text + "</div>
<p>Owner ID:</p>
<div id=\"BusinessName\">" + cell(currentLine, OwnerIDCol).text + "</div>
... etc. ...
<table>
<tr>
<td>Sign Content</td> <td>Width</td> <td>Height</td> <td>Location</td> <td>etc. etc.</td>
</tr>"
---- LOOP THAT CREATES LINES IN THE TABLE OF SIGNS ----
"</table>
</body>
</html>"
Hope you understood more or less the thing =]
Post a reply if you have some clarifying questions.
Thanks *A LOT* in advance for all the helpers!!! :)
=-= MeiR =-=
I'm trying to create detailed html report pages based on excel data.
I know this is a long post, but it's only for the clearness of my request, and for the helpers to understand it easily.
I have some general knowledge in programming, but no exprience with vba, so sorry in advanced for the incorrect code examples I'll provide for explaining my requests.
We are a company that collect data for taxation needs, for municipal councils.
A good example is advertising signs. Here's a little view to how we keep the data in Excel:
ht tp://i33.tinypic.com/2q8dpoi.jpg
First of all, I want the method to "remember" the coulmns by their titles (they'll be always the same), and "know" where to find the needed data.
I guess it'll be a loop running on line 1, and storing in variables the location of the recognized columes, e.g.:
colindex = 1
do while (cell(line 1, colindex).text != "Business Name")
next colindex
loop
BusNameCol = colindex
colindex = 1
do while (cell(line 1, colindex).text != "Sign Content")
next colindex
loop
SignContentCol = colindex
etc. etc.
Now, I need a loop to run through the lines, and create for every businnes owner his own report page.
Every html page will contain the details of the owner and businnes at the top, and a table with the signs data at the bottom (which will be created by another loop, I assume).
As you can see, the refering lines to one businnes might not be in sequence, so the method should "collect" the matching ones, according to current handled "Property ID".
I'm uncertain if I want it to collect from the whole sheet, or from a range of next, lets say 200 lines.
I think there should be a "check" coulmn that the method will put "1" in its cells, near every line it has already used.
>>> Please notice line 12 in the picture, which lacks the owner and property data, and has only the signs data. I still don't know what would I do in this case, I assume it'll need to "collect" according to business name instead of property id.
If you can, please make the code also to prompt a box that tells about this kind of business.
You may found this link useful for creating the html files and writing into them: ht tp://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=436
I've played with this a little bit, but couldn't find a way to print multiple lines (with line break) to the file by the same command. Perhaps it should be:
Print #iFileNum, _
"line 1 line 1 line 1 line 1 line 1" _
& vbCrLf & "line 2 line 2 line 2 line 2 line 2" _
& vbCrLf & "line 3 line 3 line 3 line 3 line 3" _
& vbCrLf & "etc." But that will require a decent work, since I've already created the html code, and meant to copy & paste it somehow into the vba code, and just replacing the needed parts with the variables and quotation marks, something like:
"<html>
<head>
<title>Report page for " + cell(currentLine, OwnerIDCol) + " - " + cell(currentLine, OwnerNameCol) + "</title>
</head>
<body>
<p>Business Name:</p>
<div id=\"BusinessName\">" + cell(currentLine, BusNameCol).text + "</div>
<p>Property ID:</p>
<div id=\"BusinessName\">" + cell(currentLine, PropIDCol).text + "</div>
<p>Owner Name:</p>
<div id=\"BusinessName\">" + cell(currentLine, OwnerNameCol).text + "</div>
<p>Owner ID:</p>
<div id=\"BusinessName\">" + cell(currentLine, OwnerIDCol).text + "</div>
... etc. ...
<table>
<tr>
<td>Sign Content</td> <td>Width</td> <td>Height</td> <td>Location</td> <td>etc. etc.</td>
</tr>"
---- LOOP THAT CREATES LINES IN THE TABLE OF SIGNS ----
"</table>
</body>
</html>"
Hope you understood more or less the thing =]
Post a reply if you have some clarifying questions.
Thanks *A LOT* in advance for all the helpers!!! :)
=-= MeiR =-=