Tập hợp các file thành 1 file dữ kiện (2 người xem)

[thao82]

Chào các Huynh đệ!
đệ có một nhu cầu nhỏ như sau: đệ có khoảng gần 1.000 file. Mỗi file exel là tên 1 khách hàng (Các file exel đều có cầu trúc như file đính kèm).
Đệ cần tổng hợp tất cả các khách hàng của đệ thành 1 file exel mới với cơ sở dữ kiện được tập hợp từ thông tin của các ô mầu đỏ trong file.
Vậy các huynh có cách nào giúp đệ với chứ đệ mà nhặt thủ công kiểu này chắc phải mùa quýt sang năm đệ mới nhặt xong mất!
File của đệ là Exel 2003 các huynh ạ!
Rất mong sự giúp đỡ hỗ trợ từ các huynh!
Đệ cám ơn các huynh nhiều!
​

 

[Quang_Hải]

Mình đã nhặt xong rồi, nhưng giờ không biết đem đi đâu và cái file mới nó ra làm sao nên tạm thời để trong máy tính của mình

 

[Ba Tê]

Mình thì không phải huynh, không phải đệ, tính sao đây?

 

[thanhsangnguyen1982]

You đưa dữ liệu lên đầy đử đi, mọi người mới giúp được. Vấn đề của You chỉ là chuyện nhỏ thôi.

 

[thao82]

Đệ mà attack dữ liệu lên thì không đủ! (kể cả nén file rồi gửi) vậy mới nhờ cậy các huynh! Nói tóm lại đệ cần nhặt các file đấy thành 1 file có dữ liệu kiểu như file đệ đính kèm. Các huynh giúp đệ với!

 

[Quang_Hải]

Đệ mà attack dữ liệu lên thì không đủ! (kể cả nén file rồi gửi) vậy mới nhờ cậy các huynh! Nói tóm lại đệ cần nhặt các file đấy thành 1 file có dữ liệu kiểu như file đệ đính kèm. Các huynh giúp đệ với!

Bạn copy code này vào 1 module trong file tonghop rồi chạy thử xem thế nào
Giờ bạn thử ngẫm nghĩ lại xem lúc đầu bạn nghĩ gì mà chỉ đưa có 1 file lên. Bạn nghĩ ai cũng thông minh như bạn hả? Không phải thế đâu nghen.

Sub tong_hop()
Dim FilesToOpen, X As Integer, CurrSh As Worksheet
Application.ScreenUpdating = False
Set CurrSh = ThisWorkbook.ActiveSheet
FilesToOpen = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)
    X = 1
    While X <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(X)
        With ActiveWorkbook
            With .ActiveSheet
               .[C5:C9].Copy
               CurrSh.[A65536].End(3).Offset(1).PasteSpecial 3, , , True
               .[E12:E31].Copy
               CurrSh.[F65536].End(3).Offset(1).PasteSpecial 3, , , True
               CurrSh.[Z65536].End(3).Offset(1) = .[C32]
            End With
        .Close False
        End With
        X = X + 1
    Wend
Application.ScreenUpdating = True
End Sub

 

[ndu96081631]

Bạn copy code này vào 1 module trong file tonghop rồi chạy thử xem thế nào
Giờ bạn thử ngẫm nghĩ lại xem lúc đầu bạn nghĩ gì mà chỉ đưa có 1 file lên. Bạn nghĩ ai cũng thông minh như bạn hả? Không phải thế đâu nghen.

Sub tong_hop()
Dim FilesToOpen, X As Integer, CurrSh As Worksheet
Application.ScreenUpdating = False
Set CurrSh = ThisWorkbook.ActiveSheet
FilesToOpen = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)
    X = 1
    While X <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(X)
        With ActiveWorkbook
            With .ActiveSheet
               .[C5:C9].Copy
               CurrSh.[A65536].End(3).Offset(1).PasteSpecial 3, , , True
               .[E12:E31].Copy
               CurrSh.[F65536].End(3).Offset(1).PasteSpecial 3, , , True
               CurrSh.[Z65536].End(3).Offset(1) = .[C32]
            End With
        .Close False
        End With
        X = X + 1
    Wend
