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

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Chào các bạn!

Tôi viết code sau để lấy dữ liệu từ một file không mở (File đang đóng thực sự):
Mã:
Sub ImPortDaTa_FileClose()
    Application.ScreenUpdating = 0
    On Error Resume Next
     With ActiveSheet
        Cells.Clear
         With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[COLOR="red"]D:\DaTa.xls[/COLOR];Jet OLEDB:Engine Type=35"), Destination:=Selection)
            .CommandType = xlCmdTable
            .CommandText = Array("TongHop$")
            .Refresh BackgroundQuery:=False
        End With
        Selection.EntireRow.Delete
        .UsedRange.Columns.AutoFit
    End With
End Sub
Tôi muốn Data Source D:\DaTa.xls được tùy biến khi người dùng chọn file từ Application.FileDialog(1).Show hoặc Application.FileDialog(3).Show).

Cảm ơn !
 

nghiaphuc

Thành viên gạo cội
Thành viên danh dự
Tham gia ngày
25 Tháng chín 2009
Bài viết
5,722
Được thích
9,528
Điểm
910
Nơi ở
Ayun Pa - Gia Lai
Chào các bạn!

Tôi viết code sau để lấy dữ liệu từ một file không mở (File đang đóng thực sự):
Mã:
Sub ImPortDaTa_FileClose()
Application.ScreenUpdating = 0
On Error Resume Next
With ActiveSheet
Cells.Clear
With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[COLOR=red]D:\DaTa.xls[/COLOR];Jet OLEDB:Engine Type=35"), Destination:=Selection)
.CommandType = xlCmdTable
.CommandText = Array("TongHop$")
.Refresh BackgroundQuery:=False
End With
Selection.EntireRow.Delete
.UsedRange.Columns.AutoFit
End With
End Sub
Tôi muốn Data Source D:\DaTa.xls được tùy biến khi người dùng chọn file từ Application.FileDialog(1).Show hoặc Application.FileDialog(3).Show).

Cảm ơn !
Bác sửa lại code như vầy:
Mã:
Sub ImPortDaTa_FileClose()
    [COLOR=red]Dim FD As FileDialog, fName As String[/COLOR]
    Application.ScreenUpdating = 0
    On Error Resume Next
    [COLOR=red]Set FD = Application.FileDialog(1)
    If FD.Show = -1 Then fName = FD.SelectedItems(1)[/COLOR]
     With ActiveSheet
        Cells.Clear
         With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data [COLOR=red]Source=fName[/COLOR];Jet OLEDB:Engine Type=35"), Destination:=Selection)
            .CommandType = xlCmdTable
            .CommandText = Array("TongHop$")
            .Refresh BackgroundQuery:=False
        End With
        Selection.EntireRow.Delete
        .UsedRange.Columns.AutoFit
    End With
End Sub
Theo như giới thiệu của bác thì code này hay quá nhưng em không hiểu về nó, nếu bỏ câu lệnh On Error Resume Next thì khi chạy, excel báo lỗi không tìm thấy đối tượng 'TongHop$'. Nhờ bác giải thích code trên giúp em. Cảm ơn bác đã chia sẻ 1 code hay.
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Bác sửa lại code như vầy:
Mã:
With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;[COLOR=red][B]Data Source=fName[/B][/COLOR];Jet OLEDB:Engine Type=35"), , Destination:=Selection)
Vầy mới đúng chứ
Mã:
With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0[COLOR=red][B];Data Source=" & fName & "[/B][/COLOR];Jet OLEDB:Engine Type=35"), Destination:=Selection)
Theo như giới thiệu của bác thì code này hay quá nhưng em không hiểu về nó, nếu bỏ câu lệnh On Error Resume Next thì khi chạy, excel báo lỗi không tìm thấy đối tượng 'TongHop$'. Nhờ bác giải thích code trên giúp em. Cảm ơn bác đã chia sẻ 1 code hay.
Record macro quá trình Import External Data sẽ có code này (menu Data\Import External Data)
 

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Cảm ơn 2 bạn Nghiaphuc và Ndu ! sau khi sửa theo ndu code chạy đúng yêu cầu rồi.


...nếu bỏ câu lệnh On Error Resume Next thì khi chạy, excel báo lỗi không tìm thấy đối tượng 'TongHop$'. Nhờ bác giải thích code trên giúp em. Cảm ơn bác đã chia sẻ 1 code hay.
1. Trong file đang đóng (File nguồn) Bạn phải có Sheets("TongHop") - lưu ý tên Sheet không có dấu $ như trong code.
2. Đúng như Ndu hướng dẫn code này là Recode Macro Import data và chỉnh sửa, bổ sung chút ít theo yêu cầu của người dùng. Bạn Recode Macro và so sánh với code sẽ thấy.

Tôi xin hỏi tiếp
Trường hợp trong File nguồn có nhiều Sheet thì... Thì code viết thế nào để chọn được đúng sheet cần lấy dữ liệu ?.

Cảm ơn !
 
Lần chỉnh sửa cuối:

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Tôi xin hỏi tiếp
Trường hợp trong File nguồn có nhiều Sheet thì... Thì code viết thế nào để chọn được đúng sheet cần lấy dữ liệu ?.

Cảm ơn !
Thì anh cứ sửa chổ này
.CommandText = Array("TongHop$")
Thay TongHop thành tên sheet anh muốn thôi
 

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Thì anh cứ sửa chổ này
.CommandText = Array("TongHop$")
Thay TongHop thành tên sheet anh muốn thôi
Ý của tôi là sau khi chọn file thì Sẽ có một FileDialog hoặc From có danh sách các Sheet trong file cho người dùng chọn để không phải sửa code (không mở file nguồn thì làm sao biết trong đó có Sheet nào mà sửa code).
 
Lần chỉnh sửa cuối:

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Ý của tôi là sau khi chọn file thì Sẽ có một FileDialog hoặc From có danh sách các Sheet trong file cho người dùng chọn để không phải sửa code (không mở file nguồn thì làm sao biết trong đó có Sheet nào mà sửa code).
Lại phải tốn thêm 1 code lấy tên sheet từ file đang đóng! Anh xem ở đây:
http://www.giaiphapexcel.com/forum/showthread.php?25407-Lấy-danh-sách-tên-các-Sheet-từ-file-excel
 

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Cảm ơn Ndu ! Mình đã xem lại các bài theo đường Links của bạn nhưng không làm được (thấy tên Sheet trong file chọn rồi nhưng không biết gán tên Sheet vào code này như thế nào).

Bạn viết giúp mình với. Cảm ơn !
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Cảm ơn Ndu ! Mình đã xem lại các bài theo đường Links của bạn nhưng không làm được (thấy tên Sheet trong file chọn rồi nhưng không biết gán tên Sheet vào code này như thế nào).

Bạn viết giúp mình với. Cảm ơn !
Với Form và code có sẳn, giờ em thêm code anh vào (trong Form luôn), sửa lại tí:
PHP:
Private Sub ImPortDaTa_FileClose(FileName As String, ShName As String)
  On Error Resume Next
  With ActiveSheet
    .Cells.Clear
    With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Jet OLEDB:Engine Type=35"), Destination:=Selection)
      .CommandType = xlCmdTable
      .CommandText = Array(ShName & "$")
      .Refresh BackgroundQuery:=False
    End With
    Selection.EntireRow.Delete
    .UsedRange.Columns.AutoFit
  End With
End Sub
Trong form ấy, em thêm 1 nút Lấy dữ liệu với code như sau:
PHP:
Private Sub txtGetData_Click()
  On Error GoTo ExitSub
  ImPortDaTa_FileClose lstWB.List(lstWB.ListIndex), lstWS.List(lstWS.ListIndex)
ExitSub:
End Sub
Chỉ vậy thôi ---> Đương nhiên code này viết ở mức đơn giản nhất, anh phải bẫy thêm các lỗi khác (chẳng hạn chưa chọn filename, chưa chọn tên sheet...)
--------------------
Hình như đoạn này:
Selection.EntireRow.Delete
Hơi thừa thì phải
 

File đính kèm

Lần chỉnh sửa cuối:

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Cảm ơn Ndu ! Mình đã test code OK rồi.



Lý do mình viết thêm dòng này vì Khi Import data dòng trên cùng luôn có tiêu đề cột là F1 F2 F... nên phải xóa nó đi.
Hình như trong 1 vùng dữ liệu thì nó xem dòng đầu là tiêu đề cột ---> Trong dòng tiêu đề này, nếu cell nào rổng thì khi xuất kết quả nó sẽ tự điền mấy cái F1, F2... vào
Em cũng chẳng biết nữa, nhưng với dữ liệu đầy đủ tiêu đề thì cho dù em bỏ dòng code Selection.EntireRow.Delete thì nó vẫn cho kết quả bình thường (không có mấy cái F1, F2 đâu)
Còn nếu anh thêm dòng code Selection.EntireRow.Delete vào thì khi xuất dữ liệu sẽ mất tiêu đề
Ngoài ra anh cũng nên cẩn thận, lở selection ban đầu không phải là 1 cell thì... chết ---> Nói không chừng vừa có dữ liệu là nó xóa sạch luôn
Em nghĩ nên là Selection.Resize(1).EntireRow.Delete (Đó là chưa nói đến việc ta vô tình Select nhiều cell rời rạc rồi chạy code ---> Rối)
 
Lần chỉnh sửa cuối:

cuongdv

Thành viên mới
Tham gia ngày
26 Tháng mười một 2008
Bài viết
38
Được thích
6
Điểm
0
Em chạy thử code của anh Ndu thì thấy nếu mình lấy dữ liệu xong rồi lại quay trở lại folder đó lấy dữ liệu file vừa lấy thì không lấy đc các sheet của file nữa. Em dùng off 2007. Anh giải thích giùm với. Cảm ơn!
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Em chạy thử code của anh Ndu thì thấy nếu mình lấy dữ liệu xong rồi lại quay trở lại folder đó lấy dữ liệu file vừa lấy thì không lấy đc các sheet của file nữa. Em dùng off 2007. Anh giải thích giùm với. Cảm ơn!
Bạn để ý đoạn code này:
Mã:
With .QueryTables.Add(Connection:=Array("OLEDB;[COLOR=red][B]Provider=Microsoft.Jet.OLEDB.4.0[/B][/COLOR];Data Source=" & FileName & ";Jet OLEDB:Engine Type=35"), Destination:=Selection)
Bạn sửa đoạn này thành vầy xem
Mã:
With .QueryTables.Add(Connection:=Array("OLEDB;[COLOR=red][B]Provider=Microsoft.ACE.OLEDB.12.0[/B][/COLOR];Data Source=" & FileName & ";Jet OLEDB:Engine Type=35"), Destination:=Selection)
Vậy để chạy được trên cả Excel 2003 và Excel 2007 ta có thể sửa Sub ImPortDaTa_FileClose thành vầy:
PHP:
Private Sub ImPortDaTa_FileClose(FileName As String, ShName As String)
  Dim Provider As String
  On Error Resume Next
  With Application
    Provider = IIf(Val(.Version) > 11, "Microsoft.ACE.OLEDB.12.0", "Microsoft.Jet.OLEDB.4.0")
  End With
  With ActiveSheet
    .Cells.Clear
    With .QueryTables.Add(Connection:=Array("OLEDB;Provider=" & Provider & ";Data Source=" & FileName & ";Jet OLEDB:Engine Type=35"), Destination:=Selection)
      .CommandType = xlCmdTable
      .CommandText = Array(ShName & "$")
      .Refresh BackgroundQuery:=False
      .SourceDataFile = FileName
    End With
    Selection.EntireRow.Delete
    .UsedRange.Columns.AutoFit
  End With
End Sub
Bạn thử xem thế nào nhé
 
Lần chỉnh sửa cuối:

Diepanh_pt

Thành viên mới
Tham gia ngày
1 Tháng một 2011
Bài viết
6
Được thích
0
Điểm
0
Tuổi
37
Chào 2 anh!..Em là thành viên mới của GPE, em có đọc bài viết của 2 anh và down về dùng thử đến phần chọn "lấy dữ liệu" thì hiện ra một bảng chọn có "pass" và một vài thông tin khác, em chọn pass hay không thì chương trình cũng không báo lỗi gì và cũng không thấy dữ liệu gì cả, vậy nhờ 2 anh hướng dẫn giúp. cảm ơn 2 anh.
 

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Chào 2 anh!..Em là thành viên mới của GPE, em có đọc bài viết của 2 anh và down về dùng thử đến phần chọn "lấy dữ liệu" thì hiện ra một bảng chọn có "pass" và một vài thông tin khác, em chọn pass hay không thì chương trình cũng không báo lỗi gì và cũng không thấy dữ liệu gì cả, vậy nhờ 2 anh hướng dẫn giúp. cảm ơn 2 anh.
Hình như Code trên tôi đã sửa để lấy dữ liệu từ file không có Pass vì vậy bạn thử lại với file không có Pass xem sao.
 

Diepanh_pt

Thành viên mới
Tham gia ngày
1 Tháng một 2011
Bài viết
6
Được thích
0
Điểm
0
Tuổi
37
Các file của em đều không dùng Pass, qua đây anh cho em hỏi thêm về chương trình ghép dữ liệu từ nhiều sheet của anh (em có đọc thấy anh dự định giới thiệu vào dịp sinh nhật lần thứ 4 của GPE), anh gửi cho em đường dẫn để lấy nhé:
 

TrungChinhs

Thành viên tích cực
Tham gia ngày
18 Tháng hai 2008
Bài viết
1,478
Được thích
2,453
Điểm
860
Nơi ở
Mường La, Sơn La
Mã:
Sub ImPortDaTa_FileClose()
    Application.ScreenUpdating = 0
    On Error Resume Next
    FileNguon = D:\New  Folder\Nguon.xls
    With ActiveSheet
        Cells.Clear
        .[b1].Select
        With .QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileNguon & ";Jet OLEDB:Engine Type=35"), Destination:=Selection)
            .CommandType = 3
            .CommandText = Array("[COLOR=#ff0000][B]ShNguon[/B][/COLOR]$")
            .Refresh BackgroundQuery:=False
        End With
        Selection.EntireRow.Clear
        .UsedRange.Columns.AutoFit
    End With
End Sub
Trong file nguồn (file đang đóng) Sheet tôi muốn lấy dữ liệu là Sheet1(ShNguon) vậy tôi muốn tên Sheet trong Array là tên gốc Sheet1 thay cho tên do người dùng đặt (ShNguon) thì câu lệnh .CommandText = Array("ShNguon$") sửa như thế nào ? tôi đã thử viết .CommandText = Array(Sheet1) hoặc .CommandText = Array(Sheet1 &"$") đều không được.

Nhờ các bạn xem giúp. Thanhks !

Để thử code này bạn tạo một File có tên là Nguon -> đặt tên Sheet muốn lấy dữ liệu là ShNguon -> lưu vào thư mục New Folder trong ổ D:\ và đóng file này lại.
Tạo một file mới rồi đặt code này vào Module và chạy code.
 
Lần chỉnh sửa cuối:

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,277
Được thích
52,671
Điểm
11,910
Trong file nguồn (file đang đóng) Sheet tôi muốn lấy dữ liệu là Sheet1(ShNguon) vậy tôi muốn tên Sheet trong Array là tên gốc Sheet1 thay cho tên do người dùng đặt (ShNguon) thì câu lệnh .CommandText = Array("ShNguon$") sửa như thế nào ? tôi đã thử viết .CommandText = Array(Sheet1) hoặc .CommandText = Array(Sheet1 &"$") đều không được.
Vụ này hơi căng nha!
Vì theo như anh mô tả thì Sheet1 là SheetCodeName ---> Làm sao mà biết được ShNguon có SheetCodeName là Sheet1 đây?
 
Top Bottom