Lấy dữ liệu từ các file excel không mở?

vungoc · 15/4/15

Nhờ viết code lấy dữ liệu từ các file excel không mở?

Mình có một nhu cầu thế này, nhờ các bác cao thủ giúp đỡ, vấn đề như sau:

A/ THỰC TRẠNG:

Hiện tại mình có 3 file dữ liệu:

“1 FILE NGUỒN 1”
“2 FILE NGUỒN 2”
“3 FILE NGUỒN 3”

Hiện tại mình căn cứ cột mã số nhân viên (MSNV) và dùng hàm Vlookup để lấy dữ liệu của 3 file trên vào trong file sau:

“4 BAO CAO (FILE ĐÍCH)”

Dùng hàm vlookup lấy thì phải mở các file nguồn lên, và đôi khi có nhiều vấn đề phiền toái trong khi thao tác

B/ GIẢI PHÁP?

Bây giờ mình không muốn dùng hàm vlookup để lấy dữ liệu như cách cũ hiện tại, mà muốn nhờ các bác cao thủ am hiểu về code dúp đỡ, viết code để lấy dữ liệu từ các file nguồn vào file đích mà không cần mở file nguồn lên

Đây cũng là vấn đề hay gặp phải đối với người làm excel, xin các bác để tâm giúp đỡ

Vui lòng xem các file đính kèm, cảm ơn các bác nhiều nhé!

Trân trọng!

P/s: ở đây dùng từ k mở file, có nghĩa là mở ngầm hay mở xong đóng lại mà người ta k thấy như cách mở excel thông thường...

hungpecc1 · 15/4/15

vungoc đã viết:
Nhờ viết code lấy dữ liệu từ các file excel không mở?

Mình có một nhu cầu thế này, nhờ các bác cao thủ giúp đỡ, vấn đề như sau:

A/ THỰC TRẠNG:

Hiện tại mình có 3 file dữ liệu:

“1 FILE NGUỒN 1”
“2 FILE NGUỒN 2”
“3 FILE NGUỒN 3”

Hiện tại mình căn cứ cột mã số nhân viên (MSNV) và dùng hàm Vlookup để lấy dữ liệu của 3 file trên vào trong file sau:

“4 BAO CAO (FILE ĐÍCH)”

Dùng hàm vlookup lấy thì phải mở các file nguồn lên, và đôi khi có nhiều vấn đề phiền toái trong khi thao tác

B/ GIẢI PHÁP?

Bây giờ mình không muốn dùng hàm vlookup để lấy dữ liệu như cách cũ hiện tại, mà muốn nhờ các bác cao thủ am hiểu về code dúp đỡ, viết code để lấy dữ liệu từ các file nguồn vào file đích mà không cần mở file nguồn lên

Đây cũng là vấn đề hay gặp phải đối với người làm excel, xin các bác để tâm giúp đỡ

Vui lòng xem các file đính kèm, cảm ơn các bác nhiều nhé!

Trân trọng!

cho hỏi bạn định :
viết 1 hàm : để khi nhập MSNV thì các cột bên cạnh tự động lấy dữ liệu
Viết 1 thủ tục : sau khi nhập hết MSNV thì chỉ cần ấn 1 nút lấy dữ liệu

giola · 15/4/15

Trên đời này không có cái không mở mà vẫn lấy được cả (kể cả phá ra cũng là 1 cách mở)

Nhiều người cứ nói dùng ADO là không mở file (hay file không mở) vẫn lấy dữ liệu - đó là đánh tráo về khái niệm, ADO vẫn phải kết nối và mở nó để lấy dữ liệu. -- nên thực chất ở đây là không dùng excel để mở hay mở ra màn hình mà thôi (mà thế chưa chắc đã đủ vì ADO cũng là extent do vba excel cho phép chúng ta dùng..., nên phải nói là không dùng cách excel thường mở).

Nên so sánh với kiểu dùng code VBA mở ngầm (không hiện ra màn hình) lấy dữ liệu nhanh rồi lại đóng liền thì người sử dụng cũng không biết là đã mở đâu (vì không nhìn thấy), cũng là 1 cách vậy thôi.

Vì thế nói là lấy dữ liệu từ file không mở là nhầm, nói vậy để chúng ta tránh bị những xảo ngữ che lấp bản chất vấn đề và nhầm tưởng là hay.

vungoc · 15/4/15

hungpecc1 đã viết:
cho hỏi bạn định :
viết 1 hàm : để khi nhập MSNV thì các cột bên cạnh tự động lấy dữ liệu
Viết 1 thủ tục : sau khi nhập hết MSNV thì chỉ cần ấn 1 nút lấy dữ liệu

Cách nào cũng được hết trơn á bạn, tuy nhiên cách thứ 2 thì ngon hơn
Cảm ơn bạn nhiều

vungoc · 15/4/15

giola đã viết:
Trên đời này không có cái không mở mà vẫn lấy được cả (kể cả phá ra cũng là 1 cách mở)

Nhiều người cứ nói dùng ADO là không mở file (hay file không mở) vẫn lấy dữ liệu - đó là đánh tráo về khái niệm, ADO vẫn phải kết nối và mở nó để lấy dữ liệu. -- nên thực chất ở đây là không dùng excel để mở hay mở ra màn hình mà thôi (mà thế chưa chắc đã đủ vì ADO cũng là extent do vba excel cho phép chúng ta dùng..., nên phải nói là không dùng cách excel thường mở).

Nên so sánh với kiểu dùng code VBA mở ngầm (không hiện ra màn hình) lấy dữ liệu nhanh rồi lại đóng liền thì người sử dụng cũng không biết là đã mở đâu (vì không nhìn thấy), cũng là 1 cách vậy thôi.

Vì thế nói là lấy dữ liệu từ file không mở là nhầm, nói vậy để chúng ta tránh bị những xảo ngữ che lấp bản chất vấn đề và nhầm tưởng là hay.

Ý mình là không phải mở file một cách thông thường - còn mở cách nào đó mà đóng lại như thể không mở thì vẫn rất ok đó bạn
Xin cảm ơn đã góp ý!

ndu96081631 · 15/4/15

vungoc đã viết:
Dùng hàm vlookup lấy thì phải mở các file nguồn lên, và đôi khi có nhiều vấn đề phiền toái trong khi thao tác

Cái này hổng đúng à nha! Đâu cần mở file nguồn cũng dùng được VLOOKUP mà Ngọc

vungoc · 15/4/15

ndu96081631 đã viết:
Cái này hổng đúng à nha! Đâu cần mở file nguồn cũng dùng được VLOOKUP mà Ngọc

Nếu dùng vlookup bác mở lên lấy dữ liệu xong đóng lại, các lần sau mở ra nó tự thêm đường dẫn vào hàm, và em thấy có đôi khi nó lấy k đúng bác ạ...

ndu96081631 · 15/4/15

vungoc đã viết:
Nếu dùng vlookup bác mở lên lấy dữ liệu xong đóng lại, các lần sau mở ra nó tự thêm đường dẫn vào hàm

Điều này là đương nhiên thôi

vungoc đã viết:
và em thấy có đôi khi nó lấy k đúng bác ạ...

Chỉ cần mỗi lần mở file, Ngọc bấm Update Link là dữ liệu sẽ được cập nhật (nó lấy không đúng là khi file chưa được cập nhật)

hungpecc1 · 15/4/15

vungoc đã viết:
Cách nào cũng được hết trơn á bạn, tuy nhiên cách thứ 2 thì ngon hơn
Cảm ơn bạn nhiều

Thử dùng ADO xem :
Bước 1 : Đổi tên các file nguồn của bạn lần lượt là : DATA1.xlsx; DATA2.xlsx; DATA3.xlsx; File đích đổi tên là Report.xlsm
Bước 2 : Copy đoạn code sau vào File đích rồi chạy :

Mã:

Sub GPE()
Dim Cnn As Object
Dim rst As As Object
Dim SQL$
    Set Cnn = CreateObject("ADODB.connection")
    Set rst = CreateObject("ADODB.recordset")
    With Cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
        .Open
    End With
    SQL = "SELECT a.f1,b.f2,b.f4,b.f5,b.f7,b.f8,b.f9,b.f10,VAL(b.f8)+VAL(b.f9)+VAL(b.f10)," _
                & "c.f2,c.f3,c.f5,c.f7,VAL(c.f3)+VAL(c.f7)," _
                & "d.f2,d.f5,d.f7,d.f9,d.f10 " _
                & "FROM (([Sheet1$A6:A65536] a LEFT JOIN " _
                  & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA1.xlsx[/COLOR]].[Sheet1$A3:J65536] b " _
                  & "ON a.f1=b.f1) LEFT JOIN " _
                                & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA2.xlsx[/COLOR]].[Sheet1$A4:G65563] c " _
                                & "ON a.f1=c.f1) LEFT JOIN " _
                                & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA3.xlsx[/COLOR]].[Sheet1$A5:K65536] d " _
                                & "ON a.f1=d.f1"
    rst.Open SQL, Cnn, adOpenDynamic, adLockOptimistic
    Range("A6").CopyFromRecordset rst
    Set rst = Nothing
    Cnn.Close:     Set Cnn = Nothing
End Sub

vungoc · 16/4/15

Trước tiên xin cảm ơn sự giúp đỡ nhiệt tình, nhanh chóng của bạn hungpecc1!

Tuy nhiên mình đã làm theo hướng dẫn các bước nêu trên nhưng khi chạy thì code báo lỗi (như hình)
(hay có phải setup thêm gì trong tools hoặc addinn nữa không?)

Và cho mình hỏi thêm, nếu buộc phải đổi tên file như trên, thì code này có chạy được ở office/ excel 2003 hay không?

(Bạn đã test nếu chạy đc thì gửi file lên lại dùm mình)

Cảm ơn bạn nhiều!

hungpecc1 · 16/4/15

vungoc đã viết:
Trước tiên xin cảm ơn sự giúp đỡ nhiệt tình, nhanh chóng của bạn hungpecc1!

Tuy nhiên mình đã làm theo hướng dẫn các bước nêu trên nhưng khi chạy thì code báo lỗi (như hình)
(hay có phải setup thêm gì trong tools hoặc addinn nữa không?)

Và cho mình hỏi thêm, nếu buộc phải đổi tên file như trên, thì code này có chạy được ở office/ excel 2003 hay không?

(Bạn đã test nếu chạy đc thì gửi file lên lại dùm mình)

Cảm ơn bạn nhiều!

xoá 1 chữ " as " tại dòng bôi vàng
chỉ cần đặt tên file ko chứa ký tự unicode + dễ nhớ

vungoc · 16/4/15

hungpecc1 đã viết:
xoá 1 chữ " as " tại dòng bôi vàng
chỉ cần đặt tên file ko chứa ký tự unicode + dễ nhớ

bạn xem dùm
vẫn báo lỗi

tks!

hungpecc1 · 16/4/15

vungoc đã viết:
bạn xem dùm
vẫn báo lỗi

tks!

Có thể do tôi soạn thảo code hơi ẩu ,chưa test trên file của bạn
khoảng cuối h chiều tôi gửi thêm file đính kèm

Hiện h mình không có máy vi tính để test

hungpecc1 · 16/4/15

vungoc đã viết:
bạn xem dùm
vẫn báo lỗi

tks!

Đính kèm file !

vungoc · 16/4/15

Đã chạy tốt, cảm ơn bạn

vungoc · 17/4/15

Chào bạn hungpecc1

Phải nói là cái code lấy dữ liệu từ 3 file không mở của bạn hungpecc1 quá ngắn gọn, hiệu quả và quá tuyệt vời;
Mình có một mong muốn là được bạn giải thích (bằng cách chú thich luôn trong code) để mình hiểu và áp dụng cho các trường hợp khác;

Sẽ rất hữu ích cho dân văn phòng, nếu hiểu và áp dụng được lệnh trên vào công việc, giúp cho việc lấy dữ liệu từ file khác một cách nhẹ nhàng, hiệu quả và rất chi là pro...

Mong được bạn chỉ giáo!

babyheomoi · 17/4/15

vungoc đã viết:
Chào bạn hungpecc1

Phải nói là cái code lấy dữ liệu từ 3 file không mở của bạn hungpecc1 quá ngắn gọn, hiệu quả và quá tuyệt vời;
Mình có một mong muốn là được bạn giải thích (bằng cách chú thich luôn trong code) để mình hiểu và áp dụng cho các trường hợp khác;

Sẽ rất hữu ích cho dân văn phòng, nếu hiểu và áp dụng được lệnh trên vào công việc, giúp cho việc lấy dữ liệu từ file khác một cách nhẹ nhàng, hiệu quả và rất chi là pro...

Mong được bạn chỉ giáo!

Mình cũng mong dc giải thích code để áp dụng!

Lê Phát Huy · 25/4/15

hungpecc1 đã viết:

Thử dùng ADO xem :
Bước 1 : Đổi tên các file nguồn của bạn lần lượt là : DATA1.xlsx; DATA2.xlsx; DATA3.xlsx; File đích đổi tên là Report.xlsm
Bước 2 : Copy đoạn code sau vào File đích rồi chạy :

Mã:

Sub GPE()
Dim Cnn As Object
Dim rst As As Object
Dim SQL$
    Set Cnn = CreateObject("ADODB.connection")
    Set rst = CreateObject("ADODB.recordset")
    With Cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
        .Open
    End With
    SQL = "SELECT a.f1,b.f2,b.f4,b.f5,b.f7,b.f8,b.f9,b.f10,VAL(b.f8)+VAL(b.f9)+VAL(b.f10)," _
                & "c.f2,c.f3,c.f5,c.f7,VAL(c.f3)+VAL(c.f7)," _
                & "d.f2,d.f5,d.f7,d.f9,d.f10 " _
                & "FROM (([Sheet1$A6:A65536] a LEFT JOIN " _
                  & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA1.xlsx[/COLOR]].[Sheet1$A3:J65536] b " _
                  & "ON a.f1=b.f1) LEFT JOIN " _
                                & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA2.xlsx[/COLOR]].[Sheet1$A4:G65563] c " _
                                & "ON a.f1=c.f1) LEFT JOIN " _
                                & "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\[COLOR=#ff0000]DATA3.xlsx[/COLOR]].[Sheet1$A5:K65536] d " _
                                & "ON a.f1=d.f1"
    rst.Open SQL, Cnn, adOpenDynamic, adLockOptimistic
    Range("A6").CopyFromRecordset rst
    Set rst = Nothing
    Cnn.Close:     Set Cnn = Nothing
End Sub

Rút gọn lại chút, lấy đúng kiểu dữ liệu theo chuẩn.

PHP:

Sub GPE()
    Dim cnn As Object, rst As Object
    Set cnn = CreateObject("ADODB.connection")
    cnn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & _
              ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=2"";")
    Set rst = cnn.Execute("SELECT a.f1,b.f2,b.f4,b.f5,b.f7,b.f8,b.f9,b.f10,b.f8+b.f9+b.f1," _
          & "c.f2,c.f3,c.f5,c.f7,c.f3+c.f7," _
          & "d.f2,d.f5,d.f7,d.f9,d.f10 " _
          & "FROM (([Sheet1$A6:A65536] a LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA1.xlsx].[Sheet1$A3:J65536] b " _
          & "ON a.f1=b.f1) LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA2.xlsx].[Sheet1$A4:G65563] c " _
          & "ON a.f1=c.f1) LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA3.xlsx].[Sheet1$A5:K65536] d " _
          & "ON a.f1=d.f1")
    Range("A6").CopyFromRecordset rst
    Set rst = Nothing
    cnn.Close:     Set cnn = Nothing