Application.ScreenUpdating = True
End Sub

Người ta có đến 1000 file đấy đồng chí à! Open file rồi copy, paste... có mà chết
-------------------

Đệ mà attack dữ liệu lên thì không đủ! (kể cả nén file rồi gửi) vậy mới nhờ cậy các huynh! Nói tóm lại đệ cần nhặt các file đấy thành 1 file có dữ liệu kiểu như file đệ đính kèm. Các huynh giúp đệ với!

Bạn nên đưa lên đây khoảng 3 đến 5 file con để mọi người làm cho tiện (có 1 file duy nhất làm sao mà thí nghiệm)
Nếu file có dung lượng lớn, bạn nén toàn bộ chúng lại rồi hẳn gửi nhé

 

[VetMini]

.....

Bạn nên đưa lên đây khoảng 3 đến 5 file con để mọi người làm cho tiện (có 1 file duy nhất làm sao mà thí nghiệm)
Nếu file có dung lượng lớn, bạn nén toàn bộ chúng lại rồi hẳn gửi nhé

Giấy bán hàng có chi tiết người mua, người bán, số hàng bán (tối đa 17 món). Chỉ việc đem nó qua bảng tổng hợp thôi. 17 dòng hàng crosstab thành ra 17 cột. Cộng 3 cột tổng.

Thật sự tại chủ thớt lười biếng chứ 1000 files như vầy làm thủ công cũng mất vài ngày là cùng. Dân kế toán gì mà mới có bấy nhiêu dữ liệu đã lo nói chuyện "mùa quýt sang năm".

Mở file, copy phần chi tiết mua bán, paste transpose, copy 20 dòng chi tiết, paste transpose. Tất cả dưới 1 phút. 1000 file = dưới 20 giờ ==> tối đa một tuần lễ, kể cả đi uống trà.

 

[ndu96081631]

Giấy bán hàng có chi tiết người mua, người bán, số hàng bán (tối đa 17 món). Chỉ việc đem nó qua bảng tổng hợp thôi. 17 dòng hàng crosstab thành ra 17 cột. Cộng 3 cột tổng.

.

Đương nhiên tôi biết điều này, có điều tính tôi vốn cẩn thận, phải có file cụ thể để test thì mới làm. Trong topic này phải có ít nhất 4 file (1 file tổng hợp + 3 file con)
Nói chung là:
- Thứ nhất: Tôi không chắc bất cứ thứ gì khi tôi chưa thí nghiệm
- Thứ hai: Tôi rất lười cái vụ giả lập dữ liệu
---------------------------

Mở file, copy phần chi tiết mua bán, paste transpose, copy 20 dòng chi tiết, paste transpose. Tất cả dưới 1 phút. 1000 file = dưới 20 giờ ==> tối đa một tuần lễ, kể cả đi uống trà.

Dân tin học nói chung và Excel nói riêng mà làm vậy thì đúng là không thể chấp nhận được
Làm thế tôi thà mướn tên nào đó gõ giùm còn sướng hơn!

 

[VetMini]

Hông phải tôi xúi Bác đưa ra giải pháp trước khi nắm vững vấn đề. Chính tôi cũng rất bực mình khi làm việc với những người đưa thông tin một cách nhát gừng. Tuy nhiên đồng thời nhận xét của tôi cũng là nếu chính nguời hỏi không nắm vững tình trạng dữ liệu của mình thì bác có viết một cái code bao 100 trường hợp cũng còn bị sót trường hợp thứ 101.

Lập trình: cũng không phải tôi nói rằng vấn đề này không cần hay không nên viết code. Tôi chỉ đưa ra điểm chủ thớt nói chuyện con số 1000 như là ghê gớm lắm vậy. (tôi là dân chuyên về dữ liệu chứ không phải tin học - chỉ vì hai cái nó dính líu với nhau nên tôi phải biết qua chút đỉnh về lập trình thôi)

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Mở file, copy phần chi tiết mua bán, paste transpose, copy 20 dòng chi tiết, paste transpose. Tất cả dưới 1 phút. 1000 file = dưới 20 giờ ==> tối đa một tuần lễ, kể cả đi uống trà.

Với công việc này lặp đi lặp lại bạn không chán sao? trong khi đó người ta chỉ click chuột, uống tách trà là có thể xong việc.

 

[thang314]

Trước đây, thú thực là trước thời điểm tham gia diễn đàn, tôi thường có kiểu làm việc rất thủ công, trong khi người khác chỉ cần 1 cái click chuột rồi họ đi chơi, mình hì hục làm mà hiệu quả đâu có cao. sau khi tham gia diễn đàn tôi đã tập dần theo cách giảm dần việc làm thủ công đi và đã làm được theo ý của mình khá nhiều, cũng giúp được nhiều bạn khác. tôi cũng như thầy ndu khi làm 1 file nào đó và chạy thử theo ý của chủ thớt, cần có luôn dữ liệu cụ thể để mình chỉ làm 1 lần đỡ phải hỏi đi hỏi lại mất công. tổng hợp 10, 100 hay 10000 file không quan trọng. có dữ liệu cụ thể sẽ có cách khắc phục luôn thôi. còn lấy dữ liệu tự tạo để làm. tôi còn làm biếng hơn cả... ( xin lỗi..)

 

[thao82]

Đệ chưa test lại nhưng rất cám ơn các huynh đã giúp đỡ! Vì file của đệ mỗi ngày một dầy thêm mà không phải lúc nào đệ cũng nhờ cậy các huynh được!
Một lần nữa rất chân thành cám ơn các huynh!

 

[thao82]

Đệ đã test lại rồi! và đã xử lý xong phần việc của mình trong buổi tối hôm qua! rất cám ơn các huynh đã giúp đỡ đệ. Tuy nhiên, đệ cũng có một câu hỏi khác là các huynh có thể làm cách nào mà đệ chạy macro này nó có thể tự động mở tất cả các file trong cùng 1 folder tổng hợp thành 1 file như đệ nói không?
Tức là đệ có 1 folder. Trong Folder đó có các file khách hàng của đệ và có 01 file là file tổng hợp như đã trình bầy phía trên. Đệ muốn viết một cái macro trong file tổng hợp để mình chỉ cần chạy macro này thì nó sẽ tự động mở các file trong folder đó tự động cập nhật vào các file tổng hợp của đệ.
Một lần nữa chân thành cám ơn sự giúp đỡ của các huynh!

 

[thao82]

Xin lỗi các huynh, đệ quên chưa attack file để các huynh giúp đỡ đệ với!

 

[ndu96081631]

Xin lỗi các huynh, đệ quên chưa attack file để các huynh giúp đỡ đệ với!

OK, làm cho bạn bằng giải pháp ADO
1> Code lấy dữ liệu:

Function GetData(ByVal FileName As String, ByVal SheetName As String, ByVal RangeAddress As String, _
            ByVal HasTitle As Boolean, ByVal UseTitle As Boolean)
            
  Dim rsCon As Object, rsData As Object, cat As Object, tbl As Object
  Dim tmpArr, Arr()
  Dim szConnect As String, szSQL As String
  Dim lCount As Long, lR As Long, lC As Long
  
  Set rsCon = CreateObject("ADODB.Connection")
  Set rsData = CreateObject("ADODB.Recordset")
  Set cat = CreateObject("ADOX.Catalog")
  
  If Val(Application.Version) < 12 Then
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  Else
    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 12.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  End If
  
  rsCon.Open szConnect
  cat.ActiveConnection = rsCon
  
  If SheetName = "" Then
    For Each tbl In cat.Tables
      If (Right(tbl.Name, 1) = "$") Or (Right(tbl.Name, 2) = "$'") Then
        SheetName = tbl.Name
        Exit For
      End If
    Next
  End If
  If Right(SheetName, 1) = "'" Then SheetName = Mid(SheetName, 2, Len(SheetName) - 2)
  If Right(SheetName, 1) <> "$" Then SheetName = SheetName & "$"
  szSQL = "SELECT * FROM [" & SheetName & RangeAddress & "];"
  rsData.Open szSQL, rsCon, 0, 1, 1
  tmpArr = rsData.GetRows
'  ReDim Arr(UBound(tmpArr, 2) - UseTitle, UBound(tmpArr, 1) + 1)
'  If UseTitle Then
'    For lC = LBound(tmpArr, 1) To UBound(tmpArr, 1)
'      Arr(0, lC) = rsData.Fields(lC).Name
'    Next
'  End If
'  For lR = LBound(tmpArr, 2) To UBound(tmpArr, 2)
'    For lC = LBound(tmpArr, 1) To UBound(tmpArr, 1)
'      Arr(lR - UseTitle, lC) = tmpArr(lC, lR)
'    Next
'  Next
  
  rsData.Close: Set rsData = Nothing
  rsCon.Close: Set rsCon = Nothing
  GetData = tmpArr
End Function

Mấy đoạn có dấu nháy đơn là tôi bỏ đi cho phù hợp với thực tế, vì đàng nào bạn cũng không cần xoay dọc dữ liệu
2> Code chính:

Sub Main()
  Dim vFile, fleItem, valItem, Arr(), tmpArr
  Dim wkb As Workbook
  Dim SheetName As String, RangeAddress As String
  Dim lC As Long, n As Long
  On Error Resume Next
  vFile = Application.GetOpenFilename("Excel Files, *.xls;*.xlsx;*.xlsm", , , , True)
  If TypeName(vFile) = "Variant()" Then
    Set wkb = ThisWorkbook
    SheetName = "Sheet1"
    RangeAddress = "C5:E32"
    ReDim Arr(1 To UBound(vFile), 1 To 26)
    For Each fleItem In vFile
      If CStr(fleItem) <> wkb.FullName Then
        tmpArr = GetData(fleItem, SheetName, RangeAddress, False, False)
        n = n + 1
        For lC = 1 To 26
          Select Case lC
            Case 1 To 5: Arr(n, lC) = tmpArr(0, lC - 1)
            Case 6 To 25: Arr(n, lC) = tmpArr(2, lC + 1)
            Case 26: Arr(n, lC) = tmpArr(0, lC + 1)
          End Select
        Next
      End If
    Next
    If n Then wkb.ActiveSheet.Range("A60000").End(xlUp).Offset(1).Resize(n, 26).Value = Arr
  End If
End Sub

3> Cách dùng:
- Mở file TongHop.xls
- Bấm nút 'Run Code'
- Hộp Open file mở ra, bạn dùng chuột chọn file đầu, bấm giữ phím Shift rồi chọn file cuối. Bấm nút Open, code sẽ tự chạy
------------------------------
Lưu ý: Tôi không chắc về tốc độ của code nên bước đầu bạn nên chọn 1 lần chừng độ 100 file trở lại. Nếu thấy tốc độ chấp nhận được thì hãy chọn số lượng file nhiều hơn

 

[VetMini]

Với công việc này lặp đi lặp lại bạn không chán sao? trong khi đó người ta chỉ click chuột, uống tách trà là có thể xong việc.

Lý luận này chỉ đúng cho sinh viên làm bài tập. Trên thực tế, người làm việc không có quyền nói đến từ "chán" (chán thì nghỉ việc đi). Cái đáng lẽ bạn cần nêu ra là "dùng lập trinh giải quyết con sô lớn như thế với mục đích chính là tránh được lỗi sơ suất" (làm thủ công cứ khoảng 100 cái lại buồn ngủ lỡ tay sai đâu đó)

Bác ndu96081631:

Nếu tôi giải quyết vấn đề này thì tôi không chú trọng tốc độ. Loại công việc này chạy nửa ngày cũng chẳng chết ai.

Điểm quan trọng tôi cần biết là nguồn gốc của 1000 files kia. Nếu nguồn gốc chúng từ một phần mềm nào đó trích ra thì khả năng chúng đồng bộ khá cao, giải quyết bằng cách đọc đơn giản là đủ. Nhưng nếu chúng do hốt ở đâu đó về (trông giống như import kết quả từ phiếu bán hàng ở chi nhánh) thì chưa chắc dữ liệu đã đồng bộ. File nào bị trệch 1 dòng hay cột thì kết quả sẽ sai.

Trường hợp kết quả sai mà crash trong lúc chạy thì không sao, chỉ việc chỉnh lại. Nhưng nếu sai do đọc khoong đúng chỗ thì rất tai hại - vì cuối cùng ta không biết là sai.

Để giải quyết trường hợp đọc vùng dữ liệu của nhiều files như thế này, người ta dùng phương pháp định vị dữ liệu (data alignment). Tức là đọc một vài ô chung quanh vùng dữ liệu xem có đúng như mẫu hay không - số ô cần đọc và vị trí chung quanh được chọn tuỳ theo độ phức tạp của vùng dữ liệu.

Giải quyết sai số, có hai cách:

1. dùng code đọc qua tất cả các files xem có đồng bộ? nếu đồng bộ thì chạy code chép dữ liệu.

2. code vừa xét tính chất đồng bộ vừa chép. Những files không đồng bộ thì ghi tên lại trong log. Chạy xong, đọc log và chỉnh sửa.

 

[ndu96081631]

Bác ndu96081631:

Nếu tôi giải quyết vấn đề này thì tôi không chú trọng tốc độ. Loại công việc này chạy nửa ngày cũng chẳng chết ai.
.

Mỗi người 1 cá tính mà. Ác cái tôi lại thích.. ĐUA XE (cả 2 nghĩa) nên đừng nói là nửa ngày, dù là nửa phút tôi cũng không chấp nhận
Ẹc... Ẹc...
------------------------

Điểm quan trọng tôi cần biết là nguồn gốc của 1000 files kia. Nếu nguồn gốc chúng từ một phần mềm nào đó trích ra thì khả năng chúng đồng bộ khá cao, giải quyết bằng cách đọc đơn giản là đủ. Nhưng nếu chúng do hốt ở đâu đó về (trông giống như import kết quả từ phiếu bán hàng ở chi nhánh) thì chưa chắc dữ liệu đã đồng bộ. File nào bị trệch 1 dòng hay cột thì kết quả sẽ sai.

Đã nói đến CSDL thì phải CHUẨN, bằng ngược lại thì thôi, nghỉ xài vậy! Lúc ấy ta "nhặt" bằng tay như bác đã nói, 1 tháng cũng xong!
Ẹc... Ẹc...

 

[VMH0307]

Đúng là cái tai hại của "tự động hóa" là nó quá tự động, hic. Với những dữ liệu kế toán, tài chính quan trọng, em cũng chịu khó lọc bằng tay hết, tránh sai có hệ thống, còn những dữ liệu ít quan trọng hơn, chắc chắn đồng bộ thì dùng ADO.

Giải quyết sai số, có hai cách:

1. dùng code đọc qua tất cả các files xem có đồng bộ? nếu đồng bộ thì chạy code chép dữ liệu.

2. code vừa xét tính chất đồng bộ vừa chép. Những files không đồng bộ thì ghi tên lại trong log. Chạy xong, đọc log và chỉnh sửa.

Cái này em đọc thấy hay hay, em chưa nghĩ đến phương án giải quyết như bác nói ở trên (do trình độ có hạn), hồi trước có mày mò làm thử ghi log nhưng cũng chưa vào đâu, hichic => mong được bác chỉ giáo về 2 giải pháp trên trong VBA.
Không biết thêm 02 giải pháp trên, code có ì ạch không nhỉ?

 

[nguyenhoang_gpe]

Em muốn nhờ các bác làm hộ em công việc tương tự như vậy nhưng cấu trúc khác 1 chút, em gửi file: 1 file tổng hợp và các file con, các file con được copy vào các cột. Cám ơn các bác !