DSUM OR SUMPRODUCT?

[Nimit Mehta]

A folder contains 1100 files of international calling
customers. All .xls files are named unique (client's name)
and have same fileds in all.

Coloumn A Coloumn B

DATE MINUTES

22-june-04 54
22-june-04 34
23-june-04 12



Another client.xls

Coloumn A Coloumn B

DATE MINUTES

16-june-04 54
22-june-04 72
23-june-04 12

One main reports.xls files is linked with all clients from
where i can just open the file and see current balance,
total deposits and stuff about all clients at one go,
without opening individual files.

I am looking for a way to check the daily consumed minutes.
I want to have a fuction where in if i enter 22-june-04 in
a cell, it should look for 22-june04 in all 1200 files and
add up minutes in same row. ( in above case should give
160 minutes, 2 calls from first client adding up to 88 and
1 call from another client.xls 72, total 160.)
Please Note: VLOOKUP wont work as it looks for first match
only, one client can have more than one calls made on the
same day itself.
DSUM works, but i have to keep all sheets open. Keeping
all 1200 sheets open would need 8 high speed processors
and GBs of RAM..
SUMPRODUCT might work.
Any suggestions?
Thanks a lot.

 

[keepITcool]

Nimit..

Either you must be mad maintaining your data in 1000 excel files,
or business growth has superseded the original design ;-)

You definitely DONT want to work with 1000 linked files...


Transfer your data to a database, (i dont know how many users are
working the system, which would drive your choice of MDB, MSDE or
SQLserver...

when your date is in 1 database, you can use all the power ot SQL
to retrieve your data, use PivotTables for your reports etc etc...


In the interim.. do some reading on ADO & SQL..
(www.erlandsendata.no is a good place to start)


As a temporary solution I've created a routine that collects the data
from the files and dumps it in an mdb.

Creating 1 MDB with all the data from 1000 excel data files with 1000
records of 10 fields each takes around 1 minute on my laptop...

You could that each morning or so... ??

Here's the code... It's probably not the most efficient code on earth,
but it DOES work to consolidate all data in 1 mdb.


Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;Header:YES"";Data Source=&FFFF;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, "&FFFF", sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'd:\phone\data.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `sheet1$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'd:\phone\data.mdb' from `sheet1$`"
else
msgbox err.number & vbnewline & err.description
End If
GoTo endH

End Sub


Hopes this get's you started



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Nimit Mehta]

Yea its business growth problem and i am trapped now.
Forget 1000 linked files.
Is there a way i can add up the minutes if it was just one
file? without opening it? I know i can using either
sumproduct or dsum. I tried both and did lot of R&D.
Finally gave up and here i am on newsgroups..

-----Original Message-----
Nimit..

Either you must be mad maintaining your data in 1000 excel files,
or business growth has superseded the original design ;-)

You definitely DONT want to work with 1000 linked files...


Transfer your data to a database, (i dont know how many users are
working the system, which would drive your choice of MDB, MSDE or
SQLserver...

when your date is in 1 database, you can use all the power ot SQL
to retrieve your data, use PivotTables for your reports etc etc...


In the interim.. do some reading on ADO & SQL..
(www.erlandsendata.no is a good place to start)


As a temporary solution I've created a routine that collects the data
from the files and dumps it in an mdb.

Creating 1 MDB with all the data from 1000 excel data files with 1000
records of 10 fields each takes around 1 minute on my laptop...

You could that each morning or so... ??

Here's the code... It's probably not the most efficient code on earth,
but it DOES work to consolidate all data in 1 mdb.


Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString

= "Provider=Microsoft.Jet.OLEDB.4.0;Extended

 

[keepITcool]

As I said..

it depends on the amount of data in each file.
that's the basis of any design AND would DRIVE your choice of how and
where to store it..

If it's inefficiently stored, there's no way you can efficiently report
on it. (let alone the chance of losing data)


how many calls (records) do these customers generate in total?


As you are aware excel has 65k row limit and with
1000+ customers this is quickly exhausted ...

1000 cust with 1000 calls per year = 1million records.
Access can just about handle that, but MSDE
or mySQL would probably be a better option.

Doing it in Excel is asking for trouble.
There's always a tradeoff =>
1 masterfile with the customer database
1 transaction file per day
1 aggregate file per week..

might work.. but it wouldn't be my choice.

as I said and demonstrated earlier =>

dumping it in an MDB for reporting can be done,
but it is an interim solution.

you'll need to spend time (and money) to securely
and efficiently store your data


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >