Xuất dử liệu từ Access ra Excel và ngược lại

[cadafi]

Sự khách biệt giữa hai cách import dữ liệu từ excel vào access

Các anh chị giúp em. Em đã tìm ra đoạn code để import từ excel vào access như sau:
http://www.utteraccess.com/forums/printthread.php?Cat=&Board=88&main=497117&type=thread

'Cách 1:
Function InsertInto_GLDATA()
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
'-------------------------------------------------------------------------------
Application.ScreenUpdating = False
Dim gcnAccess as New ADOB.connection
Dim gcnRecords as New ADOB.Recordset
Dim rngDetail As Range
Dim tblName As String
tblName = "GLDATA"

'-------------------------------------------------------------------------------
'Open connection to the database
Call ConnectToDatabase
'Chuan bi tien hanh import du lieu vao access
On Error GoTo EndUpdate
'-------------------------------------------------------------------------------
'Insert records into database from worksheet table
gcnAccess.BeginTrans

Set gcnRecords = New ADODB.recordset

For Each rngDetail In Range(Sheets("GLDATA").[a2], Sheets("GLDATA").[a65536].End(xlUp))
 gcnRecords.Open "Select Period,DocNbr,DocDate,DocDesc,DocAttached,DrAcct,CrAcct,CustVendID,InvtID,LineQty," & _
                                        "LineUP,LineAmt,Serial,InvcNbr,InvcDate,TaxRate,InvcDesc,ContactName,WorkCode,TaxID," & _
                                        "Division,CostCenter,CflwID,User,Date From " & tblName, gcnAccess, adOpenStatic, adLockPessimistic, adCmdText
 
With gcnRecords
         .AddNew  ' create a new record
 ' Add values to each field in the record
    .Fields("Period") = rngDetail.Value
    .Fields("DocNbr") = rngDetail(, 2).Value
    .Fields("DocDate") = rngDetail(, 3).Value
    .Fields("DocDesc") = rngDetail(, 4).Value
    .Fields("DocAttached") = rngDetail(, 5).Value
    .Fields("DrAcct") = rngDetail(, 6).Value
    .Fields("CrAcct") = rngDetail(, 7).Value
    .Fields("CustVendID") = rngDetail(, 8).Value
    .Fields("InvtID") = rngDetail(, 9).Value
    .Fields("LineQty") = rngDetail(, 10).Value
    .Fields("LineUP") = rngDetail(, 11).Value
    .Fields("LineAmt") = rngDetail(, 12).Value
    .Fields("Serial") = rngDetail(, 13).Value
    .Fields("InvcNbr") = rngDetail(, 14).Value
    .Fields("InvcDate") = rngDetail(, 15).Value
    .Fields("TaxRate") = rngDetail(, 16).Value
    .Fields("InvcDesc") = rngDetail(, 17).Value
    .Fields("ContactName") = rngDetail(, 18).Value
    .Fields("WorkCode") = rngDetail(, 19).Value
    .Fields("TaxID") = rngDetail(, 20).Value
    .Fields("Division") = rngDetail(, 21).Value
    .Fields("CostCenter") = rngDetail(, 22).Value
    .Fields("CflwID") = rngDetail(, 23).Value
    .Fields("User") = rngDetail(, 24).Value
    .Fields("Date") = rngDetail(, 25).Value
    .Update ' stores the new record
    .Close
    End With
Next
   a = MsgBox("Da cap nhat du lieu vao he thong thanh cong!", vbInformation, "Thong bao")
'-------------------------------------------------------------------------------------------------
EndUpdate:
    'Check if error was encounted
  If Err.Number <> 0 Then
      'Error encountered.  Rollback transaction and inform user
      On Error Resume Next
      gcnAccess.RollbackTrans
      MsgBox vbObjectError & "-" & Err.Number & "-" & Err.Description, vbCritical, "Error!"
  Else
      On Error Resume Next
      gcnAccess.CommitTrans
  End If
    
'Close the ADO objects
     gcnAccess.Close
    Set gcnRecords = Nothing
    Set gcnAccess = Nothing
    On Error GoTo 0
Application.ScreenUpdating = True
End Function

So sánh với đoạn code import trong module của em, không biết lỗi ở đâu:

'Cách 2:
Function InsertInto_GLDATA()
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
'-------------------------------------------------------------------------------
Dim colHead, rcdDetail As String
Dim rngHeader, rngDetail As Range
Dim NColumn As Integer
Dim NRow As Integer
Dim NotNull As Boolean
Dim tblName As String
Dim TargetDetail As New ADODB.recordset
Set rngHeader = Range("A1:Y1")
Set rngDetail = Range("A2:Y2")
tblName = "GLDATA"
'MsgBox rngDetail.Address & Chr(13) & rngHeader.Address, , "Thong bao"

'Truyen tham so cot can insert vao access:
'Ke noi chuoi cac cot trong cu pham Insert into tblTemp(column1, column2,column3,....)
colHead = " ("
    For NColumn = 1 To rngHeader.Count
        colHead = colHead & rngHeader.Columns(NColumn).Value
        Select Case NColumn
            Case Is = rngHeader.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next NColumn
    
 MsgBox "Insert into " & colHead & " From " & tblName, , "Thong bao" 'kietvt: Bay loi tai day
'-------------------------------------------------------------------------------
'Open connection to the database
Call ConnectToDatabase
'Chuan bi tien hanh import du lieu vao access
On Error GoTo EndUpdate
'-------------------------------------------------------------------------------
'Insert records into database from worksheet table
   gcnAccess.BeginTrans
    For NRow = 1 To rngDetail.Rows.Count
       'Assume record is completely Null, and open record string for concatenation
        NotNull = False
        rcdDetail = "('"

        'Evaluate field in the record
        For NColumn = 1 To rngHeader.Count
            Select Case rngDetail.Rows(NRow).Columns(NColumn).Value
                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case NColumn
                        Case Is = rngHeader.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "'')" 'Neu gia tri null thi bo qua va cach nhau boi dau ")" trong truong hop cot cuoi cung
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & ",'" 'Neu gia tri null thi bo qua va cach nhau boi dau ")" trong truong hop cot cuoi cung
                    End Select

                    'if not empty, set notNull to true, and append value to string
                Case Else
                    NotNull = True
                    Select Case NColumn
                        Case Is = rngHeader.Count
                            rcdDetail = rcdDetail & rngDetail.Rows(NRow).Columns(NColumn).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngDetail.Rows(NRow).Columns(NColumn).Value & "','"
                    End Select
                End Select
         Next NColumn
           MsgBox rcdDetail, , "Thongbao"    'kiet
        'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case NotNull
            Case Is = True
                TargetDetail.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, gcnAccess
            Case Is = False
                'do not insert record
        End Select
    Next NRow
    a = MsgBox("Da cap nhat du lieu vao he thong thanh cong!", vbInformation, "Thong bao")

   
    
EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        gcnAccess.RollbackTrans
        MsgBox vbObjectError & "-" & Err.Number & "-" & Err.Description, vbCritical, "Error!"
    Else
        On Error Resume Next
        gcnAccess.CommitTrans
    End If
    
'Close the ADO objects
     gcnAccess.Close
    Set TargetData = Nothing
    Set gcnAccess = Nothing
    On Error GoTo 0
End Function

Tuy nhiên cách 1 em đã import thành công vào access.
Còn với cách 2, em chỉ impor được vào table GLINVC, nhưng không import được vào table GLDATA, thiệt tình không hiểu? (vì cấu trúc lệnh INSERT INTO GLDATA (field1,field2,...,field n) em làm đúng mà!.....
Các anh chị giúp em với.

 

[caythachluu]

Em cũng đang muốn đưa dữ liệu từ 1 file Excel vào trong file cơ sở dữ liệu đã có sẵn của Access, anh chị có thể chỉ giúp em được không ?

 

[levanduyet]

To: ca_dafi,

Hai cách của em không có gì sai. Vậy vấn đề là chổ nào?
_ Cách một sẽ dễ hiểu hơn.
_ Cách hai em chú ý cho nếu giá trị là chuổi thì em phải đặt trong ''. Ví dụ:

INSERT INTO tb_Customer(CusId, CusName) VALUES(2, 'CA_DAFI')

Đó là lý do lỗi xãy ra khi em thực hiện theo cách hai.

Thông thường để nhập dữ liệu từ Excel vào Access chúng ta nên làm theo các bước sau (nếu có nhiều bảng cần phải đưa vào Access)
_ Thiết kế form giúp nhập liệu, giả sử tôi có form như hình sau:

Combobox1: nhằm tạo danh sách các bảng dữ liệu cần phải đưa vào bảng trong Access.
Combobox2: là danh sách các workbook đang mở hiện hành.
Combobox3: là danh sách các worksheet khi bạn chọn workbook tại combobox2.

Ngoài ra còn có các chức năng như: Kiểm tra tiêu đề của bảng dữ liệu nhập vào, Xóa dữ liệu của bảng trước khi nhập vào.

Và cuối cùng là thực hiện thao tác nhập vào.
Chú ý:
_Nếu bảng dữ liệu của bảng nhiều record thì việc nhập liệu vào sẽ hơi lâu.
_Nhằm tránh việc nhập dữ liệu không đúng như kiểu dữ liệu trong Access, bạn nên kiểm tra trước hoặc kiểm tra trước khi nhập vào.

Xin tham khảo code của tập tin form ở trên.

Vài kinh nghiệm xin chia sẻ.

Lê Văn Duyệt

 

[anhphuong]

Excel và Access

Trên GPE cũng đã có rất nhiều bài viết về làm việc trên Ex và đưa dữ liệu lưu trữ vào trong Ac. Khi đó việc kết nối CSDL là một việc làm quan trọng nhất. Cũng có nhiều cao thủ đã chỉ ra việc kết nối CSDL và chủ yếu là dùng kết nối bằng ADO. Việc kết nối CSDL bằng ADO là hoàn chỉnh nhất và đúng cho mọi việc kết nối, nhưng không phải ai cũng rành rẽ về việc này. Hôm nay xin giới thiệu với các bạn một cách kết nối đơn giản với một file Access từ Ex mà bạn nào cũng có thể làm được dựa vào việc định nghĩa một không gian làm việc của Ac và đưa CSDL Ac vào không gian làm việc đó rồi từ đ1o chúng ta dùng các lệnh, hàm của Ac mà xử lý dữ liệu. Để làm được việc này, trước hết trong cửa sổ VBE của Ex, bạn vào Tools/References chọn các thư viện động sau đây :
1/ Microsoft Access 11.0 Ofject Library
2/ Microsoft DAO 3.6 Ofject Library
3/ Microsoft Visual Basic for Applications Extensibility 5.3
Sau đó, để có thể làm việc được với CSDL Ac, trong Modul của Ex, các bạn định nghĩa như sau :

Dim dtbAc As Database
Dim strTen As String
Set strTen = "D\BaiTap\Data.mdb"
Set dtbAc = DBEngine.Workspaces(0).OpenDatabase(strTen)

Bằng việc định nghĩa như vậy, bây giờ bạn có thể vô tư vọc Ac ngay trên Ex rồi đấy. Đây là cách mà tôi vẫn thường dùng và dùng từ thời còn là Ac 2.0. Bài sau tôi sẽ hướng dẫn các bạn dùng các lệnh và hàm của Ac để truy vấn và xử lý dũ liệu trong CSDL Ac và đưa ra Ex
Chúc các bạn thực hành tốt
Thân

 

[anhphuong]

Trên GPE đã có rất nhiều cách trình bày về khai thác xử lý dữ liệu từ Ac đưa ra Ex. Các bạn có thể dùng các lệnh đưa nguyên cả một table hay một query trong Ac ra Ex, có thể dùng Query của Ex để lấy dữ liệu, và cũng có thể dùng A_Tools của Mr.Tuân… Ở đây tôi trình bày cách sử dụng ngay query của Ac và các hàm người dùng viết trong Ex để lấy dữ liệu. Cụ thể tôi sẽ trình bày cách làm để có thể lấy dữ liệu về tồn quỹ tiền mặt của cơ sở dữ liệu Data.mdb ở trên và đưa ra Excel.
Trước hết ta chuẩn bị như sau :

1/ Giả sử trong CSDL Data.mdb, các bạn có dử liệu của 2 table tblPhieuThuChi và tblPhieuChiTiet với các trường như sau .
Table tblPhieuThuChi :
+ RecKey – Text – 20 (Primarykey -Yes (No Duplicates))
+ Ngay – Date/Time
+ SoCT – Text – 6
+ LoaiCT – Text – 2
+ DienGiai – Text – 100
Table tblPhieuChiTiet :
+ RecKey – Text – 20(Index - Yes (Duplicates OK))
+ TKDU – Text – 6
+ SoTien – Number – Double
Và dĩ nhiên hai khóa RecKey phải được kết nối theo dạng Một – Nhiều, các table đã có dữ liệu. Cách nhập liệu có thể nhập trực tiếp hoặc tham khảo file đính kèm ở cuối bài

2/ Các bạn tạo hai query sau :
+ qryChiTietTruoc : Câu lệnh SQL của nó là

SELECT tblPhieuThuChi.RecKey, tblPhieuThuChi.Ngay, tblPhieuThuChi.SoCT, tblPhieuThuChi.LoaiCT, tblPhieuThuChi.DienGiai, tblPhieuChiTiet.TKDU, tblPhieuChiTiet.SoTien
FROM tblPhieuThuChi INNER JOIN tblPhieuChiTiet ON tblPhieuThuChi.RecKey = tblPhieuChiTiet.RecKey
WHERE (((tblPhieuThuChi.Ngay)<[TuNgay]));

Query này sẽ sử dụng để tính tồn quỹ đầu kỳ

+ qryChiTietTrong : Câu lệnh SQL của nó là

SELECT tblPhieuThuChi.RecKey, tblPhieuThuChi.Ngay, tblPhieuThuChi.SoCT, tblPhieuThuChi.LoaiCT, tblPhieuThuChi.DienGiai, tblPhieuChiTiet.TKDU, tblPhieuChiTiet.SoTien
FROM tblPhieuThuChi INNER JOIN tblPhieuChiTiet ON tblPhieuThuChi.RecKey = tblPhieuChiTiet.RecKey
WHERE (((tblPhieuThuChi.Ngay) Between [TuNgay] And [DenNgay]));

Query này sẽ sử dụng để lấy các dữ liệu phát sinh trong kỳ báo cáo

3/ Đồng thời các bạn cũng tạo thêm 2 table nữa như sau :
a/ Table tblTonDau chỉ có một Field duy nhất : TonDau – Number – Double
b/ Table tblChiTiet gồm các trường sau :
+ Ngay – Date/Time
+ SoCT – Text – 6
+ LoaiCT – Text – 2
+ DienGiai – Text – 100
+ TKDU – Text – 6
+ SoTien – Number – Double
Vậy là ta đã chuẩn bị xong nguyên liệu, dụng cụ cần thiết. Tiếp theo ta sẽ xào nấu các món ăn dựa vào các nguyên vật liệu đã chuẩn bị trên đây
Lưu ý : các table và query này các bạn tạo trong Access

 

[anhphuong]

Dưới đây là các hàm người dùng để lấy dữ liệu(Viết trong modul của Excel)


1/ Hàm xóa Table

Function XoaTab(TabName As String)
    Dim TenFile As String
    TenFile = "D:\BaiTap\Data.mdb"
    Dim Tien As Database
    Set Tien = DBEngine.Workspaces(0).OpenDatabase(TenFile)
    Dim ThisTab As Recordset
    Set ThisTab = Tien.OpenRecordset(TabName, dbOpenTable)
    If ThisTab.RecordCount > 0 Then
        ThisTab.MoveFirst
        Do Until ThisTab.EOF
            ThisTab.Delete
            ThisTab.MoveNext
        Loop
    End If
End Function

2/ Hàm tính tồn quỹ đầu kỳ

Function TinhTonDau(TruocNgay)
    Dim TenFile As String
    Dim Tien As Database
    Dim NK As Recordset
    Dim Ton As Recordset
    Dim QNK As QueryDef
    TenFile = "D:\BaiTap\Data.mdb"
    Set Tien = DBEngine.Workspaces(0).OpenDatabase(TenFile)
    Set QNK = Tien.QueryDefs("qryChiTietTruoc")
    QNK.Parameters("TuNgay") = TruocNgay
    Set NK = QNK.OpenRecordset()
    Set Ton = Tien.OpenRecordset("tblTonDau", dbOpenTable)
    On Error Resume Next
    If Ton.RecordCount > 0 Then Call XoaTab("tblTonDau")
    TonDK = 0
    If NK.RecordCount > 0 Then
        NK.MoveFirst
        Do Until NK.EOF
            If NK!LoaiCT = "PT" Then
                TonDK = TonDK + NK!SoTien
            Else
                TonDK = TonDK - NK!SoTien
            End If
            NK.MoveNext
        Loop
    End If
    Ton.AddNew
    Ton!TonDau = TonDK
    Ton.Update
    Ton.Close: Set Tien = Nothing
End Function

3/ Hàm Lấy dữ liệu phát sinh trong kỳ

Function SoQuy(TuNgay, DenNgay)
    Dim TenFile As String
    Dim Tien As Database
    Dim NKTC As Recordset
    Dim NK As Recordset
    Dim QNK As QueryDef
    TenFile = "D:\BaiTap\Data.mdb"
    Set Tien = DBEngine.Workspaces(0).OpenDatabase(TenFile)
    Set QNK = Tien.QueryDefs("qryChiTietTrong")
    QNK.Parameters("TuNgay") = TuNgay
    QNK.Parameters("DenNgay") = DenNgay
    Set NKTC = QNK.OpenRecordset()
    Set NK = Tien.OpenRecordset("tblChiTiet", dbOpenTable)
    On Error Resume Next
    If NK.RecordCount > 0 Then Call XoaTab("tblChiTiet")
    If NKTC.RecordCount > 0 Then
        NKTC.MoveFirst
        Do Until NKTC.EOF
            NK.AddNew
            NK!Ngay = NKTC!Ngay
            NK!SoCT = NKTC!SoCT
            NK!LoaiCT = NKTC!LoaiCT
            NK!DienGiai = NKTC!DienGiai
            NK!TKDU = NKTC!TKDU
            NK!SoTien = NKTC!SoTien
            NK.Update
            NKTC.MoveNext
        Loop
    End If
    NK.Close: NKTC.Close: Set Tien = Nothing
End Function

Vậy là ta đã xào nấu xong các món ăn ngon theo ý thích của mình. Bây giờ ta sẽ bày bàn ăn và dọn các món ăn ngon lên bàn Excel để chiêu đãi bạn bè nhé

 

[anhphuong]

Đưa dữ liệu từ Access ra Excel

Để có được bàn thức ăn ngon mời bạn bè, các bạn chuẩn bị dọn chén đũa, bàn ghế như sau (Mục đích là mở một file SoQuyTienMat.xls mẫu trong máy chưa có số liệu gì và dùng hàm người dùng để ghi các số liệu đã chuẩn bị sẵn trên đây vào file excel này)

1/ Hàm mở file Excel có sẵn trong máy

Function MoFileEx(ExTaiLieu As String)
    Dim ExTen As String
    Dim Ex As Workbook
    ExTen = TimFile(ExTaiLieu)
    Set Ex = GetObject(ExTaiLieu)
    Ex.Application.Windows(ExTen).Visible = True
    Set Ex = Nothing
End Function

2/ Hàm tìm tên một file từ một đường dẫn cho trước

Function TimFile(TenFile As String)
    Dim n As Integer
    For n = Len(TenFile) To 1 Step -1
        ViTri = Mid(TenFile, n, 1)
        If ViTri = "\" Then
            Exit For
        End If
    Next
    TimFile = Right(TenFile, Len(TenFile) - n)
End Function

3/ Hàm lấy dữ liệu trong Access và đưa vào file Exel mẫu

Function LocSoQuy()
    Dim TenFile As String
    Dim Tien As Database
    Dim NK As Recordset
    Dim Ton As Recordset
    TenFile = "D:\BaiTap\Data.mdb"    
    Set Tien = DBEngine.Workspaces(0).OpenDatabase(TenFile)
    Set NK = Tien.OpenRecordset("tblChiTiet", dbOpenTable)
    Set Ton = Tien.OpenRecordset("tblTondau", dbOpenTable)    
    Dim SoQuy As String
    SoQuy = "D:\Luu\So Quy Tien Mat.xls"    
    Call MoFileEx(SoQuy)                     ‘ Mở File Excel mẫu
    Cells(2, 4) = S00.NgayDau             ‘ Lấy giá trị NgayDau đặt vào ô D2
    Cells(3, 4) = S00.NgayCuoi            ‘ Lấy giá trị NgayCuoi đặt vào ô D3
    If Ton.RecordCount > 0 Then
        Cells(3, 6) = Ton!TonDau           ‘ Lấy giá trị Tồn Đầu Kỳ đa75t vào ô F3
    End If    
    If NK.RecordCount > 0 Then
        NK.MoveFirst
        Dim m As Long, n As Long          ‘ Lấy giá trị phát sinh đặt vào ô tương ứng
        For m = 1 To NK.RecordCount
            n = Range("F5000").End(xlUp).Row
            Cells(n + 1, 1) = NK!Ngay
            Cells(n + 1, 2) = NK!SoCT
            Cells(n + 1, 3) = NK!LoaiCT
            Cells(n + 1, 4) = NK!DienGiai
            Cells(n + 1, 5) = NK!TKDU
            If NK!LoaiCT = "PT" Then
                Cells(n + 1, 6) = NK!SoTien
                Cells(n + 1, 7) = 0
            Else
                Cells(n + 1, 7) = NK!SoTien
                Cells(n + 1, 6) = 0
            End If
            NK.MoveNext
        Next
    End If
    Call FormatDongSoQuy    
    NK.Close: Ton.Close: Set Tien = Nothing
End Function

 

[anhphuong]

Để hàm LocSoQuy hoạt động được, trong file Excel kết nối với Access, bạn có một Sheet đặt tên là S00, trên đó có hai textbox tên là NgayDau và NgayCuoi, một commandbuton có tên là Loc để gọi hàm LocSoQuy như sau :

Sub Loc_Click()
    Application.ScreenUpdating = False    
    TuNgay = S00.NgayDau.Value
    DenNgay = S00.NgayCuoi.Value
    Call TinhTonDau(TuNgay)
    Call SoQuy(TuNgay, DenNgay)
    Call LocSoQuy
    Application.ScreenUpdating = True
End Sub

Và thêm hàm FormatDongSoQuy như sau(Mục đích là kẻ dòng và tính tổng) :

Function FormatDongSoQuy()
‘ Kẻ dòng
    Range("A6:G" & Range("G1000").End(xlUp).Row).Select
    With Selection
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
         If Range("G1000").End(xlUp).Row > 6 Then
            .Borders(xlInsideHorizontal).LineStyle = xlDot
         End If
    End With
    Range("A" & Range("G1000").End(xlUp).Row + 1 & ":E" & Range("G1000").End(xlUp).Row + 2).Select
    With Selection
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
    Range("F" & Range("G1000").End(xlUp).Row + 1 & ":G" & Range("G1000").End(xlUp).Row + 2).Select
    With Selection
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With
 
‘ Tính tổng Thu
    Range("F" & Range("G1000").End(xlUp).Row + 1).Select
    ActiveCell.Value = Application.WorksheetFunction.Sum(Range("F6:F" & Range("G1000").End(xlUp).Row))
    ActiveCell.Font.Bold = True
    ActiveCell.NumberFormat = "#,##0"
 
‘Tính tổng Chi
    Range("G" & Range("G1000").End(xlUp).Row + 1).Select
    ActiveCell.Value = Application.WorksheetFunction.Sum(Range("G6:G" & Range("G1000").End(xlUp).Row))
    ActiveCell.Font.Bold = True
    ActiveCell.NumberFormat = "#,##0"
 
‘Ghi chữ Cộng ở dòng Tổng Phát Sinh
    Range("D" & Range("G1000").End(xlUp).Row).Select
    ActiveCell.Value = S00.Cells(5, 1)           ‘ Chữ Cộng ở ô A5
    ActiveCell.Font.Bold = True
    ActiveCell.HorizontalAlignment = xlRight
 
‘Tính tồn cuối kỳ            
    Range("F" & Range("F1000").End(xlUp).Row + 1).Select
    ActiveCell.FormulaR1C1 = "=R3C6+R" & Range("F1000").End(xlUp).Row & "C6-R" & Range("G1000").End(xlUp).Row & "C7"
    ActiveCell.Font.Bold = True
    ActiveCell.NumberFormat = "#,##0"
    Range("D" & Range("F1000").End(xlUp).Row).Select
    ActiveCell.Value = S00.Cells(6, 1)            ‘ Chữ Tồn Quỹ Cuối Kỳ ở ô A6 
    ActiveCell.Font.Bold = True
    ActiveCell.HorizontalAlignment = xlRight
End Function

Vậy là ta đã có một Sổ Quỹ Tiền Mặt được bày trên bàn Excel. Các bạn có thể thưởng thức món ăn này do tay bạn tự chế biến, xào nấu và trưng bày sản phẩm. Vì là số liệu được xử lý thông qua hàm người dùng và đưa vào file mẫu nên khi muốn lưu lại, các bạn tuyệt đối không được nhấn Save vì như vậy sẽ làm hỏng file nguồn, lần sau không sử dụng được nữa. Các bạn phải dùng Save As để lưu ra một tên khác

Chú ý rằng trên đây là món ăn được chế biến khi không gặp phải bất cứ một sự cố nào như nguyên vật liệu chưa được rửa sạch sẽ trước khi chế biến, lửa to, thiếu gia vị….Vì thế các bạn cần phải thêm các bẫy lổi cần thiết như File không tồn tại, Đường dẫn tới file không đúng, File đã được mở nhưng vẫn cố tình nhấn nút, thậm chí có thể dùng lệnh ngăn không cho người dùng Save File mà chỉ có thể Sava as mà thôi……Rất nhiều nơi cần phải bẫy lỗi. Các bạn có thể tham khảo file đính kèm ở cuối bài

Thôi, hôm nay thế đã, ngày mai lại hầu các bác tiếp(hôm nay quên không đem theo file đính kèm)
Chúc cuối tuần vui vẻ
Thân

 

[BinhTam]

query sử dụng hàm nz() trong access

Chào anhphuong,

Mình có câu hỏi như sau :

Nếu trong query có sử dụng hàm NZ() trong access thì từ excel có thể lấy được dữ liệu của query này hay ko?

(vì từ access thì xuất ra excel vẫn bình thường cho dù query có sử dụng hàm nz() cũng không có vấn đề khi export)

Nếu có bạn gửi cho mình 1 ví dụ tham khảo


Thanks,
BinhTâm




2/ Các bạn tạo hai query sau :
+ qryChiTietTruoc : Câu lệnh SQL của nó là

SELECT tblPhieuThuChi.RecKey, tblPhieuThuChi.Ngay, tblPhieuThuChi.SoCT, tblPhieuThuChi.LoaiCT, tblPhieuThuChi.DienGiai, tblPhieuChiTiet.TKDU, tblPhieuChiTiet.SoTien

[font=Verdana]FROM tblPhieuThuChi INNER JOIN tblPhieuChiTiet ON tblPhieuThuChi.RecKey = tblPhieuChiTiet.RecKey[/font]
[font=Verdana]WHERE (((tblPhieuThuChi.Ngay)<[TuNgay]));[/font]

Query này sẽ sử dụng để tính tồn quỹ đầu kỳ

+ qryChiTietTrong : Câu lệnh SQL của nó là

SELECT tblPhieuThuChi.RecKey, tblPhieuThuChi.Ngay, tblPhieuThuChi.SoCT, tblPhieuThuChi.LoaiCT, tblPhieuThuChi.DienGiai, tblPhieuChiTiet.TKDU, tblPhieuChiTiet.SoTien

[font=Verdana]FROM tblPhieuThuChi INNER JOIN tblPhieuChiTiet ON tblPhieuThuChi.RecKey = tblPhieuChiTiet.RecKey[/font]
[font=Verdana]WHERE (((tblPhieuThuChi.Ngay) Between [TuNgay] And [DenNgay]));[/font]

Query này sẽ sử dụng để lấy các dữ liệu phát sinh trong kỳ báo cáo

 

[levanduyet]

To: Bác Phương,

Em góp ý hàm xóa bảng (table) của Bác.
Nếu xóa theo điều kiện thì quét qua các record để xóa. Còn nếu xóa hết thì chỉ cần
"DELETE * FROM Table;" là xong. Chứ còn nếu bảng mà có nhiều dữ liệu quá thì cách của Bác e rằng hơi bị lâu.
Ngoài ra anh có dùng hàm

[B]Function[/B] FormatDongSoQuy()

Sao em thấy không trả về gì cả? Nếu vậy đây chỉ là một thủ tục mà thôi.
Trong hàm này anh có dùng nhiều phương thức

Select, ActiveCell

, em nghĩ không nên dùng chúng vì nó sẽ làm chậm chương trình.

Lê Văn Duyệt

 

[transongngocquan]

Cho tôi hỏi tại sao khi chạy code này:

Dim dtbAc As Database
Dim strTen As String
Set strTen = "D:\BaiTap\Data.mdb"
Set dtbAc = DBEngine.Workspaces(0).OpenDatabase(strTen)

máy báo lổi là: Error in loading DLL (Error 48)

Tôi đã tham chiếu 3 thư viện này rồi:
1/Microsoft Access 11.0 Ofject Library
2/ Microsoft DAO 3.6 Ofject Library
3/ Microsoft Visual Basic for Applications Extensibility 5.3

cám ơn!

 

[levanduyet]

Cho tôi hỏi tại sao khi chạy code này:

Dim dtbAc As Database
Dim strTen As String
Set strTen = "D:\BaiTap\Data.mdb"
Set dtbAc = DBEngine.Workspaces(0).OpenDatabase(strTen)

máy báo lổi là: Error in loading DLL (Error 48)

cám ơn!

Bạn đã tham chiếu đến các thư viện mà anhphuong đã đề cập đến trong thread #1 chưa?

Lê Văn Duyệt

 

[transongngocquan]

Tôi đã tham chiếu 3 thư viện rồi

1/Microsoft Access 11.0 Ofject Library
2/ Microsoft DAO 3.6 Ofject Library
3/ Microsoft Visual Basic for Applications Extensibility 5.3

 

[transongngocquan]

Như vậy có thể các file này của bạn bị hỏng.

Lê Văn Duyệt


1/Microsoft Access 11.0 Ofject Library
2/ Microsoft DAO 3.6 Ofject Library
3/ Microsoft Visual Basic for Applications Extensibility 5.3

3 file này không có bị hỏng. Tôi đã thử trên nhiều máy khác mà vẫn bị là sao ?

 

[anhphuong]

Dear All!
Thành thật xin lỗi mọi người vì thời gian qua bận nhiều việc nên không kịp trả lời các bạn. Với câu hỏi của transongngocquan, tôi xin trả lời như sau :

1/ Lỗi chủ yếu ở đây là do thư viện DAO 306 nằm không đúng vị trí nên gây lỗi Error load DLL.

2/ Bạn vào Tools\References rà tìm đến thư viện Microsoft DAO 3.6 Ofject Library và xem nó nằm ở thư mục nào trong máy.

3/ Bạn mở Explore tìm đến địa chỉ trên, coppy thư viện dao 360.dll vào trong C:\WINDOWS\system32 xong khởi động lại máy tính.

Bây giờ bạn có thể tung hoành được rồi đó. Chỉ khi bạn đăng ký với Windows thì lỗi sẽ không xảy ra nữa.
Chúc bạn thành công.
Thân.

 

[transongngocquan]

Báo lổi undefined function 'IsInternational' in expression

Cho tôi hỏi tại sao lại báo lổi "undefined function 'IsInternational' in expression" khi thực hiện đoạn code như sau bên dưới. Cách khắc phục như thế nào?


1.Open the sample database Northwind.mdb in Microsoft Access.

2.Create a module and type the following procedure:

Function IsInternational(strCountry As String) As String         
           If strCountry = "USA" Then            
                 IsInternational = "Local" 
           Else            
                 IsInternational = "Intl"         
           End If      
End Function

3.Create a new query based on the Customers table as follows:
Query: qryExcelTest --------------------------------------
Type: Select Query
Field: CompanyName Table:
Customers Field: Country Table:
Customers Field: Expr1: IsInternational([Country])

4.Quit Microsoft Access.

5.In Microsoft Excel, create a new workbook.

6.On the Tools menu, point to Macros, and then click Visual Basic Editor. In the Visual Basic Editor, insert a new module sheet by clicking Module on the Insert menu. Type the following sample macro (Sub procedure).

Sub xlTest()         
       Dim db As Database
       rs1 As Recordset         
       Dim qry As QueryDef         
       Set db = Workspaces(0).OpenDatabase _ 
               ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")     
 [COLOR="Blue"]      ' Replace the above path with the correct path to the _       
    ' sample Northwind.mdb database on your computer.  [/COLOR]       
       Set qry = db.QueryDefs("qryExcelTest")         
       Set rs1 = qry.OpenRecordset 
       rs1.MoveLast         
       rs1.Close         
      db.Close      
End Sub

7.On the Tools menu, click References. Click to select the "Microsoft DAO 3.5 Object Library" check box, and then click OK.




Nếu tôi mở file Northwind.mdb bằng Access lên thì khi chạy thủ tục xlTest trong exel thì được. Còn nếu không mở file Northwind.mdb bằng Access thì chạy thủ tục xlTest thì báo lổi:

run-time error '3085'
undefined function 'IsInternational' in expression

Mong các các bạn chỉ giúp làm sao không cần mở bằng Access mà vẫn chạy được thủ tục xlTest ?

Cám ơn rất nhiều !

 

[rollover79]

Không mở Access thì không có module tạo từ Access nên không thể có hàm IsInternational, sao bạn không viết hàm này trong 1 Module của excel?

 

[transongngocquan]

Viết trong Module của excel làm sao mà sử dụng trong query của Access được ?

 

[rollover79]

Đúng là không thể sử dụng đối tượng Query mà bạn tạo ra từ Access. Nhưng bạn hoàn toàn có thể viết lại câu Query để truy vấn bảng Access từ excel mà. Còn hàm viết trong Module của Access mà không mở Access thì không thể sử dụng trong Excel.

 

[anhphuong]

Nói như rollover79 là đúng. Sao bạn không sử dụng ngay query cua Ac để truy vấn dữ liệu rồi lấy dữ liệu đó đưa ra Ex ?. Bạn tham khảo lại bài viết của mình nhé
Thân