[@@] Tự động lấy dữ liệu từ file excel khác

  • Thread starter Thread starter pat_ftu
  • Ngày gửi Ngày gửi
Liên hệ QC
P

pat_ftu

Thành viên mới
Tham gia
16/1/07
Bài viết
19
Được thích
2
Em đang tập tọe viết code VBA để làm báo cáo, xin mô tả khó khăn của em, mong các cao nhân chỉ giáo ạ!
1. Việc cần phải làm:
- Lấy dữ liệu từ các đầu tài khoản (khoảng 70 tài khoản) trong các file *.xls (mỗi tháng chừng 30 file)
- Sắp xếp các dữ liệu lấy được vào một file excel tổng hợp để xử lý tiếp.
2. Việc đã làm:
- Viết code VBA để lấy dữ liệu & Dữ liệu đã lấy được
3. Khó khăn:
- Thời gian lấy dữ liệu rất lâu
4. Nội dung mong được chỉ giáo:
- Với code em viết như vậy, có cách nào tối ưu hóa để giảm thời gian lấy dữ liệu ko
- Nếu cách em làm chưa hiệu quả, mong các cao nhân chỉ giúp em cách khác ạ.

Em xin gửi data 30 ngày và file em đã làm để các cao nhân xem giúp em ạ!
Em xin cảm ơn ạ :)
 
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Mình ko biết nhiều về cái này, nhưng mình thấy có cách tổng hợp các file chung gộp thành 1 model thôi có lẽ nhanh hơn, thay vì bạn chia nhỏ thành các sub ra.

Cái này trên mạng cũng khá nhiều đó bạn tham khảo xem
 
Upvote 0
pat_ftu đã viết:
Em đang tập tọe viết code VBA để làm báo cáo, xin mô tả khó khăn của em, mong các cao nhân chỉ giáo ạ!
1. Việc cần phải làm:
- Lấy dữ liệu từ các đầu tài khoản (khoảng 70 tài khoản) trong các file *.xls (mỗi tháng chừng 30 file)
- Sắp xếp các dữ liệu lấy được vào một file excel tổng hợp để xử lý tiếp.
2. Việc đã làm:
- Viết code VBA để lấy dữ liệu & Dữ liệu đã lấy được
3. Khó khăn:
- Thời gian lấy dữ liệu rất lâu
4. Nội dung mong được chỉ giáo:
- Với code em viết như vậy, có cách nào tối ưu hóa để giảm thời gian lấy dữ liệu ko
- Nếu cách em làm chưa hiệu quả, mong các cao nhân chỉ giúp em cách khác ạ.

Em xin gửi data 30 ngày và file em đã làm để các cao nhân xem giúp em ạ!
Em xin cảm ơn ạ :)
Nhấp chuột vào đây để mở rộng...
Bạn thử code này.Mình định viết bằng ADO nhưng không hiểu sao không chạy.
Mã: 
Sub tonghop()
    Dim arr, data, kq, dic As Object, i As Long, j As Long, lr As Long, dk As String, lc As Long, cn As Object, filenguon As String
    Dim Sql As String, a As Long, b As Long, k As Integer, T As Workbook, max As Long
    Set dic = CreateObject("scripting.dictionary")
    Set cn = CreateObject("ADODB.Connection")
        With Sheets("tham so")
             lr = .range("A" & Rows.Count).End(xlUp).row
             data = .range("A1:A" & lr).Value
             lc = .Cells(1, 10000).End(xlToLeft).Column
             For i = 11 To lc
                 dk = Cells(1, i)
                 dic.Item(dk) = Array(i - 10, 1)
             Next i
       End With
       ReDim kq(1 To 1000, 1 To lc - 10)
