O
Oscar K
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?
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?