Kết nối giữa các file Excel bằng ADODC.

[challenge98]

Ủa nhưng mình đã cho tạo ra hộp thoại tạo ra FName riêng, mình nạp đường dẫn trước rồi mới nạp list sau mà sao nó không lấy FName của bước nạp đường dẫn nhỉ? sao 2 cái FName ở bước chọn đường dẫn và bước nạp list không liên kết với nhau nhỉ?

 

[ThuNghi]

Bài này, mình thêm 1 ví dụ thô sử dụng ADODC để trích rút dữ liệu từ chính file Excel này để thiết lập báo cáo, chưa thêm phần nội lực của Excel. Điều mình muốn đề cập ở đây không phải giới thiệu kỹ thuật ADODC (Vì mình còn kém về vấn đề này lắm, nhưng không lo với sự hỗ trợ của GPE mình cho là không phải không làm được) mà mình muốn bàn là giải pháp nâng cao khả năng và có thể sử dụng Exc phục vụ công tác Kế toán thống kê mà thôi.
Trước đây, qua sách vở quảng bá và thực tế mình đã viết file Kế toán trên Exc khá kỳ công. Khi ở dạng Demo vài chục dòng thì chương trình trôi chảy, sổ sách báo báo cáo nhanh chóng, đẹp đẽ. Nhưng đau nhất lại là cuối năm, khi thời gian gấp rút không làm lại được với vài ngàn chứng từ (Tương ứng hàng chục ngàn dòng data) thì file sinh sự ỳ ra không chạy nổi, nhiều khi đơ luôn. Mỗi khi nhập 1 ô phải đợi mãi mới cập nhật được. Lý do, từ việc liên kết công thức từ quá nhiều sổ sách báo cáo đến sheet data. Biết bao nhiêu phép tính thường trực mỗi khi file hoạt động, nó làm cạn kiệt tài nguyên tưởng là khổng lồ của máy tính hiện nay.
Nhưng với ADODC trích dữ liệu xong thì cắt quan hệ với dữ liệu nguồn, các báo cáo gần như độc lập thì việc tạo thêm bao nhiêu báo cáo có hạn chế gì mấy, trong khi dùng công thức thì phải đắn đo mỗi báo cáo phải hy sinh bao nhiêu tốc độ. Mình mạn phép ví Excel -ADODC với khả năng kết hợp SQL thì như các cung thủ thời Tam Quốc được trang bị thêm kính La ze.
(Dữ liệu tháng 4/2009 các ô từ ngày, đến ngày thì bạn gõ tuỳ ý trong tháng 4)

Cám ơn Bác, rất hay, vừa thay AdFi vừ sumif từ ADO này.
Nhưng có điều chạy hay treo máy, chắc chiếm nhiều bộ nhớ.
Em nghĩ nên gám 1 Button để thực thi lệnh, dùng Event hay treo.
Hay là Bác thêm giữa phần

Sheet3.Rows("5:100").ClearContents
Sheet3.[b5].CopyFromRecordset recex

Thành

With Application
  .EnableEvents = False
End With
Sheet3.Rows("5:100").ClearContents
Sheet3.[b5].CopyFromRecordset recex
With Application
  .EnableEvents = True
End With

 

[sealand]

Thu Nghi à, không phải ADO chiếm nhiều bộ nhớ đến nỗi treo máy đâu vì trong các phần mềm đôi khi đồng thời tồn tại vài RecordSet cùng lúc để sử lý mà có sao đâu (Hơn nữa, dữ liệu ví dụ đâu có nhiều). Mình nghĩ cái chính là code ví dụ nên độ tùy biến chưa cao khi sang máy khác hay bị trục trặc hoặc không hoạt động (Ở máy của mình thì những cái sổ lớn như 111,511 thì chỉ là 1 cái chớp màn hình). Nếu cẩn thận thì dùng code của anh Duyệt để kết nối thì an toàn hơn. Nhưng khổ nỗi ví dụ gửi lên gửi xuống mà nó lớn quá cũng không ổn.

Cái Group by trong ví dụ nó tương quan với Sumproduct cơ (3 điều kiện cơ đấy: Tài khoản, từ ngày, đến ngày) . Sumif sao làm nổi nếu không chuyển sang công thức mảng (Mà đã nói đến mảng thì mình lại ghê vì ít thì được chứ nhiều thì ngồi đếm % calculate là thường)

Phần thêm của Thu Nghi là đúng quá chứ. Trong ví dụ để ngắn gọn nên mình áp roẹt 1 cái là Sheet3.Rows("5:100").ClearContents chứ thực tế phải xác định dòng cuối cùng cụ thể rồi xóa mới chính xác chứ. Rồi còn bẫy và sử lý lỗi nữa.

 

[challenge98]

Ai giải thích cho mình câu hỏi ở bài #41 với.

 

[sealand]

To challenge98:

Mình sửa lại 1 số chỗ theo yêu cầu của bạn, bạn xem lại nhé.
Nhưng làm sao lại phải thêm 1 cái form trung gian để làm gì nữa mình tham gia càng bớt càng tốt.

 

[challenge98]

Cảm ơn sealand rất đúng ý mình à nhờ mọi người giải thích cho mình đoạn code này với mình chưa hiểu lắm.

If recex.RecordCount > 0 Then
recex.MoveFirst
k = 0
Do While Not recex.EOF
ListBox1.AddItem recex.Fields(0)
ListBox1.List(k, 1) = recex.Fields(1)
k = k + 1
recex.MoveNext
Loop

Mình thấy trong giáo trình SQL có viết muốn chọn tất cả các cột có dữ liệu chỉ cần dùng truy vấn select * from lop là được mà sao không được nhỉ?
Thanks!

 

[sealand]

If recex.RecordCount > 0 Then 'Nếu recex có DL thì làm , không thì thôi
recex.MoveFirst 'Chuyển con trỏ về record đầu tiên
k = 0 'Đặt biến k=0
Do While Not recex.EOF 'Bắt đầu vòng lặp nếu recex ở cuối thì thôi
ListBox1.AddItem recex.Fields(0) 'Thêm 1 mục vào List bằng Field thứ nhất
ListBox1.List(k, 1) = recex.Fields(1) 'Thêm cột thứ 2 cho mục thứ k của List bằng Field thứ hai
k = k + 1 'Đặt biến k tăng them 1
recex.MoveNext 'Chuyển con trỏ về record tiếp theo
Loop 'Lặp lại


2/ Câu lệnh SQL thì cũng y chang ý bạn đấy thôi
recex.Open "select * from [Sheet1$] ", cnEx, adOpenKeyset, adLockOptimistic

 

[challenge98]

sealand này mình thấy một số người lại chuyển dữ liệu thành Array rồi từ array đưa vào listbox chứ không add từng cột( Field). 2 cách này thì cách nào hơn ( nhanh và đơn giản hơn).

Cho mình hỏi cái là file csv cũng là một trong những file của excel mà sao khi nạp vào thì không chạy nhỉ?

 

[sealand]

1/Theo mình nạp trực tiếp gọn hơn đỡ phải qua khâu nạp mảng, đỡ phải dọn dẹp vì với dữ liệu lớn thì cái biến mảng ấy khổng lồ, nếu quên reset biến thì nó chiếm bộ nhớ không nhỏ.
2/Nó không chạy vì phần mở rộng mà thôi, bạn đã khai đuôi trong chuỗi connect rồi. Bạn đổi phần mở rộng thành .xls xem.

 

[challenge98]

Không phai đâu sealand à mình nạp thủ không được đố sealand nạp chạy được file này

à mình xin nói thêm là cái file data.xls trên là file data.txt rename thành khi nạp no không nhận có cách nào khắc phục không cả nhà?

 

[sealand]

Bạn nói là file Exc nhưng đâu fải, nó là file txt chứ.

 

[challenge98]

2/Nó không chạy vì phần mở rộng mà thôi, bạn đã khai đuôi trong chuỗi connect rồi. Bạn đổi phần mở rộng thành .xls xem.

Thì theo như bạn nói chỉ cần đánh chuyển thành xls mà. có cách nào khắc phục không ban? vì mình muốn dữ liệu dạng text cho nhẹ.

 

[ThuNghi]

Bài này, mình thêm 1 ví dụ thô sử dụng ADODC để trích rút dữ liệu từ chính file Excel này để thiết lập báo cáo, chưa thêm phần nội lực của Excel. Điều mình muốn đề cập ở đây không phải giới thiệu kỹ thuật ADODC (Vì mình còn kém về vấn đề này lắm, nhưng không lo với sự hỗ trợ của GPE mình cho là không phải không làm được) mà mình muốn bàn là giải pháp nâng cao khả năng và có thể sử dụng Exc phục vụ công tác Kế toán thống kê mà thôi.

Sub chepdl2()
ng1 = [d2]
ng2 = [d3]
On Error Resume Next
  FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Sheet3.Rows("5:100").ClearContents
Sheet3.[b5].CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic

Bác cho em hỏi [Data$] có thể là 1 range không.
Ý là mình xác định số dòng cột để mà tổng hợp. Nếu dùng trên Ex 2007, vậy là nó lấy hết bảng tính. Sợ lớn không.
Từ này em sẽ dùng cái này thế AdFi thử.
Cám ơn Bác nhiều.

 

[Nguyễn Duy Tuân]

Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
[/PHP]Bác cho em hỏi [Data$] có thể là 1 range không.
Ý là mình xác định số dòng cột để mà tổng hợp. Nếu dùng trên Ex 2007, vậy là nó lấy hết bảng tính. Sợ lớn không.
Từ này em sẽ dùng cái này thế AdFi thử.
Cám ơn Bác nhiều.

Nếu muốn truy vấn dữ liệu trên một Range thì làm thế này

SELECT * FROM [sheet1$A4:H100]...

Thông thường nên đặt tên/Name NKC= sheet1!$A$4:$H$100

Khi đó ta có thể viết câu lệnh SQL

SELECT * FROM NKC...

 

[MinhNguyệt]

Sub chepdl2()
ng1 = [d2]
ng2 = [d3]
On Error Resume Next
  FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Sheet3.Rows("5:100").ClearContents
Sheet3.[b5].CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic

Bác cho em hỏi [Data$] có thể là 1 range không.
Ý là mình xác định số dòng cột để mà tổng hợp. Nếu dùng trên Ex 2007, vậy là nó lấy hết bảng tính. Sợ lớn không.
Từ này em sẽ dùng cái này thế AdFi thử.
Cám ơn Bác nhiều.

1/ Truy vấn toàn bộ Worksheet

"SELECT * FROM [MySheet$]"

2/ Truy vấn một dãy ô :

"SELECT * FROM [MySheet$A1:G100]"

3/ Truy vấn một dãy được đặt tên :

"SELECT * FROM MyNamedRange"

 

[ThuNghi]

Sao vận dụng đặt name vào bài SO KE TOAN của anh Sealand trong topic này mà nó không chạy. Các bác xem hộ sai chỗ nào.

Sub chepdl2()
ng1 = [d2]
ng2 = [d3]
Dim endR As Long, myRng As Range
On Error Resume Next
  FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
With Sheets("Data")
  endR = .Cells(65000, 1).End(xlUp).Row
  Set myRng = .Range("A1:H" & endR)
  
End With
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from myrng where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic


Sheet3.Rows("5:100").ClearContents
Sheet3.[b5].CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

 

[MinhNguyệt]

Bài này, mình thêm 1 ví dụ thô sử dụng ADODC để trích rút dữ liệu từ chính file Excel này để thiết lập báo cáo, chưa thêm phần nội lực của Excel. Điều mình muốn đề cập ở đây không phải giới thiệu kỹ thuật ADODC (Vì mình còn kém về vấn đề này lắm, nhưng không lo với sự hỗ trợ của GPE mình cho là không phải không làm được) mà mình muốn bàn là giải pháp nâng cao khả năng và có thể sử dụng Exc phục vụ công tác Kế toán thống kê mà thôi.
Trước đây, qua sách vở quảng bá và thực tế mình đã viết file Kế toán trên Exc khá kỳ công. Khi ở dạng Demo vài chục dòng thì chương trình trôi chảy, sổ sách báo báo cáo nhanh chóng, đẹp đẽ. Nhưng đau nhất lại là cuối năm, khi thời gian gấp rút không làm lại được với vài ngàn chứng từ (Tương ứng hàng chục ngàn dòng data) thì file sinh sự ỳ ra không chạy nổi, nhiều khi đơ luôn. Mỗi khi nhập 1 ô phải đợi mãi mới cập nhật được. Lý do, từ việc liên kết công thức từ quá nhiều sổ sách báo cáo đến sheet data. Biết bao nhiêu phép tính thường trực mỗi khi file hoạt động, nó làm cạn kiệt tài nguyên tưởng là khổng lồ của máy tính hiện nay.
Nhưng với ADODC trích dữ liệu xong thì cắt quan hệ với dữ liệu nguồn, các báo cáo gần như độc lập thì việc tạo thêm bao nhiêu báo cáo có hạn chế gì mấy, trong khi dùng công thức thì phải đắn đo mỗi báo cáo phải hy sinh bao nhiêu tốc độ. Mình mạn phép ví Excel -ADODC với khả năng kết hợp SQL thì như các cung thủ thời Tam Quốc được trang bị thêm kính La ze.
Mình xúi vậy thôi, nhưng các bạn cứ thử xem.
(Dữ liệu tháng 4/2009 các ô từ ngày, đến ngày thì bạn gõ tuỳ ý trong tháng 4)

Tại sao file này chạy trên máy tôi lại không được nhỉ? Đã thay 2.0 bằng 2.8 nhưng khi thay đổi ngày thì code không chạy. Máy cài WinXP SP3, Office 2003 và 2007. Hay tại cài hai cái Off trên một máy nhỉ? Ai biết chỉ giùm
Thân

 

[ThuNghi]

Mới nhờ PTM0412 hướng dẫn và rút ra 1 nhận xét.
Phải đặt name chớ không phải
Dim MyRng as range
Set MyRng=...

Mà phải là

With Sheets("Data")
  endR = .Cells(65000, 1).End(xlUp).Row
  .Range("A1:H" & endR).Name = "myRng"
End With

Lúc này ADO mới xem dòng 1 là tên field.
Và phải thêm "[...]" thành [MyRng] thì nó mới chạy.

 

[sealand]

Trong các loại biến thì biến Variant là loại biến đáng dè chừng nhất, mình cũng chưa thấy tài liệu nào chỉ dẫn nó biến tướng đến đâu. Trong code của Thu Nghi kêu nó không chạy, nhưng đổi dạng biến 1 chút thì nó lại chạy (Không phải thay đổi bất cứ điều gì nữa):

Dim MyRng As Range <---đổi thành---> Dim MyRng As Variant

 

[cadafi]

Đây là một ví dụ ứng dụng ADODB với câu lệnh SQL lồng (dùng Union All). Các anh chị và các bạn tham khảo code bên dưới và xem file đính kèm. Đã test thử với cấu trúc DATA như file đính kèm, khoản 10.000 record lọc chưa tới 15 giây.

[highlight=vb]
Sub GetDetail()
'---------------------------------------------------------------------------
'FName: Database Name including Path (ex: "D:\MyDatabase\MyFile.xls")
'cnnEx: Connection string into external database
'recEx: Recordset
'mySQL: SQL statement
'---------------------------------------------------------------------------
Dim FName As String, mySQL_Dr As String, mySQL_Cr As String, mySQLDetail As String
Dim mPeriod As Long, mAcctID As String
Dim cnnEx As New ADODB.Connection
Dim RecEx As New ADODB.Recordset
'---------------------------------------------------------------------------
FName = ThisWorkbook.FullName
mPeriod = Sheets("Detail").[B6].Value 'Ky bao cao can loc
mAcctID = "'" & Sheets("Detail").[B5].Text & "%'" 'Tai khoan can loc, co the loc theo do dai ky tu tai khoan
'Tao Ket noi voi file du lieu nguon:
cnnEx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & _
";Persist Security Info=False; Extended Properties=Excel 8.0;"
'---------------------------------------------------------------------------
mySQL_Dr = "Select a.TKNo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKCo as TKDoiUng, a.Thanhtien as GhiNo, 0 as GhiCo, 0 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Dr = mySQL_Dr & "WHERE a.Period= " & mPeriod & " AND a.TKNo LIKE " & mAcctID
mySQL_Cr = "Select a.TKCo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKNo as TKDoiUng, 0 as GhiNo, a.ThanhTien as GhiCo, 1 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Cr = mySQL_Cr & "WHERE a.Period= " & mPeriod & " AND a.TKCo LIKE " & mAcctID
'---------------------------------------------------------------------------
mySQLDetail = mySQL_Dr & Chr(13) & "Union all" & Chr(13) & mySQL_Cr
a = MsgBox("You will get the data by runing this SQL statement: " & Chr(13) & "----------" & Chr(13) & mySQLDetail & Chr(13) & "----------", vbInformation + vbYesNo, "Info")
If a = vbYes Then
RecEx.Open mySQLDetail, cnnEx, adOpenKeyset, adLockOptimistic
'---------------------------------------------------------------------------
'Neu query khong tim thay du lieu thi thoat ra, khong xoa du lieu cu:
If RecEx.EOF Then
a = MsgBox("Hic!No record found!", vbCritical + vbOKOnly, "Info")
Exit Sub
Else
Sheets("Detail").[9:65536].Delete
Sheets("DETAIL").[A9].CopyFromRecordset RecEx
'Refresh lai hai bien cnEx va RecEx:
RecEx.Close: Set RecEx = Nothing
cnnEx.Close: Set cnEx = Nothing
b = MsgBox("Data has already been exported to sheet(Detail)!", vbInformation + vbOKOnly, "Info")
Sheets("DETAIL").Activate
End If
Else
c = MsgBox("Cancel query by user!Hehe...!See you later! --> ca_dafi", vbCritical + vbOKOnly, "Info")
Exit Sub
End If
End Sub
[/highlight]

File PDF đính kèm hướng dẫn sơ lược cách sử dụng ADODB một cách đơn giản nhất! Mời mọi người tham khảo!