End Sub

vungoc · 8/10/15

Lê Phát Huy đã viết:

Rút gọn lại chút, lấy đúng kiểu dữ liệu theo chuẩn.

PHP:

Sub GPE()
    Dim cnn As Object, rst As Object
    Set cnn = CreateObject("ADODB.connection")
    cnn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & _
              ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=2"";")
    Set rst = cnn.Execute("SELECT a.f1,b.f2,b.f4,b.f5,b.f7,b.f8,b.f9,b.f10,b.f8+b.f9+b.f1," _
          & "c.f2,c.f3,c.f5,c.f7,c.f3+c.f7," _
          & "d.f2,d.f5,d.f7,d.f9,d.f10 " _
          & "FROM (([Sheet1$A6:A65536] a LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA1.xlsx].[Sheet1$A3:J65536] b " _
          & "ON a.f1=b.f1) LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA2.xlsx].[Sheet1$A4:G65563] c " _
          & "ON a.f1=c.f1) LEFT JOIN " _
          & "[Excel 12.0;HDR=NO;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\DATA3.xlsx].[Sheet1$A5:K65536] d " _
          & "ON a.f1=d.f1")
    Range("A6").CopyFromRecordset rst
    Set rst = Nothing
    cnn.Close:     Set cnn = Nothing
End Sub

Code gốc chạy nhanh, code này chậm lắm bạn ạ!
Với lại bạn nói rút gọn, nhưng thực ra tui thấy nó vẫn vậy, đâu có gọn gì đâu?

dephetnoi · 28/6/17

hungpecc1 đã viết:
Đính kèm file !

Em thử sửa chi dùng 1 file thôi mà sao không được ạ bác.
Nó cứ bão lỗi ở dòng "rst.Open SQL, cnn, 3, 3, 1"
Sub GPE()
Dim cnn As Object
Dim rst As Object
Dim SQL$
Set cnn = CreateObject("ADODB.connection")
Set rst = CreateObject("ADODB.recordset")
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = ThisWorkbook.FullName
.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
.Open
End With
SQL = "SELECT a.f1,b.f2,b.f4,b.f5,b.f7,b.f8,b.f9,b.f10,VAL(b.f8)+VAL(b.f9)+VAL(b.f10)," _
& "FROM (([Sheet1$A6:A65536] a LEFT JOIN " _
& "[Excel 12.0;HDR=NO;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\DATA1.xlsx].[Sheet1$A3:J65536] b " _
& "ON a.f1=b.f1"
rst.Open SQL, cnn, 3, 3, 1
Range("A6").CopyFromRecordset rst
Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Lấy dữ liệu từ các file excel không mở?

Find Sexy Womans from your town for night

File đính kèm

Thành viên gắn bó

theo chiều Gió

Find Sexy Womans from your town for night

Find Sexy Womans from your town for night

Huyền thoại GPE

Find Sexy Womans from your town for night

Huyền thoại GPE

Thành viên gắn bó

Find Sexy Womans from your town for night

File đính kèm

Thành viên gắn bó

Find Sexy Womans from your town for night

File đính kèm

Thành viên gắn bó

Thành viên gắn bó

File đính kèm

Find Sexy Womans from your town for night

Find Sexy Womans from your town for night

Thành viên thường trực

Thành viên hoạt động

Find Sexy Womans from your town for night

Thành viên chính thức

Đếm ngược thời gian