Lấy dữ liệu từ file excel đang đóng, sang file excel đang mở bằng ADODB (ADO)!

[Thư Sinh Áo Trắng]

- Em xin nhờ các anh chị giúp đỡ lấy dữ liệu file excel đang đóng sang file excel đang mở bằng VBA xử dụng ADODB (ADO)
- Em có ngồi kiếm code đễ sửa chữa nhưng đa số code mở lên 1 bảng chọn file, hoặc copy địa chỉ cố định. Hai vấn đề này khiến em không thể sửa code trên mạng để dùng được
- Vì:
+ Dữ liệu em là cập nhật mới, tức là chỉ số dòng luôn tăng lên sau mỗi lần cần lấy dữ liệu => copy địa chỉ cố định là em chết rồi
+ Mỗi lần dùng code, code bắt chọn file, nếu có 600 file thì tức là 600 lần chọn => vậy em cũng chết rồi
- Xin giúp đỡ code VBA xử dụng ADO để lấy dữ liệu file đang đóng sang file đang mở, không bắt chọn file, copy(ghi) được vùng có chỉ số động( luôn tăng)

Em chân thành cảm ơn! Và mong được sự giúp đỡ!
(file đính kèm dang_dong, dang_mo)

 

[BuiQuangThuan]

- Xin giúp đỡ code VBA xử dụng ADO để lấy dữ liệu file đang đóng sang file đang mở, không bắt chọn file, copy(ghi) được vùng có chỉ số động( luôn tăng)

Bạn thử code này coi

Sub GPE()
  Dim cn As Object, rs As Object
  Dim eRow&, includeList$, excludeList$, Sql$
  With Sheet1
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow > 2 Then .Range("A2:C" & eRow).Clear
  End With
  With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "All Excel", "*.xls*"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count < 1 Then MsgBox ("Ban khong chon file nao"): Exit Sub
    If .SelectedItems.Count Then
    
      On Error Resume Next
      Set cn = CreateObject("adodb.connection")
      cn.Open ("provider=Microsoft.ACE.OLEDB.12.0;data source=" & .SelectedItems(1) & ";mode=Read;extended properties=""Excel 12.0;hdr=no"";")
      Sql = "SELECT * FROM [$A2:C] WHERE f1 is not Null"
      Set rs = cn.Execute(Sql)
      If Not rs.EOF Then Sheet1.Range("A2").CopyFromRecordset rs
      rs.Close:      cn.Close
      Set rs = Nothing: Set cn = Nothing
      On Error GoTo 0
    End If
  End With
End Sub

 

[Hoàng Tuấn 868]

Bạn thử code này coi

File này có đủ chỗ cho dữ liệu của 600 file không bạn nhỉ?

 

[huhumalu]

File này có đủ chỗ cho dữ liệu của 600 file không bạn nhỉ?

file định dạng *.xlsm có thể chứa tối đa 1048576 dòng, nếu 600 file mỗi file tầm 1747 dòng thì toang.

 

[BuiQuangThuan]

File này có đủ chỗ cho dữ liệu của 600 file không bạn nhỉ?

Hihi. Có lẽ như bác ở trên nói. Tới tận 600 file thì nhiều lắm nhỉ.

 

[Hoàng Tuấn 868]

Hihi. Có lẽ như bác ở trên nói. Tới tận 600 file thì nhiều lắm nhỉ.

Không lẽ mỗi file lại lẻ tẻ hơn ngàn dòng thôi sao.

 

[BuiQuangThuan]

Không lẽ mỗi file lại lẻ tẻ hơn ngàn dòng thôi sao.

Hihi. Không thấy chủ top vào nói gì nhỉ. Chứ thực sự em cũng không nghĩ là dữ liệu nó sử dụng tới hơn triệu dòng. Còn nếu mà thực sự nhiều dữ liệu thế thì chắc dùng cách khác có lẽ hay hơn.

 

[huhumalu]

Hihi. Có lẽ như bác ở trên nói. Tới tận 600 file thì nhiều lắm nhỉ.

Nhìn lại thấy nick cậu "Thư sinh áo trắng" này, theo cậu này mấy topic thì biết đang nghiên cứu tiền ảo hay Bitcoin gì đó, nhưng ngại không dám đưa dữ liệu thật, hỏi hết cái này sang cái khác.
Tạm đoán việc @Thư Sinh Áo Trắng đang làm do vậy có mấy ý như này:
1. Bitcoin có ý nghĩa thật sự chứ không phải chỉ hư danh là tiền ảo, công nghệ Blockchain của BTC có ý nghĩa thật trong cuộc sống, chỉ là người ta đang lạm dụng và bơm giá thôi.
2. Việc lấy dữ liệu thô có nhiều trang web cho phép lấy giữ liệu giá cũ (đã qua) nó có thể nhẹ nhàng hơn nhiều so với cách cậu đang làm.
3. Muốn giao dịch thì phải có phương pháp, và phải có hiệu quả, một rừng dữ liệu thô không được liên kết thì kg có ý nghĩa gì cả.
4. Nếu việc đang làm dễ dàng như vậy thì chắc hẳn các nhà toán học, coder, ... chắc giàu dữ lắm.
---------
Hy vọng mình đoán sai

 

[Thư Sinh Áo Trắng]

Em cảm ơn các bác đã đã chia sẻ.
Thực tế là em cũng phải thử hết cái này đến cái khác xem nó ra làm sao? Vì sao mà phải nhiều máy phần cứng khủng chỉ để giải thuật toán?

Bài đã được tự động gộp: 3/5/21

Hihi. Không thấy chủ top vào nói gì nhỉ. Chứ thực sự em cũng không nghĩ là dữ liệu nó sử dụng tới hơn triệu dòng. Còn nếu mà thực sự nhiều dữ liệu thế thì chắc dùng cách khác có lẽ hay hơn.

Sơ sơ 1 sheets em đang xử dụng tới 20 triệu cell và 1 file nặng tầm 200 mê.
Vì file nặng phải chia tách nhỏ ra để chạy code không đơ.
Phương châm em giờ. Code chạy không đơ quá 15 phút là đc.
Do chia tách nhiều nên nhiều file ạ.

 

[Thư Sinh Áo Trắng]

File này có đủ chỗ cho dữ liệu của 600 file không bạn nhỉ?

Phải dùng nhiều sub và func để thu nhỏ hoặc dàn ra nhiều cột. Nhưng làm sao file vba không thôi tầm 200 mê thì chạy code còn kiên nhẫn đc, nếu quá 200 mê/ file thì chia nhỏ file ra nữa và có thể cả 1000 file ạ

 

[Hoàng Tuấn 868]

Phải dùng nhiều sub và func để thu nhỏ hoặc dàn ra nhiều cột. Nhưng làm sao file vba không thôi tầm 200 mê thì chạy code còn kiên nhẫn đc, nếu quá 200 mê/ file thì chia nhỏ file ra nữa và có thể cả 1000 file ạ

Mình biết có người đầu tư rất nhiều máy, một tháng nhiều triệu tiền điện, bây giờ đang đi bán hàng thuê để trả nợ dần.

 

[HieuCD]

Em cảm ơn các bác đã đã chia sẻ.
Thực tế là em cũng phải thử hết cái này đến cái khác xem nó ra làm sao? Vì sao mà phải nhiều máy phần cứng khủng chỉ để giải thuật toán?

Bài đã được tự động gộp: 3/5/21

Sơ sơ 1 sheets em đang xử dụng tới 20 triệu cell và 1 file nặng tầm 200 mê.
Vì file nặng phải chia tách nhỏ ra để chạy code không đơ.
Phương châm em giờ. Code chạy không đơ quá 15 phút là đc.
Do chia tách nhiều nên nhiều file ạ.

Nghiên cứu lĩnh vực gì vậy, bình thường ít khi lưu dữ liệu tới 20 triệu cell, những kết quả xử lý không nên lưu trong file.
Dữ liệu nhiều nên chuyển sang các phần mềm quản trị dữ liệu hoặc lưu dưới dạng file text

 

[Thư Sinh Áo Trắng]

Nghiên cứu lĩnh vực gì vậy, bình thường ít khi lưu dữ liệu tới 20 triệu cell, những kết quả xử lý không nên lưu trong file.
Dữ liệu nhiều nên chuyển sang các phần mềm quản trị dữ liệu hoặc lưu dưới dạng file text

Em tập tành xây một số đường hồi quy.
Vì excel em coi là cái gốc mọi lập trình khác, em mới nhập môn nên cũng chưa nghĩ tới ngôn ngữ hay phần mềm khác ạ.
Hiện cách chia thành nhiều file, 1 máy và nhiều Sub/Func thì thu nhỏ hay phóng to dữ liệu vẫn ổn anh ạ.
Anh có thể giởi thiệu qua phần mềm và lưu file dạng text thì có tính toán đc như VBA không ạ?

 

[Thư Sinh Áo Trắng]

Bạn thử code này coi
Sub GPE()

Code chạy tốt anh ạ! Em cảm ơn anh cho em code!
-Nhờ anh và các anh nâng cấp nên một chút, chỗ

...........
With Application.FileDialog(msoFileDialogFilePicker) <----- Chỗ này em muốn là đường dẫn đến file cố định một file->1 Sheet->1 vùng cần ghi, không phải pick chọn file ạ!
............

Tức là em bỏ qua phần Pick chọn file ạ! Mà là đường dẫn cụ thể ạ!
- Bỏ qua bẫy lỗi, và không hiện thông báo: Khi cung cấp đúng địa chỉ(đường dẫn file đóng) cần ghi dữ liệu thì ông Code phải làm chuẩn đưa nó về file mở cho Em. Nghĩa là chạy code là phải lấy được giữ liệu ở file đóng mà code không kêu ca gì(nhưng phải chuẩn). Nôm na là giấy trắng mực đen không thể có chuyện lỗi được hoặc ràng buộc để không để ra lỗi cho quá trình ghi và dán
- Giữ nguyên định dạng hiện thị ở file đóng khi copy sang file mở(file thực thi có code VBA)
Em chân thành cảm ơn anh @buiquangthuan

 

[HieuCD]

Em tập tành xây một số đường hồi quy.
Vì excel em coi là cái gốc mọi lập trình khác, em mới nhập môn nên cũng chưa nghĩ tới ngôn ngữ hay phần mềm khác ạ.
Hiện cách chia thành nhiều file, 1 máy và nhiều Sub/Func thì thu nhỏ hay phóng to dữ liệu vẫn ổn anh ạ.
Anh có thể giởi thiệu qua phần mềm và lưu file dạng text thì có tính toán đc như VBA không ạ?

Phần mềm Excel
Đọc file text: https://www.giaiphapexcel.com/diendan/threads/bài-12-filesystemobject.129399/
Lưu theo dạng text: Tìm trên diễn đàn

 

[ongke0711]

Phải dùng nhiều sub và func để thu nhỏ hoặc dàn ra nhiều cột. Nhưng làm sao file vba không thôi tầm 200 mê thì chạy code còn kiên nhẫn đc, nếu quá 200 mê/ file thì chia nhỏ file ra nữa và có thể cả 1000 file ạ

Dữ liệu đầu vào nên là file .CSV cho nhẹ nhàng, bỏ hết các định dạng, công thức...không cần thiết. Tôi thấy dữ liệu từ sàn thường được lưu về dưới dạng .csv hoặc dùng ứng dụng (python api) cũng lấy dữ liệu về xử lý cũng ở dạng .csv

 

[VetMini]

Dữ liệu đầu vào nên là file .CSV cho nhẹ nhàng, bỏ hết các định dạng, công thức...không cần thiết. Tôi thấy dữ liệu từ sàn thường được lưu về dưới dạng .csv hoặc dùng ứng dụng (python api) cũng lấy dữ liệu về xử lý cũng ở dạng .csv

Nếu được là CSV thì ngwoif ta dùng Powershell append đống files lại với nhau rồi hãy import. Làm việc tới mức độ nhiều files thế này thì bắt buộc phải biết Powershell. Nếu muốn lâu dài hơn thì phải học qua kỹ thuật CSDL Phân Vùng (partitioned database)

Thớt này làm việc với dữ liệu hàng khủng của hàng khủng mà không chịu cập nhật kiến thức rồi cứ ngồi đấy than "em chết rồi", "em cũng chết rồi". Nếu cái cơ quan này cứ mãi dùng thớt ở công việc này thì chính cơ quan cũng đang dãy chết.

Hàng khủng của hàng khủng này chỉ có dân chuyên nghiệp về Data Migration mới quản lý nổi.
Về nghề Data thì người này tối thiểu phải trên bậc tôi.
Tôi nghĩ là cỡ SQL Server bản chính trở lên chứ SQL Server Express cũng sụm.

Chú: số files nhiều thì phân nhóm chúng thành nhiều thư mục. 1000 files phân ra vài chục nhóm chỉ mất chừng 1 giờ đòng hồ là tối đa. Hình như các cô cậu công tử tiểu thơ ngày nay cứ tưởng tượng làm viiệc kéo chuột 1 giờ là "gãy tay", "em chết mất". Trong khi ngược lại, chơi ghêm cả buổi chả sao hết. Người không quen như tôi nhìn cái ghêm 20 phút là tối cả mặt mũi. Suy ra, chỉ là thói quen thôi. Kéo chuột cho công việc vài phút là mỏi tay, bấm ghêm càng lâu càng khoái?

 

[phuongnam366377]

Thấy có nhiều ý hay ... Tôi gợi ý cho 1 cách ... còn thực hiện hay ko thì tùy

