MeiR_ct
09-28-2010, 09:06 AM
Hi all.
This is a new post, which is very similar to [and kind of cross-posted from] http://www.vbaexpress.com/forum/showthread.php?p=225763 , since I have not introduced my request properly there, and perhaps that's why people avoided answering.
I have marked the old post as "Solved", because of this one.
I have this kind of sheet:
http://i33.tinypic.com/2q8dpoi.jpg
I need a loop to run on the sheet, and create a section in the DB for every "property ID" (column P).
First of all, the columns positions may change from time to time, so at the start, there will be several "find" commands, that store the columns in variables:
BusNameCol = rows(1).find(what:="Business Name").column
OwnerIDCol = rows(1).find(what:="Owner ID").column
PropIDCol = rows(1).find(what:="Property ID").column
OwnerNameCol = rows(1).find(what:="Owner Name").column
OwnerAddrCol = rows(1).find(what:="Owner Address").column
SignIDCol = rows(1).find(what:="Sign ID").column
SignContentCol = rows(1).find(what:="Sign Content").column
SignWidthCol = rows(1).find(what:="Width (cm)").column
SignHeightCol = rows(1).find(what:="Height (cm)").column
SignAreaCol = rows(1).find(what:="Rounded Area").column
SignStreetCol = rows(1).find(what:="Sign Address - Street").column
SignHouseCol = rows(1).find(what:="Sign Address - House").column
SignLocCol = rows(1).find(what:="Sign Location").column
As you can see, the lines that match one property ID, have the same owner and property data, but a different signs data.
So if the property ID doesn't exist in the DB, the macro will create it, and add the one-time data. And if it exists, it'll add to it the signs data.
Here's a kind of textual algorithm:
put 2 in variable "x"
while "x" smaller than last line
put the value from line "x" and column "PropIDCol" in variable "CurrentProp"
if a "Property ID" record named "CurrentProp" doesn't exists in file "Data.db"
create a "Property ID" record named as "CurrentProp" in file "Data.db"
add to "CurrentProp" record, a sub-record "BusinessName" with value from line "x" and column "BusNameCol"
add to "CurrentProp" record, a sub-record "OwnerID" with value from line "x" and column "OwnerIDCol"
add to "CurrentProp" record, a sub-record "OwnerName" with value from line "x" and column "OwnerNameCol"
add to "CurrentProp" record, a sub-record "OwnerAddress" with value from line "x" and column "OwnerAddrCol"
put the value from line "x" and column "SignIDCol" in variable "CurrentSign"
create inside "CurrentProp" record, a sub-record "Sign ID" named as "CurrentSign"
add to "CurrentSign" sub-record, a sub-sub-record "Content" with value from line "x" and column "SignContentCol"
add to "CurrentSign" sub-record, a sub-sub-record "Width" with value from line "x" and column "SignWidthCol"
add to "CurrentSign" sub-record, a sub-sub-record "Height" with value from line "x" and column "SignHeightCol"
add to "CurrentSign" sub-record, a sub-sub-record "Area" with value from line "x" and column "SignAreaCol"
add to "CurrentSign" sub-record, a sub-sub-record "AddressStreet" with value from line "x" and column "SignStreetCol"
add to "CurrentSign" sub-record, a sub-sub-record "AddressHouse" with value from line "x" and column "SignHouseCol"
add to "CurrentSign" sub-record, a sub-sub-record "Location" with value from line "x" and column "SignLocCol"
put (x + 1) in variable "x"
end of while loop
I'm not familiar with the DB syntax, so here is an output example in XML format which I wanted first, but Kenneth suggested DB.
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.
* Notice: I don't need the DB for any special purpose like SQL server and so, I just need a way to store my data, so I can update records later and do actions on them, like creating report files.
* If it's possible, I'd like to have also a button that does the job for a single (the current selceted) line.
* Please notice also line 12 in the picture, which has no property ID. I'm not sure yet what to do in this case. Maybe to prompt a messages that tells about it?
Thanks a lot in advance! :)
Meir.
This is a new post, which is very similar to [and kind of cross-posted from] http://www.vbaexpress.com/forum/showthread.php?p=225763 , since I have not introduced my request properly there, and perhaps that's why people avoided answering.
I have marked the old post as "Solved", because of this one.
I have this kind of sheet:
http://i33.tinypic.com/2q8dpoi.jpg
I need a loop to run on the sheet, and create a section in the DB for every "property ID" (column P).
First of all, the columns positions may change from time to time, so at the start, there will be several "find" commands, that store the columns in variables:
BusNameCol = rows(1).find(what:="Business Name").column
OwnerIDCol = rows(1).find(what:="Owner ID").column
PropIDCol = rows(1).find(what:="Property ID").column
OwnerNameCol = rows(1).find(what:="Owner Name").column
OwnerAddrCol = rows(1).find(what:="Owner Address").column
SignIDCol = rows(1).find(what:="Sign ID").column
SignContentCol = rows(1).find(what:="Sign Content").column
SignWidthCol = rows(1).find(what:="Width (cm)").column
SignHeightCol = rows(1).find(what:="Height (cm)").column
SignAreaCol = rows(1).find(what:="Rounded Area").column
SignStreetCol = rows(1).find(what:="Sign Address - Street").column
SignHouseCol = rows(1).find(what:="Sign Address - House").column
SignLocCol = rows(1).find(what:="Sign Location").column
As you can see, the lines that match one property ID, have the same owner and property data, but a different signs data.
So if the property ID doesn't exist in the DB, the macro will create it, and add the one-time data. And if it exists, it'll add to it the signs data.
Here's a kind of textual algorithm:
put 2 in variable "x"
while "x" smaller than last line
put the value from line "x" and column "PropIDCol" in variable "CurrentProp"
if a "Property ID" record named "CurrentProp" doesn't exists in file "Data.db"
create a "Property ID" record named as "CurrentProp" in file "Data.db"
add to "CurrentProp" record, a sub-record "BusinessName" with value from line "x" and column "BusNameCol"
add to "CurrentProp" record, a sub-record "OwnerID" with value from line "x" and column "OwnerIDCol"
add to "CurrentProp" record, a sub-record "OwnerName" with value from line "x" and column "OwnerNameCol"
add to "CurrentProp" record, a sub-record "OwnerAddress" with value from line "x" and column "OwnerAddrCol"
put the value from line "x" and column "SignIDCol" in variable "CurrentSign"
create inside "CurrentProp" record, a sub-record "Sign ID" named as "CurrentSign"
add to "CurrentSign" sub-record, a sub-sub-record "Content" with value from line "x" and column "SignContentCol"
add to "CurrentSign" sub-record, a sub-sub-record "Width" with value from line "x" and column "SignWidthCol"
add to "CurrentSign" sub-record, a sub-sub-record "Height" with value from line "x" and column "SignHeightCol"
add to "CurrentSign" sub-record, a sub-sub-record "Area" with value from line "x" and column "SignAreaCol"
add to "CurrentSign" sub-record, a sub-sub-record "AddressStreet" with value from line "x" and column "SignStreetCol"
add to "CurrentSign" sub-record, a sub-sub-record "AddressHouse" with value from line "x" and column "SignHouseCol"
add to "CurrentSign" sub-record, a sub-sub-record "Location" with value from line "x" and column "SignLocCol"
put (x + 1) in variable "x"
end of while loop
I'm not familiar with the DB syntax, so here is an output example in XML format which I wanted first, but Kenneth suggested DB.
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.
* Notice: I don't need the DB for any special purpose like SQL server and so, I just need a way to store my data, so I can update records later and do actions on them, like creating report files.
* If it's possible, I'd like to have also a button that does the job for a single (the current selceted) line.
* Please notice also line 12 in the picture, which has no property ID. I'm not sure yet what to do in this case. Maybe to prompt a messages that tells about it?
Thanks a lot in advance! :)
Meir.