Solved: Write data from sheet into DBs, then into files

[MeiR_ct]

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.

[MeiR_ct]

Ah yeah, forgot to mention my version: Excel 2002 SP3
Any help please? ^_^

[MeiR_ct]

Hmm, if someone wishes, I can try to write a textual algorithm, which he'll only need to "translate" it to VBA.

[MeiR_ct]

hmm bump? :O

[deyken]

Hi Meir,

Not sure if someone had already helped you, but I figured I'll add my 5 cents worth, since I am a regular user of this method on Excel 2003 and 2007:

PS: This is code copied from an Excel Workbook that I use daily to upload Currency Rates of Exchange into a SQL Server 2008 Database for myself, I will try indicate where you could change minor details to facilitate your own Excel File and Database situation...


Sub UploadROE()
' This function will create a Recordset from the Excel Sheet
' and then upload the data into the tblROE SQL Server 2008 Table

Dim Conn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set Conn = New ADODB.Connection
Dim Tbl As New ADODB.Recordset
Dim Start, Finish As Long
Dim DT As Date ' Date extracted from Excel Cells on Column B
Dim Curr As String ' String[3] of Currency extracted from Column C
Dim Vall As Double ' ROE Value extracted from Column D
Dim intRow As Integer

' OPEN DATABASE - SQL SERVER 2008
Conn.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING004\SQLEXPRESS;Database=BrolazShipping"

' OPEN RATES OF EXCHANGE TABLE
Tbl.Open "Select * from tblROE WHERE ROE_DATE = '" & DateToday & "'", Conn, adOpenDynamic, adLockOptimistic

' Now we will loop through the Excel rows and individually assign the variable values
intRow = 2 ' Row 1 contains all the HEADER Column Names
Do Until (intRow = 45)
DT = Cells(intRow, "B")
Curr = Cells(intRow, "G") ' Column "G" has the actual values we use, not column "C" as previously used
Vall = Cells(intRow, "D")

' THEN we will insert those values into the SQL Server Table
Tbl.AddNew
Tbl.Fields(1).Value = DT
Tbl.Fields(2).Value = Curr
Tbl.Fields(3).Value = Vall
Tbl.Update
Cells(2, "I") = "Added Currency: " & Curr & " @ " & Vall
' increment Counter intRow
intRow = intRow + 1
Loop
' LOOP COMPLETE

' If we reached this section the Update was successfull
Cells(2, "I") = "Updated Rates of Exchange for " & DT & " successfully! ROE's affected = " & intRow
Conn.Close
Set Conn = Nothing
End Sub

Notes:

1. Simply replace the connection string/method with one that supports your Database platform (like Excel itself, MS Access or what you prefer)

2. Simply rename the Recordset(s) with names that make more sense for your own personal use

3. Important - If you are using a Microsoft Database, please go to (menu) Tools | References... and select the Microsoft ActiveX Data Object (version 2.8 in Office 2007) by checking the box left of it. This enables you to access and use ADODB Methods and Properties.

4. This post assumes some familiarity with SQL Syntax

5. My objects, such "tblROE" are specific table names within the scope of my Datbase Platform (SQL Server Express 2008) and need to be replaced with your own Database's table name(s)

Holler of you need more info/assistance.

Adios

[MeiR_ct]

deyken, thanks a lot for the reply!
First of all, no such thing "5 cents worth", any help is worth and appreciated :)

Hmm, I can see your code handles DBs, but I really don't need the actual use of it as a database. I chose it only as a form to store my data, so I can access it later; for creating the report files, and also for editing and updating single records if I wish.
An SQL server is really out of my needs. The platform that I need is a simple Office (Access) database. [Or maybe they both are the same and I'm a newbie that doesn't know :D]

I'm not sure why people avoided answering so far, perhaps my post looks onerous because I explained in too much details what I want.
Any chance you know if I can open a new thread with same issue, so I can change my post and the length of it?

[Kenneth Hobs]

ADO methods similar to what Deyken posted can be used. You will need some modifications though like the connection string.

The first thing to do is decide how you will create the dbf file. If you have Access, I would do it manually first. If not, you can do it by programming. For the programming method, see the post by Jan Karel Pieterse, http://vbaexpress.com/forum/showthread.php?t=24883.

Once the dbf is created, we can easily fill it with the data.

If you don't have Access, be sure to get the MDAC files from Microsoft which are free.

The next step is to insert the rows as records into the dbf file. You can then run some SQL code to remove duplicates or use some more specific ADO code to iterate the rows and check for a matching with the same ID and not insert it.

Here is one simple method for poking Excel data into a dbf file.
Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub
I probably won't have time to give more specific help until this weekend. These ideas should give you something to play with though.

For some more specific ADO code, see: http://www.excelguru.ca/node/18

Notice that these code examples use early binding so you need to set the reference to the Microsoft Data Activex Connection library object.

[MeiR_ct]

Dear Kenneth,
I marked this post as solved, because I prefered to open a new one, which is better explained and focuses in the db handling only:
http://www.vbaexpress.com/forum/showthread.php?t=34256
I really should learn how to post clearly and not in a way that people will avoid answering :P

Anyway, I quoted there your answer from here and replied to it with some questions.

If you please wish to help, you may do it there :)
Thanks !!!