MeiR_ct
09-22-2010, 09:04 AM
Hi all.
I'm trying to create detailed report pages based on excel data.
Here's a little view to the sheet:
http://i33.tinypic.com/2q8dpoi.jpg
I've also attached this sample if you want to work on it.
Each property ID will have its own section in the DB, and finally its own report page.
As you can see, the lines that match one property ID, has same owner \ property data, and the difference is in the signs data.
So the reports structure should be: 1. owner and property data 2. collected signs data 3. more text \ data that I should be able to add later to the code as I wish.
At first, I was planning to directly create the reports from excel, a thing that required a collection of matching data for every property, since the lines are not necessarily in sequence. And this is, of course, not efficient.
Here is the thread of the old request: http://www.vbaexpress.com/forum/showthread.php?t=33930
Kenneth, who helped a lot there, suggested to create new file in case of not-yet-handled property, and then to append lines to existing files.
The problem that came up (and that was my fault- I didn't provide enough details at the beginning), is that I wish to add more data to the reports, after all the sign data is collected. (stage 3 in the structure above)
I raised an idea to store first in XML, then to loop on it and create reports, and Kenneth offerd DB which can be handled much better in Office apps.
First of all, I want the method to "remember" some coulmns by their titles (they'll be always the same), and "know" where to find the needed data.
I guess it'll be several loops that search for "title" and strore the column indexes in variables. ("Business Name" into BusNameCol, "Sign Content" into SignContentCol, etc.)
Now, there will be a loop that runs on the sheet and stores data into DB.
I'm not familiar with DB syntax, so I'll provide an XML example as I did in the old thread.
The DB pattern should be the same:
<Property id="166143000003">
<BusinessName>Paz</BusinessName>
<OwnerID>510216054</OwnerID >
<PropertyID>166143000003</PropertyID>
<OwnerName>Paz Company Ltd.</OwnerName>
<OwnerAddress>P.O.B. 222 City</OwnerAddress>
<Sign id="2010004701">
<Content>Tasty Yellow</Content>
<Width>250</Width>
<Height>230</Height>
<Area>6</Area>
<AddressStreet>rd. 434</AddressStreet>
<AddressHouse>21</AddressHouse>
<Location>Display Window</Location>
</Sign>
<Sign id="2010004801">
…
…
</Sign>
etc. etc.
</Property>
<Property id="36100001550">
…
…
…
</Property>
etc.
etc.
Or maybe most of the sign data should be in attributes and the content inside the tag:
<Sign id="2010004701" Width="250" Height="230" Area="6" AddressStreet="rd. 434" AddressHouse="21" Location="Display Window">
Tasty Yellow
</Sign>
Someone who knows to manipulate DB in VBA, will need to determine what's the better pattern.
After the DB is complete, there will be a loop that runs on it, and creates such kind of report for every section (every property ID):
-------------------------------------------------------
BusinessName: Paz | Owner ID: 510216054 | Property ID: 166143000003 | Owner Name: Paz Company Ltd. | Owner Address: P.O.B. 222 City
Sign ID Sign Content Width (cm) Height (cm) Rounded Area Sign Address - Street Sign Address - House Sign Location
2010004701 Tasty Yellow 250 230 6 rd. 434 21 Display Window
2010004801 Paz (changing ads) 90 60 1 rd. 434 21 Bulding Wall
2010004901 Paz (changing ads) 90 60 1 rd. 434 21 Bulding Wall
2010005101 Price List 120 60 1 rd. 434 21 Pole
2010005601 Paz 80 60 1 rd. 434 23 Gas Station
..... More text that maybe uses DB data too .....
..... More text that maybe uses DB data too .....
..... More text that maybe uses DB data too .....
-------------------------------------------------------
I remind you, there's the previous thread which Kennet had helped me a lot in it:
http://www.vbaexpress.com/forum/showthread.php?t=33930
You probably can use large parts of his codes!!!
Thanks *a lot* in advance for all the helpers! :)
Meir.
I'm trying to create detailed report pages based on excel data.
Here's a little view to the sheet:
http://i33.tinypic.com/2q8dpoi.jpg
I've also attached this sample if you want to work on it.
Each property ID will have its own section in the DB, and finally its own report page.
As you can see, the lines that match one property ID, has same owner \ property data, and the difference is in the signs data.
So the reports structure should be: 1. owner and property data 2. collected signs data 3. more text \ data that I should be able to add later to the code as I wish.
At first, I was planning to directly create the reports from excel, a thing that required a collection of matching data for every property, since the lines are not necessarily in sequence. And this is, of course, not efficient.
Here is the thread of the old request: http://www.vbaexpress.com/forum/showthread.php?t=33930
Kenneth, who helped a lot there, suggested to create new file in case of not-yet-handled property, and then to append lines to existing files.
The problem that came up (and that was my fault- I didn't provide enough details at the beginning), is that I wish to add more data to the reports, after all the sign data is collected. (stage 3 in the structure above)
I raised an idea to store first in XML, then to loop on it and create reports, and Kenneth offerd DB which can be handled much better in Office apps.
First of all, I want the method to "remember" some coulmns by their titles (they'll be always the same), and "know" where to find the needed data.
I guess it'll be several loops that search for "title" and strore the column indexes in variables. ("Business Name" into BusNameCol, "Sign Content" into SignContentCol, etc.)
Now, there will be a loop that runs on the sheet and stores data into DB.
I'm not familiar with DB syntax, so I'll provide an XML example as I did in the old thread.
The DB pattern should be the same:
<Property id="166143000003">
<BusinessName>Paz</BusinessName>
<OwnerID>510216054</OwnerID >
<PropertyID>166143000003</PropertyID>
<OwnerName>Paz Company Ltd.</OwnerName>
<OwnerAddress>P.O.B. 222 City</OwnerAddress>
<Sign id="2010004701">
<Content>Tasty Yellow</Content>
<Width>250</Width>
<Height>230</Height>
<Area>6</Area>
<AddressStreet>rd. 434</AddressStreet>
<AddressHouse>21</AddressHouse>
<Location>Display Window</Location>
</Sign>
<Sign id="2010004801">
…
…
</Sign>
etc. etc.
</Property>
<Property id="36100001550">
…
…
…
</Property>
etc.
etc.
Or maybe most of the sign data should be in attributes and the content inside the tag:
<Sign id="2010004701" Width="250" Height="230" Area="6" AddressStreet="rd. 434" AddressHouse="21" Location="Display Window">
Tasty Yellow
</Sign>
Someone who knows to manipulate DB in VBA, will need to determine what's the better pattern.
After the DB is complete, there will be a loop that runs on it, and creates such kind of report for every section (every property ID):
-------------------------------------------------------
BusinessName: Paz | Owner ID: 510216054 | Property ID: 166143000003 | Owner Name: Paz Company Ltd. | Owner Address: P.O.B. 222 City
Sign ID Sign Content Width (cm) Height (cm) Rounded Area Sign Address - Street Sign Address - House Sign Location
2010004701 Tasty Yellow 250 230 6 rd. 434 21 Display Window
2010004801 Paz (changing ads) 90 60 1 rd. 434 21 Bulding Wall
2010004901 Paz (changing ads) 90 60 1 rd. 434 21 Bulding Wall
2010005101 Price List 120 60 1 rd. 434 21 Pole
2010005601 Paz 80 60 1 rd. 434 23 Gas Station
..... More text that maybe uses DB data too .....
..... More text that maybe uses DB data too .....
..... More text that maybe uses DB data too .....
-------------------------------------------------------
I remind you, there's the previous thread which Kennet had helped me a lot in it:
http://www.vbaexpress.com/forum/showthread.php?t=33930
You probably can use large parts of his codes!!!
Thanks *a lot* in advance for all the helpers! :)
Meir.