1/ tạo 1 CSDL SQLite ( File *.sqlite, *.db +...) 1 File cở sở dữ liệu SQLite cho lưu tối đa 2048 GB và chạy đa nền tảng
2/ VD: những cái gì mà ko phải tạo mối quan hệ tính toán như DataXuat , DataNhap ... lưu vào đó
3/ khi cần truy xuất cực nhanh ... hãy hình dung nó cho lưu với cái Data khủng đó
4/ Nhưng cái gì cần tính toán linh tinh thì lưu vào Files *.accdb ( Access ) ... làm gì đó tùy
....
....
Sử dụng SQLite
1/ để sử dụng được SQLite trên Windows các bản sau này có sẳn thư viện API cứ thế khai báo mà xài .... rất khó
2/ cài Driver của nó xong dùng ADODB của Ms cứ thế kết nối mà xài .... rất đơn giản như xài trên VBA vậy
3/ nếu ko thích 2 mục trên thì có thể tự tay viết lấy 1 DLL mà sử dụng ... VD: VB6 họ cũng viết ok .... Delphi thì quá ok hơn VB6
....
...
Hiện tại tôi đang viết 1 Hàm duy nhất có thể truy xuất được dữ liệu Access , Excel, SQLite chung vào 1 Hàm ( tạm keo SQL Builder ) vì thấy nhiều ttrang trên Quốc tế họ sẻ sử dụng CSDL là SQLite phổ biến trong thời gian tới

...
...
hãy hình dung 1 chút
1/ 1 File Excel nếu lưu khoãng 15MB ... thì cái gì sẻ xảy ra
2/ 1 File Access tối đa = 2GB
3/ SQL Server Ms Free = 10 GB
4/ SQL Server Ms trả phí = ko giới hạn = Tiền ??? = mang qua máy Khác = ?? qua OS = ? qua Linux = ?
....
Tạm nói sơ bộ vậy ... có hứng nói tiếp .... ko thì Run

 

[Thư Sinh Áo Trắng]

Thấy có nhiều ý hay ... Tôi gợi ý cho 1 cách ... còn thực hiện hay ko thì tùy

1/ tạo 1 CSDL SQLite ( File *.sqlite, *.db +...) 1 File cở sở dữ liệu SQLite cho lưu tối đa 2048 GB và chạy đa nền tảng
2/ VD: những cái gì mà ko phải tạo mối quan hệ tính toán như DataXuat , DataNhap ... lưu vào đó
3/ khi cần truy xuất cực nhanh ... hãy hình dung nó cho lưu với cái Data khủng đó
4/ Nhưng cái gì cần tính toán linh tinh thì lưu vào Files *.accdb ( Access ) ... làm gì đó tùy
....
....
Sử dụng SQLite
1/ để sử dụng được SQLite trên Windows các bản sau này có sẳn thư viện API cứ thế khai báo mà xài .... rất khó
2/ cài Driver của nó xong dùng ADODB của Ms cứ thế kết nối mà xài .... rất đơn giản như xài trên VBA vậy
3/ nếu ko thích 2 mục trên thì có thể tự tay viết lấy 1 DLL mà sử dụng ... VD: VB6 họ cũng viết ok .... Delphi thì quá ok hơn VB6
....
...
Hiện tại tôi đang viết 1 Hàm duy nhất có thể truy xuất được dữ liệu Access , Excel, SQLite chung vào 1 Hàm ( tạm keo SQL Builder ) vì thấy nhiều ttrang trên Quốc tế họ sẻ sử dụng CSDL là SQLite phổ biến trong thời gian tới

...
...
hãy hình dung 1 chút
1/ 1 File Excel nếu lưu khoãng 15MB ... thì cái gì sẻ xảy ra
2/ 1 File Access tối đa = 2GB
3/ SQL Server Ms Free = 10 GB
4/ SQL Server Ms trả phí = ko giới hạn = Tiền ??? = mang qua máy Khác = ?? qua OS = ? qua Linux = ?
....
Tạm nói sơ bộ vậy ... có hứng nói tiếp .... ko thì Run

Dạ! Có ạ!
Đúng là file vba nặng tầm 15 mê đã thấy chậm chậm rồi bác ạ. Mà em toàn phải lưu file nặng tầm 200 mê.
Mỗi lần chạy lại " Not respending" đau kinh khủng.
Những gì bài 16,17,18 đều là cái đề cương của chuyên gia chia sẻ đáng quý ạ
Anh nói tiếp đi ạ!

 

[Hai Lúa Miền Tây]

Em cảm ơn các bác đã đã chia sẻ.
Thực tế là em cũng phải thử hết cái này đến cái khác xem nó ra làm sao? Vì sao mà phải nhiều máy phần cứng khủng chỉ để giải thuật toán?

Bài đã được tự động gộp: 3/5/21

Sơ sơ 1 sheets em đang xử dụng tới 20 triệu cell và 1 file nặng tầm 200 mê.
Vì file nặng phải chia tách nhỏ ra để chạy code không đơ.
Phương châm em giờ. Code chạy không đơ quá 15 phút là đc.
Do chia tách nhiều nên nhiều file ạ.

20 triệu cell thì nó bao nhiêu dòng và bao nhiêu cột vậy bạn?