Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cell value to master report file which is closed? | Excel Worksheet Functions | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
Header in Report Manager Report | Excel Discussion (Misc queries) | |||
Fill an individual report with values from a team report? | Excel Programming | |||
macro to change the names and delete closed books | Excel Discussion (Misc queries) |