For k = 1 To UBound(data)
    filenguon = data(k, 1)
   
   ' cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filenguon & ";Extended Properties=""Excel 12.0;HDR=No"";"
   ' Sql = "Select * From [a$A16:M100000]  where f1 is not null"
   ' arr = cn.Execute(Sql).GetRows
   ' For i = 0 To UBound(arr, 2)
    '    dk = arr(1, i)
     '   If dic.exists(dk) Then
      '     a = dic.Item(dk)(0)
       '    b = dic.Item(dk)(1)
       '    kq(b, a) = arr(12, i)
       '    b = b + 1
        '   dic.Item(dk) = Array(a, b)
      '  End If
   'Next i
   Set T = Workbooks.Open(filenguon)
       lr = T.Sheets("A").range("A" & Rows.Count).End(xlUp).row
       arr = T.Sheets("A").range("A16:m" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
         If dk <> Empty Then
           If dic.exists(dk) Then
             a = dic.Item(dk)(0)
             b = dic.Item(dk)(1)
             kq(b, a) = arr(i, 13)
             dic.Item(dk) = Array(a, b + 1)
             If max < b + 1 Then max = b + 1
           End If
         End If
      Next i
      T.Close False
Next k
   With Sheets("tham so")
        .range("K2:K1000").Resize(, lc - 10).ClearContents
        .range("k2").Resize(max, lc - 10).Value = kq
   End With
End Sub
 
Upvote 0
snow25 đã viết:
Bạn thử code này.Mình định viết bằng ADO nhưng không hiểu sao không chạy.
Mã: 
Sub tonghop()
    Dim arr, data, kq, dic As Object, i As Long, j As Long, lr As Long, dk As String, lc As Long, cn As Object, filenguon As String
    Dim Sql As String, a As Long, b As Long, k As Integer, T As Workbook, max As Long
    Set dic = CreateObject("scripting.dictionary")
    Set cn = CreateObject("ADODB.Connection")
        With Sheets("tham so")
             lr = .range("A" & Rows.Count).End(xlUp).row
             data = .range("A1:A" & lr).Value
             lc = .Cells(1, 10000).End(xlToLeft).Column
             For i = 11 To lc
                 dk = Cells(1, i)
                 dic.Item(dk) = Array(i - 10, 1)
             Next i
       End With
       ReDim kq(1 To 1000, 1 To lc - 10)
For k = 1 To UBound(data)
    filenguon = data(k, 1)
 
   ' cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filenguon & ";Extended Properties=""Excel 12.0;HDR=No"";"
   ' Sql = "Select * From [a$A16:M100000]  where f1 is not null"
   ' arr = cn.Execute(Sql).GetRows
   ' For i = 0 To UBound(arr, 2)
    '    dk = arr(1, i)
     '   If dic.exists(dk) Then
      '     a = dic.Item(dk)(0)
       '    b = dic.Item(dk)(1)
       '    kq(b, a) = arr(12, i)
       '    b = b + 1
        '   dic.Item(dk) = Array(a, b)
      '  End If
   'Next i
   Set T = Workbooks.Open(filenguon)
       lr = T.Sheets("A").range("A" & Rows.Count).End(xlUp).row
       arr = T.Sheets("A").range("A16:m" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
         If dk <> Empty Then
           If dic.exists(dk) Then
             a = dic.Item(dk)(0)
             b = dic.Item(dk)(1)
             kq(b, a) = arr(i, 13)
             dic.Item(dk) = Array(a, b + 1)
             If max < b + 1 Then max = b + 1
           End If
         End If
      Next i
      T.Close False
Next k
   With Sheets("tham so")
        .range("K2:K1000").Resize(, lc - 10).ClearContents
        .range("k2").Resize(max, lc - 10).Value = kq
   End With
End Sub
Nhấp chuột vào đây để mở rộng...
mình chạy code của bạn, kết quả bị lỗi. Ý mình là có những giá trị trong file excel nguồn có nhưng sau khi chạy code kết quả ko hiện.
VD: ô BW1 - tài khoản: 410203003, giá trị trong file excel 20190524 là: 391994140193 (tương ứng với cell O2546)
Bạn xem có gì bảo mình với nhé!
Cám ơn bạn Snow25 nhiều!
 

File đính kèm

  • Tong hop.jpg
    Tong hop.jpg
    257.9 KB · Đọc: 12
Upvote 0
pat_ftu đã viết:
mình chạy code của bạn, kết quả bị lỗi. Ý mình là có những giá trị trong file excel nguồn có nhưng sau khi chạy code kết quả ko hiện.
VD: ô BW1 - tài khoản: 410203003, giá trị trong file excel 20190524 là: 391994140193 (tương ứng với cell O2546)
Bạn xem có gì bảo mình với nhé!
Cám ơn bạn Snow25 nhiều!
Nhấp chuột vào đây để mở rộng...
không hiểu.Bạn ghi kết quả khi chạy code mình xem nào.
 
Upvote 0
snow25 đã viết:
không hiểu.Bạn ghi kết quả khi chạy code mình xem nào.
Nhấp chuột vào đây để mở rộng...
Mình gửi lại kết quả theo file đính kèm nhé. Vùng mình bôi vàng là vùng trong file nguồn có giá trị nhưng sau khi chạy code ko thấy có giá trị được ghi nhận trên file tổng hợp => mình đã bôi vàng vùng này và điền tay 2 giá trị vào để bạn tiện theo dõi.
Bạn xem lại giúp mình nhé!
 

File đính kèm

Upvote 0
Tò mò tý, số tiền hay gì của DN nào mà toàn ngàn tỷ, triệu tỷ vậy, kinh thế ?
 
Upvote 0
cao mạnh sơn đã viết:
Bạn tham khảo nhé
Nhấp chuột vào đây để mở rộng...
Cảm ơn Cao Mạnh Sơn nhiều nhé! Theo mình hiểu, bạn đang giải quyết bài toán với 1 file nguồn và 1 file đích, đầu tài khoản bạn lấy dữ liệu theo cột M của file nguồn. Nhưng bài toán của mình hơi rộng 1 chút, nếu đầu TK đầu 1 hoặc 5 sẽ lấy giá trị tại cột M của file đích; nếu đầu TK là 2 hoặc 4 sẽ lấy giá trị tại cột O của file đích. Bạn xem giúp mình nhé!
Bài đã được tự động gộp:

 
Lần chỉnh sửa cuối:
Upvote 0
Không hiểu được dữ liệu cả bạn nên chi đưa ra dữ liệu của cột M, bạn tùy biến 1 chút là OK thôi mà
Bài đã được tự động gộp:

Không biết loại tài khoản nào lấy dữ liêu cột nào, lấy tất các loại tài khoản hay loại 6 số, 9 số....bạn xem trong file rồi tự ý tùy biến nhé
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
cao mạnh sơn đã viết:
Không hiểu được dữ liệu cả bạn nên chi đưa ra dữ liệu của cột M, bạn tùy biến 1 chút là OK thôi mà
Bài đã được tự động gộp:

Không biết loại tài khoản nào lấy dữ liêu cột nào, lấy tất các loại tài khoản hay loại 6 số, 9 số....bạn xem trong file rồi tự ý tùy biến nhé
Nhấp chuột vào đây để mở rộng...
Mình gửi bạn bộ dữ liệu theo file đính kèm nhé. File đích là file Tong hop. Nếu xử lý vài file excel thì ok nhưng nếu đến vài trăm file thì mình thấy chưa tối ưu. Mình cũng mới sử dụng VBA nên có gì Cao Mạnh Sơn chỉ bảo giúp nhé!
 

File đính kèm

Upvote 0
Mình đã test với 20 file của bạn không vấn đề lắm, còn vài trăm file thì bạn thử xem chạy bao lâu nhé. tuy nhiên dữ liệu bạn cần lấy giá trị cột nào thì chỉ có bạn biết nên bạn lấy file ở bài 12 đưa chung vào 1 folder với các file nguồn, tùy biến lại code theo ghi chú trong code sẽ có kết quả đúng. còn phương pháp nào tối ưu hơn thì chưa rõ
 
Upvote 0
cao mạnh sơn đã viết:
Mình đã test với 20 file của bạn không vấn đề lắm, còn vài trăm file thì bạn thử xem chạy bao lâu nhé. tuy nhiên dữ liệu bạn cần lấy giá trị cột nào thì chỉ có bạn biết nên bạn lấy file ở bài 12 đưa chung vào 1 folder với các file nguồn, tùy biến lại code theo ghi chú trong code sẽ có kết quả đúng. còn phương pháp nào tối ưu hơn thì chưa rõ
Nhấp chuột vào đây để mở rộng...
Cảm ơn bạn nhiều!
 
Upvote 0
pat_ftu đã viết:
Cảm ơn bạn nhiều!
Nhấp chuột vào đây để mở rộng...
cao mạnh sơn đã viết:
Mình đã test với 20 file của bạn không vấn đề lắm, còn vài trăm file thì bạn thử xem chạy bao lâu nhé. tuy nhiên dữ liệu bạn cần lấy giá trị cột nào thì chỉ có bạn biết nên bạn lấy file ở bài 12 đưa chung vào 1 folder với các file nguồn, tùy biến lại code theo ghi chú trong code sẽ có kết quả đúng. còn phương pháp nào tối ưu hơn thì chưa rõ
Nhấp chuột vào đây để mở rộng...
Phiền bạn Cao Mạnh Sơn một chút nữa! Kết quả xuất theo cách làm của bạn thì một số đầu tài khoản được định dạng số, một số khác (VD đàu TK 2, 4) lại định dạng text cho nên pivot những giá trị từ đầu TK 2, 4 sẽ =0. Nhờ bạn chỉ cách khắc phục giúp nhé!
 
Upvote 0
Tôi đã nói với bạn, tôi không biết đầu tài khoản nào sẽ lấy dữ liệu cột nào. bạn mới cho biết tài khoản đầu 1 lấy cột M, tôi đã làm cho bạn tất cả các đầu tài khoản đều lấy cột M, tương tự như vậy với đầu 2, 4, 5 tương ứng là cột O, O, M... nhưng còn các đầu tài khoản khác tôi đâu biết.
bạn không nói rõ nên tôi ko thể làm cho bạn chi tiết hơn, tôi đã ghi chú trong code để bạn tùy chỉnh.
tôi đã kiểm tra các TK đầu 2, dữ liệu không phải là text, nó vẫn có kết quả đấy chứ, bạn ktra lại file bài trước tôi đính kèm. còn nếu dữ liệu nguồn của bạn mỗi file một kiểu thì bài toán này phải xử lý dữ liệu từ file nguồn là chuẩn nhất nhưng tôi ko hiểu hết dữ liệu của bạn, tôi xử lý dữ liệu sau khi tổng hợp. bạn đã viết được code thì việc xử lý dữ liệu nguồn chuyển từ text sang số cũng không khó. Làm luôn cho bạn nhưng dữ liệu vẫn là chưa đầy đủ, hy vọng bạn tự hoàn thiện được
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
cao mạnh sơn đã viết:
Tôi đã nói với bạn, tôi không biết đầu tài khoản nào sẽ lấy dữ liệu cột nào. bạn mới cho biết tài khoản đầu 1 lấy cột M, tôi đã làm cho bạn tất cả các đầu tài khoản đều lấy cột M, tương tự như vậy với đầu 2, 4, 5 tương ứng là cột O, O, M... nhưng còn các đầu tài khoản khác tôi đâu biết.
bạn không nói rõ nên tôi ko thể làm cho bạn chi tiết hơn, tôi đã ghi chú trong code để bạn tùy chỉnh.
tôi đã kiểm tra các TK đầu 2, dữ liệu không phải là text, nó vẫn có kết quả đấy chứ, bạn ktra lại file bài trước tôi đính kèm. còn nếu dữ liệu nguồn của bạn mỗi file một kiểu thì bài toán này phải xử lý dữ liệu từ file nguồn là chuẩn nhất nhưng tôi ko hiểu hết dữ liệu của bạn, tôi xử lý dữ liệu sau khi tổng hợp. bạn đã viết được code thì việc xử lý dữ liệu nguồn chuyển từ text sang số cũng không khó. Làm luôn cho bạn nhưng dữ liệu vẫn là chưa đầy đủ, hy vọng bạn tự hoàn thiện được
Nhấp chuột vào đây để mở rộng...
Rất biết ơn bạn!
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

Momominh
Sơ đồ hàng hóa trong Container
Trả lời
2
Đọc
72
anhdepjai
A
L
Viết phần mềm đánh lô bằng Visual Basic
    • Thích
2 3
Trả lời
51
Đọc
103K
taimeanh
T
hungtin1997
  • Question Question
[Hỏi] Tạo Hyperlink đến ô chạy ngang theo cột
Trả lời
6
Đọc
392
A HOANG 620
A
LuuAnh980
Add in tạo QR code miễn phí.
Trả lời
14
Đọc
520
htin1997
htin1997
N
Nhờ các anh chỉ chỉnh lỗi giúp em với ạ, em cảm ơn ạ.
Trả lời
1
Đọc
27
minhminh27
minhminh27
tramcannt
Các bác cho hỏi làm cách nào để thu nhỏ thanh công thức này với ạ. Em cảm ơn.
Trả lời
2
Đọc
44
Nhattanktnn
Nhattanktnn
S
  • Question Question
Điều chỉnh kích thước dòng tự động theo trang in
Trả lời
5
Đọc
130
HUONGHCKT
H
A
Tạo vé Loto bằng Excel
2
Trả lời
20
Đọc
23K
Tuấn tata
Tuấn tata
T
File quản lý thiết bị trong trường học!
Trả lời
3
Đọc
90
SA_DQ
SA_DQ
Nauduong
Cách chuyển định dạng từ PDF sang Image
Trả lời
9
Đọc
242
Châu Bá Thông
Châu Bá Thông
minhduongct
  • Question Question
Viết code VBA thay cho dùng hàm vlookup
Trả lời
6
Đọc
1K
VetMini
V
N
Thử viết code lập trình đa luồng bằng TwinBasic và khả năng ứng dụng vào COM add-in chạy trong Excel
    • Thích
Trả lời
0
Đọc
126
nguyendang95
N
Thanh271188
Tìm giá trị xuất hiện bất kì (không phải giá trị đầu, cuối) trong excel
    • Cười
Trả lời
4
Đọc
215
Duy Tùng 9x
Duy Tùng 9x
H
Xin viết dùng mã vba thay thế cho hàm vlookup
    • Thích
12 13 14
Trả lời
264
Đọc
79K
VetMini
V
SA_DQ
  • Question Question
[Bài tập VBA cho người rỗi rảnh] Hãy viết 1 macro tìm ra 3 số nguyên tố liên tiếp
2
Trả lời
35
Đọc
740
VetMini
V
P
Các Bác cho e hỏi làm thế nào để ẩn công thức trên GOOGLE SHEET với ạ?
2
Trả lời
22
Đọc
18K
yeudoi
yeudoi
T
Định dạng cột dữ liệu trong sql
Trả lời
5
Đọc
133
ptm0412
ptm0412
L
  • Question Question
[Hlep] Xóa ảnh theo tên và kích thước
Trả lời
8
Đọc
194
Luong24
L
Xoainon3005
Xin file mẫu kế hoạch sản xuất theo tháng ngành động cơ
Trả lời
0
Đọc
94
Xoainon3005
Xoainon3005
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
    • Cảm ơn
14 15 16
Trả lời
308
Đọc
23K
CheezeTube
CheezeTube
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
Chúc mừng năm mới Ất Tỵ 2025
Back
Top Bottom