Tổng hợp dữ liệu không cần mở File (1 người xem)

  • Thread starter Thread starter huy vu
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
H

huy vu

Thành viên hoạt động
Tham gia
29/2/12
Bài viết
164
Được thích
1
Em có câu hỏi sau, mong các thành viên giúp đỡ.
Em có file Tổng hợp, và nhiều file Số tiền các tháng T1, T2, T3....
Cấu trúc các file:
- File Tong Hop gồm trường: Code, Sotien1, VAT1, Sotien2.....
- File số tiền gồm 3 trường: Code, SoTien, VAT
Bình thường e hay copy dữ liệu file T1, T2... vào các sheet của file Tổng hợp
Rồi từ đó dùng phương thức Find để tìm kiếm theo Code của sheet Tổng hợp từ các sheet T1, T2...
Bài toán đặt ra là dữ liệu T1, T2 càng ngày càng nhiều, copy vào file Tổng hợp làm nặng file.
Vậy có cách nào mà Tổng hợp dữ liệu không cần copy các file T1, T2 vào các sheet của file tổng hợp không?
Tổng hợp dữ liệu không cần mở file. Hình như ADO có thể làm được việc này, hoặc bằng phương thức khác, mong các thành viên giúp đỡ!!!
Em xin chân thành cảm ơn!
 

File đính kèm

Sắp xếp theo thời gian sắp xếp theo bầu chọn
Không mở file e là khó, thôi em dùng cách copy dữ liệu vào các sheet cũng được.
tuy nhiên em cũng muốn hỏi thêm là: nêis có 12 sheet tương đương với 12 tháng (T1 -> T12), mà khai báo và set tới 12 cái Rng, rồi từng Rng lại khớp từng mảng, thì code dài quá.
Mong anh/chi có cách nào tốt hơn.
 
Upvote 0
Huy Vũ đã viết:
Không mở file e là khó, thôi em dùng cách copy dữ liệu vào các sheet cũng được.
tuy nhiên em cũng muốn hỏi thêm là: nêis có 12 sheet tương đương với 12 tháng (T1 -> T12), mà khai báo và set tới 12 cái Rng, rồi từng Rng lại khớp từng mảng, thì code dài quá.
Mong anh/chi có cách nào tốt hơn.
Nhấp chuột vào đây để mở rộng...
Thay vì copy thủ công dữ liệu vào từng sheet, tôi có thể giúp bạn Import dữ liệu tự động từng file vào từng sheet, ( còn mà tự động lấy dữ liệu ko cần cop vào sheet thì tôi chưa có giải pháp )
 
Lần chỉnh sửa cuối:
Upvote 0
Một giải pháp hay, mong bạn Cá Ngừ F1 giúp đỡ, vì tôi cũng có tình huống như vậy. Cảm ơn!
 
Upvote 0
Tôi mách cho bạn giải pháp. Code VBA để thực hiện các giai đoạn trong giải pháp thì trên diễn đàn có cả đống. Bạn chịu khó tìm sẽ ra.

1. Tạo một thư mục "CanTongHop", và một thư mục "DaTongHop"
2. copy các files cần tổng hợp vào CanTongHop
3. chạy code VBA trong file tổng hợp. Code này sẽ duyệt thư mục CanTongHop:
3.1. mở từng file trong CanTongHop.
3.2. đọc dữ liệu, tổng hợp
3.3. cập nhật file tổng hợp
3.4. dời file đã đọc vào DaTongHop
4. Hêt. Nếu cẩn thận thì ghi thêm chi tiết báo cáo ngày tổng hợp.

Bước 1,2, và 3.4 bảo đảm rằng bạn không bị nhầm lẫn, cộng dồn dữ liệu nhiều lần.
Bước 3.2 có thể dùng ADO để tổng hợp gọn gàng nếu dữ liệu bạn được xếp đúng chuẩn CSDLLH.
 
Upvote 0
nguyen6571gpex đã viết:
Một giải pháp hay, mong bạn Cá Ngừ F1 giúp đỡ, vì tôi cũng có tình huống như vậy. Cảm ơn!
Nhấp chuột vào đây để mở rộng...
Xin trả lời bài này. Mở file TongHop
B1. Copy Code này vào 1 Module
Mã: 
Function GetData(ByVal FileName As String, ByVal SheetName As String, ByVal RangeAddress As String, _
ByVal HasTitle As Boolean, ByVal UseTitle As Boolean)
 
    Dim cnn As Object, rsData As Object
    Dim tmpArr, Arr
    Dim szConn As String, szSQL As String, tmp As String
    Dim lR As Long, lC As Long, lVersn As Long
    On Error GoTo ErrHandler
        lVersn = Val(Application.Version)
    Set cnn = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")
 
    If lVersn < 12 Then
        szConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=" & IIf(HasTitle, "Yes", "No") & ";IMEX=1"";"
    Else
        szConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
        "Extended Properties=""Excel 12.0;HDR=" & IIf(HasTitle, "Yes", "No") & ";IMEX=1"";"
    End If
    If SheetName = "" Then
    Dim Dbs As Object, db As Object
    Set Dbs = CreateObject("DAO.DBEngine." & IIf(lVersn < 12, "36", "120"))
    Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
    tmp = db.TableDefs(0).Name
    tmp = Replace(tmp, "''", "'")
    SheetName = tmp
    db.Close
    Set Dbs = Nothing: Set db = Nothing
    Else
    SheetName = SheetName & "$"
    End If
    cnn.Open szConn
    szSQL = "SELECT * FROM [" & SheetName & RangeAddress & "];"
    rsData.Open szSQL, cnn, 1, 1
    tmpArr = rsData.GetRows
    ReDim Arr(UBound(tmpArr, 2) - UseTitle, UBound(tmpArr, 1))
    If UseTitle Then
    For lC = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    Arr(0, lC) = rsData.Fields(lC).Name
    Next
    End If
    rsData.Close: cnn.Close
    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
    GetData = Arr
    Set rsData = Nothing: Set cnn = Nothing
    Exit Function
ErrHandler:
MsgBox Err.Description
Set rsData = Nothing: Set cnn = Nothing
End Function

B2. Copy Code này vào 1 module
Mã: 
Sub LayDuLieu()
    T1
    T2
    T3
End Sub
Sub T1()
  Dim FileName As String, SheetName As String, RangeAddress As String
  Dim Arr
  Sheet1.[A1].CurrentRegion.Clear ' xoa neu sheet co du lieu
  FileName = ThisWorkbook.Path & "\" & Sheet5.[A2]
  Arr = GetData(FileName, SheetName, RangeAddress, True, True)
    If IsArray(Arr) Then
      ThisWorkbook.Sheets("T1").Range("A1").Resize(UBound(Arr, 1) + 1, _
      UBound(Arr, 2) + 1).Value = Arr
    End If
End Sub
Sub T2()
  Dim FileName As String, SheetName As String, RangeAddress As String
  Dim Arr
  Sheet2.[A1].CurrentRegion.Clear ' xoa neu sheet co du lieu
  FileName = ThisWorkbook.Path & "\" & Sheet5.[A3]
  Arr = GetData(FileName, SheetName, RangeAddress, True, True)
    If IsArray(Arr) Then
      ThisWorkbook.Sheets("T2").Range("A1").Resize(UBound(Arr, 1) + 1, _
      UBound(Arr, 2) + 1).Value = Arr
    End If
End Sub
Sub T3()
  Dim FileName As String, SheetName As String, RangeAddress As String
  Dim Arr
  Sheet3.[A1].CurrentRegion.Clear ' xoa neu sheet co du lieu
  FileName = ThisWorkbook.Path & "\" & Sheet5.[A4]
  Arr = GetData(FileName, SheetName, RangeAddress, True, True)
    If IsArray(Arr) Then
      ThisWorkbook.Sheets("T3").Range("A1").Resize(UBound(Arr, 1) + 1, _
      UBound(Arr, 2) + 1).Value = Arr
    End If
End Sub
Chạy thủ tục LayDuLieu.
Code vẫn còn nhiều cái chưa bẫy lỗi, ví dụ nếu không có các file T1, T2...
Có gì mong các thành viên chỉ dậy thêm.
P/s: Lưu ý, các file để cùng Folder.
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Huy Vũ đã viết:
Em có câu hỏi sau, mong các thành viên giúp đỡ.
Em có file Tổng hợp, và nhiều file Số tiền các tháng T1, T2, T3....
Cấu trúc các file:
- File Tong Hop gồm trường: Code, Sotien1, VAT1, Sotien2.....
- File số tiền gồm 3 trường: Code, SoTien, VAT
Bình thường e hay copy dữ liệu file T1, T2... vào các sheet của file Tổng hợp
Rồi từ đó dùng phương thức Find để tìm kiếm theo Code của sheet Tổng hợp từ các sheet T1, T2...
Bài toán đặt ra là dữ liệu T1, T2 càng ngày càng nhiều, copy vào file Tổng hợp làm nặng file.
Vậy có cách nào mà Tổng hợp dữ liệu không cần copy các file T1, T2 vào các sheet của file tổng hợp không?
Tổng hợp dữ liệu không cần mở file. Hình như ADO có thể làm được việc này, hoặc bằng phương thức khác, mong các thành viên giúp đỡ!!!
Em xin chân thành cảm ơn!
Nhấp chuột vào đây để mở rộng...

bài này bạn có thể sử dụng ADO hoặc VBA lấy dữ liệu các T1,T2... lên trên sheet hết sau đó xử lý cũng được mà
 
Upvote 0
VetMini đã viết:
Tôi mách cho bạn giải pháp. Code VBA để thực hiện các giai đoạn trong giải pháp thì trên diễn đàn có cả đống. Bạn chịu khó tìm sẽ ra.

1. Tạo một thư mục "CanTongHop", và một thư mục "DaTongHop"
2. copy các files cần tổng hợp vào CanTongHop
3. chạy code VBA trong file tổng hợp. Code này sẽ duyệt thư mục CanTongHop:
3.1. mở từng file trong CanTongHop.
3.2. đọc dữ liệu, tổng hợp
3.3. cập nhật file tổng hợp
3.4. dời file đã đọc vào DaTongHop
4. Hêt. Nếu cẩn thận thì ghi thêm chi tiết báo cáo ngày tổng hợp.

Bước 1,2, và 3.4 bảo đảm rằng bạn không bị nhầm lẫn, cộng dồn dữ liệu nhiều lần.
Bước 3.2 có thể dùng ADO để tổng hợp gọn gàng nếu dữ liệu bạn được xếp đúng chuẩn CSDLLH.
Nhấp chuột vào đây để mở rộng...
Cảm ơn anh đã chỉ ra logic bài toán, tuy nhiên code em còn kém cỏi lắm, mong anh chỉ dậy thêm.

Code anh Cá Ngừ chạy vèo vèo, em cảm ơn nhiều.
 
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Huy Vũ đã viết:
Mong em chỉ dậy thêm.
Nhấp chuột vào đây để mở rộng...

Bạn thử tham khảo sử dụng ADO lấy hết lên trên Sheet nhé. nếu muốn tách từng T1,T2 một thì sửa lại code
(khi nào cần thì lấy lên ko thì thôi cho nhẹ File)
code này có một người bạn cho mình mình chỉ sủa lại một tí cho bạn thôi nha
sau khi lấy lên nếu các mã code trùng nhau ta dùng Dic hoặc ... tổng hợp cộng lại theo mã code
nếu bạn làm theo hướng đó thì mình viết tiếp cho bạn một code Dic nữa nếu ko từ từ ta nghiên cứu tiếp
 

File đính kèm

Upvote 0
kieu manh đã viết:
Bạn thử tham khảo sử dụng ADO lấy hết lên trên Sheet nhé. nếu muốn tách từng T1,T2 một thì sửa lại code
(khi nào cần thì lấy lên ko thì thôi cho nhẹ File)
code này có một người bạn cho mình mình chỉ sủa lại một tí cho bạn thôi nha
sau khi lấy lên nếu các mã code trùng nhau ta dùng Dic hoặc ... tổng hợp cộng lại theo mã code
nếu bạn làm theo hướng đó thì mình viết tiếp cho bạn một code Dic nữa nếu ko từ từ ta nghiên cứu tiếp
Nhấp chuột vào đây để mở rộng...
Code chạy tốt anh ạ, tuy nhiên phải copy vào 3 sheet khác nhau, khi đó ở sheet TongHop mới tìm kiếm theo từng T1, T2 chứ anh nhỉ, vì copy chung hết vào 1 sheet thì biết tham chiếu đến vùng nào?
Tách ra tốt hơn anh ạ, vì code Find ở bài đầu em tham chiếu đến từng sheet T để tìm kiếm theo code.
Mong anh và mọi người có tư vấn thêm.
 
