LinkBack URL
About LinkBacks
As attachment shows, the result should be 321.3, however the result of VBA code is 310, why the diff exsits? Many thanks for help.
As attachment shows, the result should be 321.3, however the result of VBA code is 310, why the diff exsits? Many thanks for help.
Hi,
Didnt download your sample, but first of all check type of your variable that contains your sum. Looks like you collect result to Integer
Thanks, can you pls help to check the code below?Originally Posted by kasan
PHP Code:Dim cnn As Object, SQL$, s$, p$, f$
Set cnn = CreateObject("ADODB.Connection")
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.CSV")
cnn.Open "Provider=Microsoft.ace.OLEDB.16.0;Extended Properties='text;FMT=Delimited(,);hdr=YES';Data Source=" & p
Do While f <> ""
SQL = SQL & " SELECT INT(Date) AS T, Amount FROM [" & f & "] UNION ALL "
f = Dir()
Loop
SQL = Left(SQL, Len(SQL) - 11)
SQL = "SELECT T,SUM(Amount) FROM (" & SQL & ") GROUP BY T"
[A2:E1048576] = ""
[a2].CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
Dim R%
R = [A1048576].End(3).Row
[a1].Offset(R, 0) = "Sum"
[b1].Offset(R, 0).Resize(1, 4) = "=sum(b2:b" & R & ")"
Last edited by czl103; 09-26-2018 at 11:29 PM.
Waitfor for help...
While waiting for help, read the Forum Rules (at the top). You should be interested in #2.
Thanks, updated... Can you pls help to check?many thanks...
PHP Code:Dim cnn As Object, SQL$, s$, p$, f$
Set cnn = CreateObject("ADODB.Connection")
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.CSV")
cnn.Open "Provider=Microsoft.ace.OLEDB.16.0;Extended Properties='text;FMT=Delimited(,);hdr=YES';Data Source=" & p
Do While f <> ""
SQL = SQL & " SELECT INT(Date) AS T, Amount FROM [" & f & "] UNION ALL "
f = Dir()
Loop
SQL = Left(SQL, Len(SQL) - 11)
SQL = "SELECT T,SUM(Amount) FROM (" & SQL & ") GROUP BY T"
[A2:E1048576] = ""
[a2].CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
Dim R%
R = [A1048576].End(3).Row
[a1].Offset(R, 0) = "Sum"
[b1].Offset(R, 0).Resize(1, 4) = "=sum(b2:b" & R & ")"
Last edited by czl103; 09-26-2018 at 11:41 PM.
Pls help, masters...
Different method
Please Login or Register to view this content.
thanks, it does work...
If there are more item to caculate other than "amount", how to add another item? thanks in advance...
OK,
If it works, goes to next step to make it flexible.
I'm out at the moment, so I'll post the code when I get back.
Try
Please Login or Register to view this content.
Thanks a lot.... I will try later...
Tried, but this doesn't work. Error:Try
Please Login or Register to view this content.
"run error "424" object required"
Pls help to check why this happen, thanks...
1) Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
2) You should not delete 2 rows on the top of the module.
Please Login or Register to view this content.
It does work, thanks a lot...
Million thanks for your kindly help. Here is one question, for large amount of csv files, the running time for this code is much longer than the old one, Is there any way to shorten the running time?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks