TO ARM REPORT FROM BOOKS CLOSED - ADO

Hello friends: I request them help for the problem that I raise them next.


I keep the monthly sales in different books xls (SAL0108, SAL0208,
SAL0308,....etc.). The information is stored of the following way :


Code (code of article), Date (date of transaction) , Invoice (whole
invoices )


in a sheet I have the detail of the articles :

Code, Detail

I need to establish the comparative half-yearly one of sales for articles.


Ej.



ARTICLE DETAIL JANUARY FEBRUARY MARCH ........

1 XXXXX 1000 2000

2 XXXXX 4000
5000


The code that I wrote is more or less the following one :


Sub Sales()

I copy all the articles + detail in the sheet of the report


For fa = 1 To FinalArticle ' To cover Articles



Cod = it takes the code of article



For Month = 1 To 6

Fname= it takes the name of each of the books Ej.SAL0108.xls

SourceSheet$ = "Sheet1" Name of the eyelash



SourceRange$ = "A1:O1000" Range of search



I establish the connection





szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & Fname & ";" & _

"Extended Properties=""Excel 8.0;HDR=Yes"";"



Set rsCon = CreateObject("ADODB.Connection")

Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect



smSQL = "SELECT SUM wSales as SumReg FROM [" & SourceSheet$ &
SourceRange$ & "] WHERE(Code = " & Cod & ")"


rsData1.Open smSQL, rsCon, 0, 1, 1 ' I execute the instruction



Sheets("Credit").Cells(fi + 2, co + 1) = rsData1!SumReg ' I copy it in the
sheet of reports

Next Month

Next fa ' line of articles


I erase the lines that have no movement


End Sub


The code works well, but it is slow, since I must establish the connections
for every article in the respective months. Might anybody indicate if there
exists some more rapid way of doing this?