Upvote 0
For I = 0 To UBound(Files)
Set ObjConn = GetExcelConnection(Path & "\" & Files(I), 0)

StrRequest = "SELECT * FROM [$A2:E10000]"

RS.Open StrRequest, ObjConn, 3, 2

Sheet1.[A65536].End(3).Offset(1).CopyFromRecordset RS
ObjConn.Close
Next
Nhấp chuột vào đây để mở rộng...
Xin hỏi trong câu lệnh này tôi đâu thấy cái nào là chỉ tới sheet đầu tiên đâu mà nó lại lấy dữ liệu sheet đầu tiên, bây giờ giải sử mình muốn lấy dữ liệu sheet thứ 2 thì làm sao? xin cảm ơn
 
Upvote 0
phihndhsp đã viết:
Xin hỏi trong câu lệnh này tôi đâu thấy cái nào là chỉ tới sheet đầu tiên đâu mà nó lại lấy dữ liệu sheet đầu tiên, bây giờ giải sử mình muốn lấy dữ liệu sheet thứ 2 thì làm sao? xin cảm ơn
Nhấp chuột vào đây để mở rộng...

Câu này nè
Set ObjConn = GetExcelConnection(Path & "\" & Files(I), 0)

Mấy dạng tổng hợp này mình viết code là hơi ngon đó, khỏi cần ADO và khỏi cần copy gì ráo.
 
Upvote 0
quanghai1969 đã viết:
Câu này nè
Set ObjConn = GetExcelConnection(Path & "\" & Files(I), 0)

Mấy dạng tổng hợp này mình viết code là hơi ngon đó, khỏi cần ADO và khỏi cần copy gì ráo.
Nhấp chuột vào đây để mở rộng...
bây giờ mình muốn lấy dữ liệu sheet thứ 2 thì thay tham số nào trong này anh Hải
 
Upvote 0
Huy Vũ đã viết:
Code chạy tốt anh ạ, tuy nhiên phải copy vào 3 sheet khác nhau, khi đó ở sheet TongHop mới tìm kiếm theo từng T1, T2 chứ anh nhỉ, vì copy chung hết vào 1 sheet thì biết tham chiếu đến vùng nào?
Tách ra tốt hơn anh ạ, vì code Find ở bài đầu em tham chiếu đến từng sheet T để tìm kiếm theo code.
Mong anh và mọi người có tư vấn thêm.
Nhấp chuột vào đây để mở rộng...

bạn muốn tách ra theo T1,T2... thì tham khảo file này nha khi bấm vào T1 thì nó lấy T1 lên khi ra nó xoá hết ..cho nhẹ file nếu không thích thì xoá code đó đi
làm Sheet>TongHop riêng... nếu làm theo hướng này thì mình tính tiếp
PHP: 
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim File_Can_Mo As String, Tam()
File_Can_Mo = ThisWorkbook.Path & "\T3.xlsb"
Workbooks.Open File_Can_Mo
With ActiveWorkbook
   With .ActiveSheet
      Tam = .Range("A2", .[A65536].End(3)).Resize(, 3).Value
   End With
   .Close False
End With
ActiveSheet.Range("A2").Resize(UBound(Tam), 3) = Tam
Application.ScreenUpdating = True
End Sub
Nếu ko muốn ra nó xoá thì xoá code này đi nha
PHP: 
Private Sub Worksheet_Deactivate()
    Sheet4.Range("A1").CurrentRegion.Offset(1).ClearContents
End Sub
 

File đính kèm

Upvote 0
Huy Vũ đã viết:
Code chạy tốt anh ạ, tuy nhiên phải copy vào 3 sheet khác nhau, khi đó ở sheet TongHop mới tìm kiếm theo từng T1, T2 chứ anh nhỉ, vì copy chung hết vào 1 sheet thì biết tham chiếu đến vùng nào?
Tách ra tốt hơn anh ạ, vì code Find ở bài đầu em tham chiếu đến từng sheet T để tìm kiếm theo code.
Mong anh và mọi người có tư vấn thêm.
Nhấp chuột vào đây để mở rộng...
Thử cái rừng này coi sao, cũng chưa hiểu hết ý bạn muốn tổng hợp kiểu gì
PHP: 
Sub Tonghop()
Dim fso As Object, Dic As Object
Dim ObjFile As Object, i&, n&, k&, x&, path$
Dim tieude(), Res(), Data(), Code(1 To 65536, 1 To 1)
path = ThisWorkbook.path
Set fso = CreateObject("Scripting.FileSystemObject")
Set Dic = CreateObject("scripting.dictionary")
With fso.GetFolder(ThisWorkbook.path)
   For Each ObjFile In .Files
      If fso.GetExtensionName(ObjFile) Like "xls*" Then
         If Left(ObjFile.Name, 2) <> "~$" Then
            If ObjFile.Name <> ThisWorkbook.Name Then
               n = n + 2
               With Workbooks.Open(ObjFile.Name, 0)
                  With .ActiveSheet
                     Data = .Range("A2", .[C65536].End(3)).Value
                     ReDim Preserve Res(1 To 65536, 1 To n)
                     ReDim Preserve tieude(1 To n)
                     tieude(n - 1) = fso.getbaseName(ObjFile)
                     For i = 1 To UBound(Data)
                        If Not Dic.exists(Data(i, 1)) Then
                           k = k + 1
                           Dic.Add Data(i, 1), k
                           Code(k, 1) = Data(i, 1)
                           Res(k, n - 1) = Data(i, 2)
                           Res(k, n) = Data(i, 3)
                        Else
                           x = Dic.Item(Data(i, 1))
                           Res(x, n - 1) = Data(i, 2)
                           Res(x, n) = Data(i, 3)
                        End If
                     Next
                  End With
                  .Close False
               End With
            End If
         End If
      End If
   Next
End With
Sheets("DATA").[D4].Resize(k) = Code
Sheets("DATA").[O2].Resize(, n) = tieude
Sheets("DATA").[O4].Resize(k, n) = Res
End Sub
kieu manh đã viết:
Vụ này mình đang học bạn chưa hết ....mong bạn chỉ dùm đi
làm khó ngại chết được
Nhấp chuột vào đây để mở rộng...
Cái nào khó quá thì cho qua đi. Từ từ rồi tính sau.
 
Upvote 0
BÀI 20 Em test lỗi dòng này Anh
With Workbooks.Open(ObjFile.Name, 0)
 
Upvote 0
kieu manh đã viết:
Thì bạn sửa lại vầy đi
PHP: 
StrRequest = "SELECT * FROM [Sheet2$A2:E10000]"
Nhấp chuột vào đây để mở rộng...
cũng làm như vậy đó vì nhưng mà không được, vì hồi chiều có xem mục ADO của anh hai lúa miền tây, thấy cái này hay hay muốn tìm hiểu thôi, hình như nó khác 1 ít với các câu lệnh trước kia học SQL
 
Upvote 0
quanghai1969 đã viết:
Thử cái rừng này coi sao, cũng chưa hiểu hết ý bạn muốn tổng hợp kiểu gì
PHP: 
Sub Tonghop()
Dim fso As Object, Dic As Object
Dim ObjFile As Object, i&, n&, k&, x&, path$
Dim tieude(), Res(), Data(), Code(1 To 65536, 1 To 1)
path = ThisWorkbook.path
Set fso = CreateObject("Scripting.FileSystemObject")
Set Dic = CreateObject("scripting.dictionary")
With fso.GetFolder(ThisWorkbook.path)
   For Each ObjFile In .Files
      If fso.GetExtensionName(ObjFile) Like "xls*" Then
         If Left(ObjFile.Name, 2) <> "~$" Then
            If ObjFile.Name <> ThisWorkbook.Name Then
               n = n + 2
               With Workbooks.Open(ObjFile.Name, 0)
                  With .ActiveSheet
                     Data = .Range("A2", .[C65536].End(3)).Value
                     ReDim Preserve Res(1 To 65536, 1 To n)
                     ReDim Preserve tieude(1 To n)
                     tieude(n - 1) = fso.getbaseName(ObjFile)
                     For i = 1 To UBound(Data)
                        If Not Dic.exists(Data(i, 1)) Then
                           k = k + 1
                           Dic.Add Data(i, 1), k
                           Code(k, 1) = Data(i, 1)
                           Res(k, n - 1) = Data(i, 2)
                           Res(k, n) = Data(i, 3)
                        Else
                           x = Dic.Item(Data(i, 1))
                           Res(x, n - 1) = Data(i, 2)
                           Res(x, n) = Data(i, 3)
                        End If
                     Next
                  End With
                  .Close False
               End With
            End If
         End If
      End If
   Next
End With
Sheets("DATA").[D4].Resize(k) = Code
Sheets("DATA").[O2].Resize(, n) = tieude
Sheets("DATA").[O4].Resize(k, n) = Res
End Sub

Cái nào khó quá thì cho qua đi. Từ từ rồi tính sau.
Nhấp chuột vào đây để mở rộng...
Code của anh Hải nhìn như tranh vẽ, trông thích quá, cảm ơn anh. Để em test xem sao.
 
Upvote 0
theo như bài 10, thì mỗi file con chỉ có 1 sheet thôi, bây giờ giả sử như mỗi file con có 3 sheet và ở file tổng hợp có 3 sheet tương ứng, bây giờ mình muốn lấy dữ liệu từ các file con vào file lớn, giống như bài 10, mà mở rộng thêm nhiều sheet thôi
tôi có thấy file của anh hai lúa như thế này
StrRequest = "SELECT * FROM [Sheet2$A2:E10000]"
nhưng áp dụng trong code thì bị lỗi, xin cảm ơn
 
Upvote 0
phihndhsp đã viết:
theo như bài 10, thì mỗi file con chỉ có 1 sheet thôi, bây giờ giả sử như mỗi file con có 3 sheet và ở file tổng hợp có 3 sheet tương ứng, bây giờ mình muốn lấy dữ liệu từ các file con vào file lớn, giống như bài 10, mà mở rộng thêm nhiều sheet thôi
tôi có thấy file của anh hai lúa như thế này
StrRequest = "SELECT * FROM [Sheet2$A2:E10000]"
nhưng áp dụng trong code thì bị lỗi, xin cảm ơn
Nhấp chuột vào đây để mở rộng...
Tôi tạm viết như sau, bạn tự chỉnh và phát triển ra nhé.

Mã: 
Sub Main()
    Dim ObjConn As Object, RS As Object, Files, sht
    Dim StrRequest As String, Path As String, i As Integer, iSht As Integer
    Path = ThisWorkbook.Path
    Files = Array("T1.xlsb", "T2.xlsb", "T3.xlsb")
    sht = Array("T1", "T2", "T3")
    Set RS = CreateObject("ADODB.Recordset")

    For i = 0 To UBound(Files)
        Set ObjConn = GetExcelConnection(Path & "\" & Files(i), 0)
        StrRequest = ""
        For iSht = 0 To UBound(sht)
            StrRequest = "SELECT * FROM [" & sht(iSht) & "$A2:E10000]"
            RS.Open StrRequest, ObjConn, 3, 1
            Sheets(sht(iSht)).[A65536].End(3).Offset(1).CopyFromRecordset RS
            RS.Close
        Next
        ObjConn.Close
    Next
    Set RS = Nothing
End Sub
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
phihndhsp đã viết:
cũng làm như vậy đó vì nhưng mà không được, vì hồi chiều có xem mục ADO của anh hai lúa miền tây, thấy cái này hay hay muốn tìm hiểu thôi, hình như nó khác 1 ít với các câu lệnh trước kia học SQL
Nhấp chuột vào đây để mở rộng...

StrRequest = "SELECT * FROM [Sheet2$A2:E10000]"

Câu lệnh này hoàn toàn căn bản. Đâu có chỗ nào đặc biệt mà gọi là khác?
 
Upvote 0
Giải sử trong folder em có 3 file con có dữ liệu như trong file, và 1 file tổng hợp, em muốn đưa dữ liệu từ các file con vào file tổng hợp như mẫu thì như thế nào? em có kèm theo file và có đáp số mẫu cần lấy trong file tổng hợp, em thấy cái này hay, chắc chắn trong tương lai phải cần tới nên tu luyện trước để dành đó. Xin cảm ơn anh hai lúa đã quan tâm
 

File đính kèm

Upvote 0
phihndhsp đã viết:
Giải sử trong folder em có 3 file con có dữ liệu như trong file, và 1 file tổng hợp, em muốn đưa dữ liệu từ các file con vào file tổng hợp như mẫu thì như thế nào? em có kèm theo file và có đáp số mẫu cần lấy trong file tổng hợp, em thấy cái này hay, chắc chắn trong tương lai phải cần tới nên tu luyện trước để dành đó. Xin cảm ơn anh hai lúa đã quan tâm
Nhấp chuột vào đây để mở rộng...

Trong bài #29 đã làm rồi mà bạn.
 
Upvote 0
khi chạy nó báo lỗi kết nối không được anh ơi
Picture1.jpg
cái này em chạy trên office 10. không biết là bộ office của em có vấn đề gì không, mà dạo này VBA trục trặc quá
 
Upvote 0
Upvote 0
chắc phải kiểm tra lại office rồi, mấy hôm này viết VBA trục trặc dữ quá mới test thử máy bàn thì ok, => nản, chắc phải làm lại máy móc rồi, cảm ơn anh hai lúa nhiều
 
Lần chỉnh sửa cuối:
Upvote 0
Huy Vũ đã viết:
Em có câu hỏi sau, mong các thành viên giúp đỡ.
Em có file Tổng hợp, và nhiều file Số tiền các tháng T1, T2, T3....
Cấu trúc các file:
- File Tong Hop gồm trường: Code, Sotien1, VAT1, Sotien2.....
- File số tiền gồm 3 trường: Code, SoTien, VAT
Bình thường e hay copy dữ liệu file T1, T2... vào các sheet của file Tổng hợp
Rồi từ đó dùng phương thức Find để tìm kiếm theo Code của sheet Tổng hợp từ các sheet T1, T2...
Bài toán đặt ra là dữ liệu T1, T2 càng ngày càng nhiều, copy vào file Tổng hợp làm nặng file.
Vậy có cách nào mà Tổng hợp dữ liệu không cần copy các file T1, T2 vào các sheet của file tổng hợp không?
Tổng hợp dữ liệu không cần mở file. Hình như ADO có thể làm được việc này, hoặc bằng phương thức khác, mong các thành viên giúp đỡ!!!
Em xin chân thành cảm ơn!
Nhấp chuột vào đây để mở rộng...
Với File đã up ở bài #1, giờ em muốn tổng hợp số liệu của Quý 1, là tổng của 3 tháng T1, T2, T3 thì làm thế nào, em có tìm hiều thì code Consolidate có thể làm được việc đó. Em đang phải dùng 3 công thức sumif với điều kiện Mã Code để làm việc này !$@!!
Mong các thành viên giúp đỡ.
 

File đính kèm

Upvote 0
Huy Vũ đã viết:
Với File đã up ở bài #1, giờ em muốn tổng hợp số liệu của Quý 1, là tổng của 3 tháng T1, T2, T3 thì làm thế nào, em có tìm hiều thì code Consolidate có thể làm được việc đó. Em đang phải dùng 3 công thức sumif với điều kiện Mã Code để làm việc này !$@!!
Mong các thành viên giúp đỡ.
Nhấp chuột vào đây để mở rộng...

Bạn nên vận dụng và suy luận từ bài tôi viết ở trên nhé:

Mã: 
Sub Main()
    Dim ObjConn As Object, RS As Object, Files, sht
    Dim StrRequest As String, Path As String, i As Integer ', iSht As Integer
    Path = ThisWorkbook.Path
    Files = Array("T1.xlsb", "T2.xlsb", "T3.xlsb")
    'sht = Array("T1", "T2", "T3")
    Set RS = CreateObject("ADODB.Recordset")
    Sheet1.[A2:E10000].ClearContents
    For i = 0 To UBound(Files)
        Set ObjConn = GetExcelConnection(Path & "\" & Files(i), 1)
        StrRequest = ""
        'For iSht = 0 To UBound(sht)
            StrRequest = "SELECT Code,SoTien,VAT FROM [" & Left(Files(i), 2) & "$A1:E10000]"
            RS.Open "SELECT Code, sum(SoTien),Sum(VAT) from (" & StrRequest & " ) group by Code", ObjConn, 3, 1
            Sheet1.[A65536].End(3).Offset(1).CopyFromRecordset RS
            RS.Close
        'Next
        ObjConn.Close
    Next
    Set RS = Nothing
End Sub
 
Upvote 0
Hai Lúa Miền Tây đã viết:
Bạn nên vận dụng và suy luận từ bài tôi viết ở trên nhé:

Mã: 
Sub Main()
    Dim ObjConn As Object, RS As Object, Files, sht
    Dim StrRequest As String, Path As String, i As Integer ', iSht As Integer
    Path = ThisWorkbook.Path
    Files = Array("T1.xlsb", "T2.xlsb", "T3.xlsb")
    'sht = Array("T1", "T2", "T3")
    Set RS = CreateObject("ADODB.Recordset")
    Sheet1.[A2:E10000].ClearContents
    For i = 0 To UBound(Files)
        Set ObjConn = GetExcelConnection(Path & "\" & Files(i), 1)
        StrRequest = ""
        'For iSht = 0 To UBound(sht)
            StrRequest = "SELECT Code,SoTien,VAT FROM [" & Left(Files(i), 2) & "$A1:E10000]"
            RS.Open "SELECT Code, sum(SoTien),Sum(VAT) from (" & StrRequest & " ) group by Code", ObjConn, 3, 1
            Sheet1.[A65536].End(3).Offset(1).CopyFromRecordset RS
            RS.Close
        'Next
        ObjConn.Close
    Next
    Set RS = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...
Em cảm ơn a, vì cơ bản còn yếu nên không biết sửa ở đâu ạ.
Em đang muốn tổng hợp vào sheet TongHop thì code thế nào ạ.
 
Upvote 0
Dữ liệu trong sheet DATA cần sửa lại kiểu number cho giống các file T1, T2, T3.
Thử với file T1 trước nhé, code để lấy tất cả các file tương tự. Mình sử dụng cách thủ công:
- Đặt Name Code =DATA!$D$3:$D$62
- Tạo thêm sheet5 lưu dữ liệu từ file T1 rồi join với bảng code (bỏ qua phần bẫy lỗi do lười gõ phím, các sư phụ thông cảm!)

Mã: 
Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.Path & "\T1.xlsb"
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT * FROM [T1$]"
    rs.Open str, cnn
    Sheet5.Range("A2:C1000").ClearContents
    Sheet5.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
    Set cnn = ConnXLS(ThisWorkbook.FullName)
    str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"
    rs.Open str, cnn
    Sheets("DATA").Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Ở đây mình giả thiết code trong T1 duy nhất, nếu code không duy nhất cần tính tổng tiền thì sửa lại lệnh SELECT thứ nhất thành "SELECT code, SUM(SoTien) AS SoTien, SUM(VAT) AS VAT FROM [T1$] GROUP BY code"
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Hau151978 đã viết:
Dữ liệu trong sheet DATA cần sửa lại kiểu number cho giống các file T1, T2, T3.
Thử với file T1 trước nhé, code để lấy tất cả các file tương tự. Mình sử dụng cách thủ công:
- Đặt Name Code =DATA!$D$3:$D$62
- Tạo thêm sheet5 lưu dữ liệu từ file T1 rồi join với bảng code (bỏ qua phần bẫy lỗi do lười gõ phím, các sư phụ thông cảm!)

Mã: 
Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.Path & "\T1.xlsb"
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT * FROM [T1$]"
    rs.Open str, cnn
    Sheet5.Range("A2:C1000").ClearContents
    Sheet5.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
    Set cnn = ConnXLS(ThisWorkbook.FullName)
    str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"
    rs.Open str, cnn
    Sheets("DATA").Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Ở đây mình giả thiết code trong T1 duy nhất, nếu code không duy nhất cần tính tổng tiền thì sửa lại lệnh SELECT thứ nhất thành "SELECT code, SUM(SoTien) AS SoTien, SUM(VAT) AS VAT FROM [T1$] GROUP BY code"
Nhấp chuột vào đây để mở rộng...

Câu sau so với dữ liệu thực tế thì hơi bị lạ
str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"
Nhấp chuột vào đây để mở rộng...

Hình như cũng chưa cần thiết phải thêm sheet phụ là sheet5 như thế
 
Upvote 0
Hai Lúa Miền Tây đã viết:
...
Hình như cũng chưa cần thiết phải thêm sheet phụ là sheet5 như thế
Nhấp chuột vào đây để mở rộng...

Nếu là tôi, thì tôi cũng không lập sheet phụ. Vì tôi hết sức tránh việc lập connection với file chính. Trong ứng dụng VBA, ADO có cái bug là lúc connection close rồi nó vẫn nó không nhả ra phần bộ nhớ đã sử dụng để kết nối với file đang mở. Vì thế nếu nối nhiều lần sẽ bị tràn bộ nhớ.

GROUP BY: nếu là tôi, tôi cũng không group trước khi join, luôn luôn group sau khi join, trừ phi không thể khác hơn.
 
Upvote 0
Bảng code ở sheet DATA cố định rồi, trong sheet5 có những dữ liệu thừa thì loại ra. Ví dụ: các file T1, T2, T3 tổng hợp nhiều cơ sở báo về nhưng tác giả chỉ phụ trách 1 số cơ sở chứ không phải tất cả nên cần LEFT JOIN.
Lý do lập sheet phụ vì tôi chưa biết cách JOIN 2 bảng ở 2 file excel khác nhau.
Còn lý do không GROUP BY trước khi JOIN thì mình không biết, mong các bác giải thích? Có phải để tránh những phép tính thừa đối với các code không được JOIN không?
 
Lần chỉnh sửa cuối:
Upvote 0
Hau151978 đã viết:
Bảng code ở sheet DATA cố định rồi, trong sheet5 có những dữ liệu thừa thì loại ra. Ví dụ: các file T1, T2, T3 tổng hợp nhiều cơ sở báo về nhưng tác giả chỉ phụ trách 1 số cơ sở chứ không phải tất cả nên cần LEFT JOIN.
Lý do lập sheet phụ vì tôi chưa biết cách JOIN 2 bảng ở 2 file excel khác nhau.
Còn lý do không GROUP BY trước khi JOIN thì mình không biết, mong các bác giải thích? Có phải để tránh những phép tính thừa đối với các code không được JOIN không?
Nhấp chuột vào đây để mở rộng...
Tôi nghĩ việc left join với file đóng thì cũng đâu phải là khó đúng không bạn. Bạn cần suy luận thêm 1 chút nữa là được thôi mà.
 
Upvote 0
Lê Phát Huy đã viết:
Tôi nghĩ việc left join với file đóng thì cũng đâu phải là khó đúng không bạn. Bạn cần suy luận thêm 1 chút nữa là được thôi mà.
Nhấp chuột vào đây để mở rộng...
Vì câu lệnh tạo recordset "rs.Open str, cnn" mà cnn chỉ kết nối đến 1 file excel nên mình chưa biết cách join 2 bảng ở 2 file khác nhau vì vậy mình copy 2 bảng vào 1 file rồi mới truy vấn.
 
Upvote 0
Hau151978 đã viết:
Vì câu lệnh tạo recordset "rs.Open str, cnn" mà cnn chỉ kết nối đến 1 file excel nên mình chưa biết cách join 2 bảng ở 2 file khác nhau vì vậy mình copy 2 bảng vào 1 file rồi mới truy vấn.
Nhấp chuột vào đây để mở rộng...
Chưa hẳn để join từ 1 bảng đến 1 bảng thì bạn phải mở đồng thời song song 2 CSDL nguồn để join. Bạn có thể mở 1 kết nối và sau đó join đến file đang đóng là được.
 
Upvote 0
Group sau khi join:
1. Câu lệnh SQL dễ debug và dễ khai triển hơn.
2. Nếu phải nối nhiều bảng thì các con toán tính tổng kết (aggregate functions) để sau cùng sẽ tránh bớt được tình trạng sai số do mất số lẻ.

Trong trường hợp bài này trước và sau không khác nhau gì cả. Nhưng tập thói quen thì vẫn hơn.
 
Upvote 0
VetMini đã viết:
Group sau khi join:
1. Câu lệnh SQL dễ debug và dễ khai triển hơn.
2. Nếu phải nối nhiều bảng thì các con toán tính tổng kết (aggregate functions) để sau cùng sẽ tránh bớt được tình trạng sai số do mất số lẻ.

Trong trường hợp bài này trước và sau không khác nhau gì cả. Nhưng tập thói quen thì vẫn hơn.
Nhấp chuột vào đây để mở rộng...
Xin nói thêm:
Đối với lập trình chuyên nghiệp thì ta không nên group trước vì điều đơn giản là sau đó ta cũng phải mất công group lại nữa. Với CSDL nhỏ thì ta không thấy sự khác biệt cho lắm, nhưng với CSDL lớn thì thời gian để làm điều đó sẽ có sự khác biệt rất rõ.
 
Upvote 0
Lê Phát Huy đã viết:
Chưa hẳn để join từ 1 bảng đến 1 bảng thì bạn phải mở đồng thời song song 2 CSDL nguồn để join. Bạn có thể mở 1 kết nối và sau đó join đến file đang đóng là được.
Nhấp chuột vào đây để mở rộng...
Vấn đề này mình chưa biết, bạn có thể cho 1 đoạn code được không? Nói chung là truy vấn trên 2 table ở 2 file excel khác nhau.
 
Upvote 0
Mong anh Vetmini và Lê Phát Huy có giải pháp tối ưu hơn, để em và nhiều người trên gpe học tập.
 
Upvote 0
Huy Vũ đã viết:
Mong anh Vetmini và Lê Phát Huy có giải pháp tối ưu hơn, để em và nhiều người trên gpe học tập.
Nhấp chuột vào đây để mở rộng...

Quan niệm về "tối ưu" của tôi khác với nhiều người trên diễn đàn này. Mỗi lần nhìn thấy từ này thì tôi dị ứng.

Những cái bạn vừa hỏi đều có thể tìm được bên hộp CSDL, thớt Đố Vui về ADO...
Bạn HLMT đã có dẫn qua, nhưng hìn như bạn không chịu đọc.
 
Upvote 0
Mình sửa lại bài 38 code để tính tổng và không dùng sheet tạm, vẫn phải SUM rồi mới JOIN vì nếu JOIN trước rồi group thì thứ tự code sẽ thay đổi.
Mã: 
Option Explicit


Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub Test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.FullName
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT T1.SoTien,T1.VAT FROM Code LEFT JOIN (SELECT Code,SUM(SoTien) AS SoTien,SUM(VAT) AS VAT FROM [Excel 12.0;HDR=Yes;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\T1.xlsb].[T1$] GROUP BY Code) T1 ON Code.Code=T1.Code"
    rs.Open str, cnn
    Sheet4.Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
 
Upvote 0
Như bạn VetMini đã đề cập, trường hợp này cũng không hiếm ở diễn đàn này. Bạn nên tìm giải pháp khác thay vì dùng sheet tạm.
 
Upvote 0
Nếu dữ liệu của em từ 2 file excel thuộc 2 thư mục khác nhau thì làm thế nào ạ.
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

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

D
Lọc các ô có dữ liệu trùng nhau trong một cột. Xin chỉ giúp (1 người xem)
Trả lời
5
Đọc
240
Gà Công Nghệ
Gà Công Nghệ
Gà Công Nghệ
Tự động điều chỉnh căn giãn dữ liệu theo độ rộng của ô trong excel
Trả lời
4
Đọc
88
Gà Công Nghệ
Gà Công Nghệ
L
Xin hàm trích xuất chuỗi trong ô
Trả lời
9
Đọc
90
thanthanhan
thanthanhan
Nguyenkhang2404
giúp đỡ lọc dữ liệu nâng cao (1 người xem)
Trả lời
17
Đọc
514
Phan Thế Hiệp
Phan Thế Hiệp
bitun1991
Xin nhờ hỗ trợ làm file excel giải đấu bóng đá
Trả lời
1
Đọc
54
Duy Tùng 9x
Duy Tùng 9x
tuanlemanh
Chữ trong ô bị nhảy dòng khi in
Trả lời
2
Đọc
44
tuanlemanh
tuanlemanh
tramcannt
các bác giúp em với ạ
Trả lời
0
Đọc
34
tramcannt
tramcannt
P
VẼ ĐƯỜNG TẦN SUẤT (DTS) TRÊN EXCEL
Trả lời
7
Đọc
117
ptm0412
ptm0412
S
Tạo sheet mới từ dữ liệu 2 sheet khác ĐVT
Trả lời
0
Đọc
40
skmart
S
X
Làm thế nào để ẩn "Blank" trong Pivot table
    • Thích
Trả lời
14
Đọc
36K
hoangpham1105
H
Kenvinclvcu
File excel bị lỗi, nhờ các bác giúp.
Trả lời
2
Đọc
49
Kenvinclvcu
Kenvinclvcu
ongke0711
  • Question Question
Trộn dữ liệu từ Excel sang Word mở rộng (phiên bản ongke0711) (3 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3
Trả lời
284
Đọc
36K
ongke0711
ongke0711
H
Addin FastlookuoIO - công cụ nhập mã sản phẩm lấy dữ liệu từ Named Range một cách tức thì, chỉ với vài phím tắt.
Trả lời
0
Đọc
71
hotafu
H
P
GOAL SEEK NÂNG CAO TRONG EXCEL
    • Thích
Trả lời
3
Đọc
126
Hoale85
H
vanthinh3101
Danh sách chi tiết 3.321 phường, xã, đặc khu của 34 tỉnh, thành phố sau sắp xếp
    • Thích
    • Yêu thích
Trả lời
2
Đọc
124
vanthinh3101
vanthinh3101
huuthang_bd
Add-in DeleteVirusMacro - Xóa và ngăn chặn virus macro (2 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
49
Đọc
20K
letuanh251095
L
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
3 4 5
Trả lời
409
Đọc
49K
tuanktt3
T
N
Cách để lọc dữ liệu gốc qua các sheet cần phân loại!
Trả lời
1
Đọc
114
thanthanhan
thanthanhan
GPE-Trợ Lý
TALKSHOW 15: EXCEL 365: HÀM GROUPBY VÀ PIVOTBY SỬ DỤNG NHƯ THẾ NÀO? CÓ THỂ THAY THẾ PIVOT TABLE KHÔNG?
    • Thích
Trả lời
0
Đọc
104
GPE-Trợ Lý
GPE-Trợ Lý
N
Thử lập trình XLL add-in bằng C/C++
    • Thích
Trả lời
4
Đọc
639
nguyendang95
N
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

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom