• Tùy chọn
• Đăng nhập
• Đăng ký
• Xem như trên Máy tính
• Accelerated Mobile Pages Theme

Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

• Trang chủ

  • Thủ Thuật Access
  • Access Nâng Cao
  • Thủ thuật VBA
  • Hướng Dẫn Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access
• Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

• Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  subasatran > 04-11-18, 12:47 PM

  Mình có một file nguồn excel được xuất ra từ hệ thống hàng ngày(không thay đổi format được). Bây giờ mình muốn import vào access để xử lý dữ liệu.
  Vấn đề gặp phải là dữ liệu file excel có một cột CODE(cột E file ví dụ) format từ hệ thống đang là number nhưng dữ liệu lại có một số dòng là text. Nên khi import các dòng này không import được.
  Nhờ mọi người hướng dẫn tạo một hàm bằng VBA để kiểm tra tự động import theo dữ liệu mà không phải chỉnh lại format của file excel.
  Mình có làm một hàm nhỏ để kiểm tra dữ liệu nhưng vẫn không import được. Rất mong mọi người giúp đỡ.
  

  Mã:

  Public Function ConvertNumberToText(ByVal txtnumber As String) As String
[/color]
If IsNumeric(Val(txtnumber)) = True Then
   ConvertNumberToText = Format(CStr(txtnumber), "00000000")
Else
   ConvertNumberToText = CStr(txtnumber)
End If
[color=#141414]End Function

  
  File ví dụ https://www.mediafire.com/file/2e8dje1g3...9.zip/file

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  mrsiro > 04-11-18, 06:50 PM

  để đơn giản hơn thì bạn tạo bảng tạm với kiểu text hết, cho import vào bảng này rồi check trên bảng tạm này nếu dữ liệu ok thì move sang bảng chính.
  Dữ liệu vào bảng tạm trên access thì check dễ hơn so với check từ file excel.

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  maidinhdan > 05-11-18, 09:14 PM

  (04-11-18, 12:47 PM)subasatran Đã viết: Mình có một file nguồn excel được xuất ra từ hệ thống hàng ngày(không thay đổi format được). Bây giờ mình muốn import vào access để xử lý dữ liệu.
  Vấn đề gặp phải là dữ liệu file excel có một cột CODE(cột E file ví dụ) format từ hệ thống đang là number nhưng dữ liệu lại có một số dòng là text. Nên khi import các dòng này không import được.
  Nhờ mọi người hướng dẫn tạo một hàm bằng VBA để kiểm tra tự động import theo dữ liệu mà không phải chỉnh lại format của file excel.
  Mình có làm một hàm nhỏ để kiểm tra dữ liệu nhưng vẫn không import được. Rất mong mọi người giúp đỡ.
  

  Mã:

  Public Function ConvertNumberToText(ByVal txtnumber As String) As String
[/color]
If IsNumeric(Val(txtnumber)) = True Then
   ConvertNumberToText = Format(CStr(txtnumber), "00000000")
Else
   ConvertNumberToText = CStr(txtnumber)
End If
[color=#141414]End Function

  
  File ví dụ https://www.mediafire.com/file/2e8dje1g3...9.zip/file

  
  Sử dung hàm ImportExcel này:
  

  Mã PHP:

  Function ImportExcel(TenTable As String, DuongdanFile As String, TenSheet As String, TongSoCot As Byte)
    Dim oEx As New Excel.Application     ' oEx la bien oExcel
    Dim oBook As Workbook              ' set oBook la tap hop oExcel
    ' Mo file can chen
    Set oBook = oEx.Workbooks.Open(DuongdanFile)
    ' Mo Sheet can chen
    Dim oSheet As Worksheet                 ' set oSheet la tap hop Sheet
    Set oSheet = oBook.Worksheets(TenSheet)

    ' Dem so dong, so cot
        Dim DongThunhat, CotThunhat, DongCuoiCung, CotCuoicung As Long
        With oSheet.UsedRange
            DongThunhat = 7         'Dòng dữ liệu bắt đầu lấy
            CotThunhat = 1              'Cột bắt đầu lấy
            DongCuoiCung = .Rows(UBound(.Value)).Row
            CotCuoicung = TongSoCot             'Tổng số cột cần lấy
        End With
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset(TenTable, dbOpenTable)
    Dim i As Long
    Dim j As Long
    ' Duyet tu dong thu 7 den dong cuoi cung
    For i = DongThunhat To DongCuoiCung
        rst.AddNew
        ' duyet tu cot thu nhat den cot cuoi cung
        For j = CotThunhat To CotCuoicung
            rst.Fields(j - CotThunhat) = oSheet.Cells(i, j)
'            Debug.Print oSheet.Cells(i, j)
        Next
        If rst.Fields(0) <> "" Then rst.Update
    Next
    oBook.Close False
    Set oEx = Nothing: rst.Close
    
End Function 


  
  
  Và đây là cú pháp để gọi hàm ImportExcel
  
  

  Mã PHP:

  Function Napdulieu()
On Error GoTo Loi
Dim LinkFile As String
LinkFile = CurrentProject.Path & "\test.xlsx"

    ImportExcel "Test_Get_Data", LinkFile, "Sheet1", 12
    MsgBox "Nap du lieu thanh cong?", vbInformation, "Thông báo"
ThoatLoi:
    Exit Function
Loi:
    MsgBox "Loi: Vui long kiem tra lai danh sach tren file Excel cua ban da nhap day du chua?", vbInformation, "Thông báo"
    Resume ThoatLoi
End Function 


  
  Đính kèm Demo theo bài bạn. Nếu bạn muốn trói buộc thì có thể làm theo 2 cách nửa:
  + Viết class cho table
  + Chỉnh lại đoạn duyệt For Next phía trên theo Fields tương ứng
  
  
  Hy vọng giúp được phần nào!
  Thân mến!

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  kieu manh > 06-11-18, 09:33 AM

  bài này dùng Workbooks.Open nếu file Excel mà dữ liệu nhiều hay dung lượng lớn mở lên thôi cũng thấy rất lâu rồi
  sao không dùng ADO lấy lên xong ghi vào TableName nó sẻ nhanh hơn đấy
  Tại mạnh thấy bài này úp bên GPE Mạnh làm cho là ghi từ Excel vào Access nay qua đây thấy vậy nên mới nghĩ ra xài ADO để lấy từ Excel lên Access

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  ongke0711 > 06-11-18, 01:29 PM

  Nếu không có tuỳ chọn cột nào mà import toàn bộ Sheet Excel thì bạn dùng:
  
  DoCmd.TransferSpreadsheet acImport, , "Test_Get_Data", "D:\Test\Test.xlsx", True, "A6:L6535"
  
  Chú ý là tên field trong table Access phải trùng với tên cột trong file Excel.
  Cách này này nó sẽ import toàn bộ không loại bỏ các dòng dạng Text.

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  duynamvnn1208 > 07-11-18, 08:28 AM

  Bạn dùng thử link table chưa?
  Vì mình chỉ cần link table thôi, nếu soure path không đổi, file name không đổi thì hàng ngày mình chỉ cần refresh lại link là được mà?
  Đâu cần import gì đâu cho mất công?

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  kieu manh > 10-11-18, 04:53 PM

  (05-11-18, 09:14 PM)maidinhdan Đã viết:

  (04-11-18, 12:47 PM)subasatran Đã viết: Mình có một file nguồn excel được xuất ra từ hệ thống hàng ngày(không thay đổi format được). Bây giờ mình muốn import vào access để xử lý dữ liệu.
  Vấn đề gặp phải là dữ liệu file excel có một cột CODE(cột E file ví dụ) format từ hệ thống đang là number nhưng dữ liệu lại có một số dòng là text. Nên khi import các dòng này không import được.
  Nhờ mọi người hướng dẫn tạo một hàm bằng VBA để kiểm tra tự động import theo dữ liệu mà không phải chỉnh lại format của file excel.
  Mình có làm một hàm nhỏ để kiểm tra dữ liệu nhưng vẫn không import được. Rất mong mọi người giúp đỡ.
  

  Mã:

  Public Function ConvertNumberToText(ByVal txtnumber As String) As String
[/color]
If IsNumeric(Val(txtnumber)) = True Then
   ConvertNumberToText = Format(CStr(txtnumber), "00000000")
Else
   ConvertNumberToText = CStr(txtnumber)
End If
[color=#141414]End Function

  
  File ví dụ https://www.mediafire.com/file/2e8dje1g3...9.zip/file

  
  Sử dung hàm ImportExcel này:
  

  Mã PHP:

  Function ImportExcel(TenTable As String, DuongdanFile As String, TenSheet As String, TongSoCot As Byte)
    Dim oEx As New Excel.Application     ' oEx la bien oExcel
    Dim oBook As Workbook              ' set oBook la tap hop oExcel
    ' Mo file can chen
    Set oBook = oEx.Workbooks.Open(DuongdanFile)
    ' Mo Sheet can chen
    Dim oSheet As Worksheet                 ' set oSheet la tap hop Sheet
    Set oSheet = oBook.Worksheets(TenSheet)

    ' Dem so dong, so cot
        Dim DongThunhat, CotThunhat, DongCuoiCung, CotCuoicung As Long
        With oSheet.UsedRange
            DongThunhat = 7         'Dòng dữ liệu bắt đầu lấy
            CotThunhat = 1              'Cột bắt đầu lấy
            DongCuoiCung = .Rows(UBound(.Value)).Row
            CotCuoicung = TongSoCot             'Tổng số cột cần lấy
        End With
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset(TenTable, dbOpenTable)
    Dim i As Long
    Dim j As Long
    ' Duyet tu dong thu 7 den dong cuoi cung
    For i = DongThunhat To DongCuoiCung
        rst.AddNew
        ' duyet tu cot thu nhat den cot cuoi cung
        For j = CotThunhat To CotCuoicung
            rst.Fields(j - CotThunhat) = oSheet.Cells(i, j)
'            Debug.Print oSheet.Cells(i, j)
        Next
        If rst.Fields(0) <> "" Then rst.Update
    Next
    oBook.Close False
    Set oEx = Nothing: rst.Close
    
End Function 


  
  
  Và đây là cú pháp để gọi hàm ImportExcel
  
  

  Mã PHP:

  Function Napdulieu()
On Error GoTo Loi
Dim LinkFile As String
LinkFile = CurrentProject.Path & "\test.xlsx"

    ImportExcel "Test_Get_Data", LinkFile, "Sheet1", 12
    MsgBox "Nap du lieu thanh cong?", vbInformation, "Thông báo"
ThoatLoi:
    Exit Function
Loi:
    MsgBox "Loi: Vui long kiem tra lai danh sach tren file Excel cua ban da nhap day du chua?", vbInformation, "Thông báo"
    Resume ThoatLoi
End Function 


  
  Đính kèm Demo theo bài bạn. Nếu bạn muốn trói buộc thì có thể làm theo 2 cách nửa:
  + Viết class cho table
  + Chỉnh lại đoạn duyệt For Next phía trên theo Fields tương ứng
  
  Hy vọng giúp được phần nào!
  Thân mến!

  Có Code nào khi mình lấy dữ liệu lên TableName thì nó tự động Refresh All không bạn Nhỉ ... thay vì phải làm thủ công trên Menu  Access

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  ongke0711 > 10-11-18, 11:24 PM

  (10-11-18, 04:53 PM)kieu manh Đã viết: ...
  Có Code nào khi mình lấy dữ liệu lên TableName thì nó tự động Refresh All không bạn Nhỉ ... thay vì phải làm thủ công trên Menu  Access

  
  Chưa hiểu ý bạn Kieu manh vụ "Refresh All làm thủ công...". Bạn nói rõ hơn chút đi.

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  kieu manh > 11-11-18, 07:50 AM

  (10-11-18, 11:24 PM)ongke0711 Đã viết:

  (10-11-18, 04:53 PM)kieu manh Đã viết: ...
  Có Code nào khi mình lấy dữ liệu lên TableName thì nó tự động Refresh All không bạn Nhỉ ... thay vì phải làm thủ công trên Menu  Access

  
  Chưa hiểu ý bạn Kieu manh vụ "Refresh All làm thủ công...". Bạn nói rõ hơn chút đi.

  
  Ý mình nói là khi ta dùng code lấy dữ liệu lên TableName xong rồi nhưng nhìn vào TableName đó chưa thấy có dữ liệu mà phải thao tác thủ công là bấm vào Refresh All trên Menu Access ấy thì dữ liệu trên TableName nó mới Show ra thì mới nhìn thấy được

• RE: Hàm kiểm tra dữ liệu để import dữ liệu từ excel vào access

  ongke0711 > 11-11-18, 09:57 AM

  (11-11-18, 07:50 AM)kieu manh Đã viết: Ý mình nói là khi ta dùng code lấy dữ liệu lên TableName xong rồi nhưng nhìn vào TableName đó chưa thấy có dữ liệu mà phải thao tác thủ công là bấm vào Refresh All trên Menu Access ấy thì dữ liệu trên TableName nó mới Show ra thì mới nhìn thấy được

  
  
  Nếu theo như file demo của bạn maidinhdan thì khi bấm nút lấy dữ liệu thì dữ liệu đã được load về table rồi. 
  - Nếu bạn mở table đó lên trước để xem khi bấm nút lấy dữ liệu thì sau khi load về dữ liệu không cập nhật trên màn hình thôi. Mọi truy vấn đối với table đó vẫn có dữ liệu.
  - Nếu bạn muốn hiển thị dữ liệu cập nhật lên trên màn hình luôn thì chỉ cần thêm Requery cho Form hay SubForm là được rồi.
         Me.Requery
     Me.SubForm.Requery
  Me: đại diện cho Form có Source là table vừa nạp dữ liệu.
• Bài trước Bài tiếp