Xin giúp đỡ: Ghép dữ liệu từ nhiều file excel khác nhau thành 1 file

[phuctq124]

Xin chào các các thành viên GPE, mình có một vấn đề nhờ mọi người giúp:

Mình có 20 file excel khác nhau, có cấu trúc dữ liệu như file mẫu đính kèm.
Mình muốn kết hợp 20 file này lại thành một file. Vùng dữ liệu mình lấy là từ dòng thứ 4 (luôn luôn là ROW 4) đến dòng trên liền kề dòng trong cột A có chữ "TỔNG SỐ TÀI KHOẢN:". Vùng dữ liệu ở trong thì dài ngắn bất kỳ, có thể từ vài chục đến vài nghìn dòng, tùy theo số lượng giao dịch phát sinh.
Chỉ kết hợp lại thôi, dữ nguyên dòng cột y chang như vậy.

Mong các thành viên giúp đỡ nha, sáng nào mình cũng phải làm cái báo cáo này, mà ngày nào cũng phải copy -> paste.

Xin chân thành cảm ơn

 

[phuctq124]

help me, không có ai giúp đỡ mình với ah,

 

[thanhsangnguyen1982]

Đưa ví dụ cụ thể hơn được không bạn, và đưa ra kết quả mong muốn của bạn. Bạn nói như vậy mình chưa hiểu lắm

 

[phuctq124]

help me, không có ai giúp đỡ mình với ah, <img src="images/smilies/a36.gif" alt="" title="4888" smilieid="305" class="inlineimg" border="0">

 

[phuctq124]

Đưa ví dụ cụ thể hơn được không bạn, và đưa ra kết quả mong muốn của bạn. Bạn nói như vậy mình chưa hiểu lắm

Mình đưa 3 file: dulieu1, dulieu2 va ketqua, bạn xem giúp mình nha. Minh mô tả lại thế này

1. File dữ liệu có cấu trúc như sau: Cột A là số Tài khoản, Cột B là tên TK ... cột M là Nơi Phát Sinh.
2: Dữ liệu cần lấy bắt đầu từ hàng thứ 4 -> đến hàng trên hàng "TỔNG SỐ TÀI KHOẢN:" 1 hàng. Còn số lượng hàng là bất kỳ tùy vào số giao dịch phát sinh (từ vài chục đến vài nghìn). Ví dụ như: file dulieu1 sẽ lấy từ hàng 4 -> hàng 12; file dulieu2 lấy từ hàng 4 đến hàng 11.
3: Tổng cộng mình có 20 file dữ liệu tất cả.
4: Dữ liệu của 20 file được tổng hợp thành 1 file như trong file kết quả (Sheet1).
5: Thông thường mình copy -> paste từng file. Vì cột số TK có lẫn 1 số dòng có chữ "LOẠI TIỀN, TỔNG NỢ ...), mình sẽ filter rồi xóa đi các dòng đó, để giữ lại số TK thôi. Thường làm mất khoảng 10-15 phút.
6: Trong file kết quả mình có để 2 sheet, nếu kết quả như sheet1 thì đã OK rồi, còn được như sheet kia thì quá tuyệt vời.

Mong cái bạn giúp đỡ nha.

 

[thanhsangnguyen1982]

Cách tốt nhất là bạn đưa 20 file dữ liệu vào cùng một sheet thì xử lý dễ dàng hơn và nếu có thể thì đưa dũ liệu chính thức lên xử lý một lần luôn

 

[phuctq124]

điều mình cần là gom 20 file này thành 1 file đó bạn, sau đó mình dùng file đó xử lý mà. Còn ý bạn nói là đưa dữ liệu lên để làm luôn? cái này không phải mình chỉ có 20 file này, mà ngày nào mình cũng phải làm cái báo cáo với 20 file đó, và dữ liệu thay đổi theo ngày. Nên khá mất thời gian, nên nhờ bạn giúp đỡ.

 

[dhn46]

Tôi làm cho bạn nhưng lưu ý 1 số điểm như sau:
1/ Các file phải có cùng cấu trúc như ví dụ và bắt buộc tên sheet chứa dữ liệu mang tên Sheet1
2/ Các file cần tổng hợp cho vào 1 thư mục
3/ Chạy trên Excel 2003 (nếu cao hơn thì phải sửa Code, cái này do bạn đưa file mẫu là *.xls nhé)

Sub TongHop()
    Dim cnn As Object, lsSQL As String, lrs As Object
    Dim Fso As Object, fn, Link As String, Fname As String
    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")


    'Mo hop thoai chon thu muc
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            Link = .InitialFileName
        Else
            MsgBox "Ban da khong chon tong hop", vbInformation, "DHN46 - Thong bao"
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    'Duyet qua cac thu muc trong Folder
    With Fso
        For Each fn In .GetFolder(Link).Files
            Fname = .BuildPath(Link, fn.Name)
            If Fname <> ThisWorkbook.FullName Then
                'Tao ket noi CSDL
                With cnn
                    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                        "Data Source=" & Fname & _
                                        ";Extended Properties=""Excel 8.0;HDR=No;Imex=1"";"
                    .Open
                End With
                'Cau lenh truy van
                lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F12 is not Null"
                lrs.Open lsSQL, cnn, 3, 1
                'Xuat ra File
                Sheet1.Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
                cnn.Close
            End If
        Next
    End With
    'Chuyen doi dang Text sang Number
    Sheet1.[IV50].Copy
    Sheet1.Range("A2", Sheet1.Range("L" & Sheet1.[A65536].End(3).Row)).PasteSpecial xlPasteAll, xlPasteSpecialOperationAdd
    Application.ScreenUpdating = True
    Set lrs = Nothing
    Set cnn = Nothing
End Sub

 

[phuctq124]

cảm ơn bạn dhn46 rất nhiều nha, nhưng nhờ bạn giúp mình thêm tí nữa nha, vì mình dùng máy ở cơ quan và đang dùng EXcel 2007, nhưng do khi xuất file dạng data only nên nó ra như vậy đó. Mình lấy về chạy trên 2007 thì thấy không báo lỗi gì nhưng nó chỉ chạy được 10 file thôi, còn có 10 file kia nó không chạy.

 

[dhn46]

cảm ơn bạn dhn46 rất nhiều nha, nhưng nhờ bạn giúp mình thêm tí nữa nha, vì mình dùng máy ở cơ quan và đang dùng EXcel 2007, nhưng do khi xuất file dạng data only nên nó ra như vậy đó. Mình lấy về chạy trên 2007 thì thấy không báo lỗi gì nhưng nó chỉ chạy được 10 file thôi, còn có 10 file kia nó không chạy.

Tôi sửa cho bạn dùng trên Excel 2003, 2007... Bạn test và cho phản hồi.

 

[phuctq124]

Cảm ơn bạn rất nhiều, mình chạy cũng chỉ được 1 phần nhưng mình xem code thấy đoạn này:
lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F12 is not Null"
lrs.Open lsSQL, cnn, 3, 1

Chỗ điều kiện không biết bạn căn cứ F12 là vì sao vậy, có cách nào mình kiểm tra cột F = 'C' hoặc 'D' thì lấy dữ liệu không. Mình sửa tạm thời thành F4 WHERE F4 is not Null thì chạy được nhưng file kết quả nó vẫn còn chữ, mình dùng filter để xóa. Nếu được bạn xem dùm mình nha

 

[dhn46]

Cảm ơn bạn rất nhiều, mình chạy cũng chỉ được 1 phần nhưng mình xem code thấy đoạn này:
lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F12 is not Null"
lrs.Open lsSQL, cnn, 3, 1

Chỗ điều kiện không biết bạn căn cứ F12 là vì sao vậy, có cách nào mình kiểm tra cột F = 'C' hoặc 'D' thì lấy dữ liệu không. Mình sửa tạm thời thành F4 WHERE F4 is not Null thì chạy được nhưng file kết quả nó vẫn còn chữ, mình dùng filter để xóa. Nếu được bạn xem dùm mình nha

Những gì người trợ giúp viết chỉ là dựa trên File bạn gửi và "đoán", có lẽ không khác "thầy bói" là mấy. Cái cơ sở đó do tôi nhận thấy các File mẫu bạn gửi đều ghi và nó có đặc điểm khác các cột khác, tức là muốn lấy dòng nào thì dòng đó đều có dữ liệu tại f12 còn dòng không muốn lấy thì f12 không có. Bạn muốn có kết quả chính xác thì các File mẫu phải tổng quát, đoán thì có trúng và trượt thôi.

 

[phuctq124]

ok, cảm ơn bạn rất nhiều, vậy cho mình hỏi có cách nào kiểm tra: lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F12 is not Null", với điều kiện là cột F = ký tự 'C' hoặc 'D' thì lấy dữ liệu không bạn.

 

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

ok, cảm ơn bạn rất nhiều, vậy cho mình hỏi có cách nào kiểm tra: lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F12 is not Null", với điều kiện là cột F = ký tự 'C' hoặc 'D' thì lấy dữ liệu không bạn.

Câu lệnh trên nôm na là Chọn (select) tất cả các cột (*) ở vùng dữ liệu (From) [SHEET1$A1:M65536] với điều kiện là cột L không có dòng không có dữ liệu (F12 is not null).
Như vậy F12 là gì? F12 là quy định trong CSDL khi ta chọn truy vấn không chọn tiêu đề cột, mặc định nó sẽ cho ta tên cột từ F1 đến Fn theo thứ tự vùng dữ liệu được chọn.
Ví dụ tôi chọn vùng dữ liệu truy vấn là "B214" thì nó sẽ có số cột là F1= cột B, F2= cột C, F3= Cột D. Tương tự câu lệnh trên khi chọn vùng [SHEET1$A1:M65536] thì tên cột sẽ là F1= Cột A, F2=Cột B... F13=Cột M.
Bạn hỏi

với điều kiện là cột F = ký tự 'C' hoặc 'D' thì lấy dữ liệu không bạn

Cột F sẽ tương ứng là cột F6, như vậy câu lệnh sẽ là

Cách 1:

lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F6 like 'C' or F6 like 'D'

Cách 2:

lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F6 like '[CD]'

Cách 3:

lsSQL = "SELECT * FROM [SHEET1$A1:M65536] WHERE F6 in ('C','D')

 

[phuctq124]

cảm ơn bạn nhiều nha, mình làm được rồi

 

[thuatnguyen]

Anh xem giúp em file đính kèm, khi chạy báo lỗi , không biết phải do máy không?

 

[Ba Tê]

điều mình cần là gom 20 file này thành 1 file đó bạn, sau đó mình dùng file đó xử lý mà. Còn ý bạn nói là đưa dữ liệu lên để làm luôn? cái này không phải mình chỉ có 20 file này, mà ngày nào mình cũng phải làm cái báo cáo với 20 file đó, và dữ liệu thay đổi theo ngày. Nên khá mất thời gian, nên nhờ bạn giúp đỡ.

Lỡ làm bằng VBA rồi, bạn thử xem sao:
1/ Thêm 1 sheet DS_File, bạn phải liệt kê tên tất cả các file từ 1 đến 20 của bạn, .xls hay xlsx, hay xlsm gì đó phải ghi rõ
2/ Để tất cá các file này và file Kết quả chung 1 Folder
3/ Bấm Nút <Tong Hop> trong Sheet TongHop của File Ket qua
4/ File Ket qua này bạn có thể dùng cho 2007 hay 2010. Thử xem, tôi chưa thử được với các loại Office khác.

Public Sub Tong_GPE()
Application.ScreenUpdating = False
Dim dArr(1 To 65000, 1 To 13), I As Long, J As Long, K As Long, Pat As String, ArrFile(), N As Long, WbName As String
Pat = ThisWorkbook.Path & "\"
With Sheets("DS_File")
    ArrFile = .Range(.[B1], .[B65000].End(xlUp)).Value
    .[D1] = Pat
   WbName = ThisWorkbook.Name
End With
For N = 1 To UBound(ArrFile, 1)
    Workbooks.Open Filename:=Pat & ArrFile(N, 1)
    With Sheets("GPE")
        sArr = .Range(.[A4], .[A65000].End(xlUp)).Offset(-2).Resize(, 13).Value
        For I = 1 To UBound(sArr, 1)
            If IsNumeric(sArr(I, 1)) Then
                K = K + 1
                For J = 1 To 13
                    dArr(K, J) = sArr(I, J)
                Next J
            End If
        Next I
    End With
        ActiveWindow.Close
Next N
Windows(WbName).Activate
With Sheets("TongHop")
    .[A4:M65000].ClearContents
    .[A4].Resize(K, 13).Value = dArr
End With
Application.ScreenUpdating = True
End Sub

-------------------------------
Ái da! Bắt đầu xem và làm từ bài #9, giờ thì đã đến bài #16 rồi. Có "trớt quớt" không đây.

 

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

Anh xem giúp em file đính kèm, khi chạy báo lỗi , không biết phải do máy không?

Bạn kiểm tra lại file cần tổng hợp có tên sheet là Sheet1 không? và vùng dữ liệu cần tổng hợp là vùng A1:M65000

 

[ndu96081631]

Lỡ làm bằng VBA rồi, bạn thử xem sao:
1/ Thêm 1 sheet DS_File, bạn phải liệt kê tên tất cả các file từ 1 đến 20 của bạn, .xls hay xlsx, hay xlsm gì đó phải ghi rõ

Thay vì vậy, anh dùng cái này cho khỏe:

Dim vFile, Item
vFile = Application.GetOpenFilename("Excel Files, *.xls; *.xlsx; *.xlsm", , , , True)
If TypeName(vFile) = "Variant()" Then
  For Each Item In vFile
    ''MsgBox CStr(Item) <----CStr(Item) chính là đường dẫn các file
  Next
End If

 

[wolfbuff]

Xin chào các các thành viên GPE, mình là thành viên mới, có một vấn đề nhờ mọi người giúp:

Mình có 40 file excel khác nhau, có cấu trúc dữ liệu như file mẫu đính kèm (file nguồn).
Mình muốn kết hợp 40 file này lại thành một file. Vùng dữ liệu mình lấy là từ cột A (ngày) cho đến cột P (TỒN KHO). Vùng dữ liệu ở trong thì dài ngắn bất kỳ, có thể từ vài chục đến khoảng 1000 dòng, tùy theo số lượng giao dịch phát sinh.
Chỉ kết hợp lại thôi, giữ nguyên dòng cột y chang như vậy. Mong muốn của mình là có được file giống như file “kết quả mẫu” – .

Ngày nào mình cũng trừ lùi số phát sinh trong ngày trên các file nguồn, và các dòng phát sinh mình phải cập nhật thêm vào phía dưới các dữ liệu hiện có.

Mong các thành viên giúp đỡ mình nha, hiện tại muốn báo cáo mình phải làm thao tác copy và paste, mất nhiều thời gian và đôi khi không chính xác . Mình có tham khảo các comment giúp đỡ của các bạn ở trên nhưng mình chưa biết gì về VBA, nên không thể làm theo được .

Xin chân thành cảm ơn.