Last active
November 18, 2017 05:16
-
-
Save ndthanh/c1d9a974228dd4c82f6deaa10f96724d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'https://www.hocexcel.online/tong-hop-du-lieu-tu-nhieu-file-excel-vao-1-file-khong-can-mo-file.html | |
Sub merge_all() | |
Dim cnn As ADODB.Connection | |
Dim rst As ADODB.Recordset | |
Dim sh As Worksheet | |
Dim I As Long, k As Long, CountFiles As Long, J As Long | |
'# Sửa Sheet1 và A1:U1000 cho phù hợp với dữ liệu của bạn | |
SheetName = "Sheet1" & "$" | |
RangeAddress = "A1:U1000" | |
Dim files As Variant | |
files = Application.GetOpenFilename(, , , , True) | |
If VarType(files) = vbBoolean Then Exit Sub | |
'# Sửa "Master" theo tên sheet tổng hợp của bạn. | |
Set sh = Sheets("Master") | |
For k = LBound(files) To UBound(files) | |
Set cnn = GetConnXLS(files(k)) | |
If cnn Is Nothing Then | |
MsgBox "Check lai co so du lieu file: " & files(k) | |
Exit Sub | |
End If | |
Set rst = cnn.Execute("SELECT *,""" & files(k) & """ as [From File] FROM [" & SheetName & RangeAddress & "]") | |
CountFiles = CountFiles + 1 | |
If CountFiles = 1 Then | |
For J = 0 To rst.Fields.Count - 1 | |
sh.Cells(3, J + 1).Value = rst.Fields(J).Name | |
Next J | |
End If | |
'# sửa lại ô đầu tiên sẽ dán dữ liệu vào, ô đầu tiên hiện tại là ô A4 | |
I = I + sh.Range("A" & 4 + I).CopyFromRecordset(rst) | |
rst.Close | |
Set rst = Nothing | |
cnn.Close | |
Set cnn = Nothing | |
Next k | |
MsgBox "Done" | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment