AccessַʵEXCEL  
 ߣ    תԣվԭ    6459    ʱ䣺2004-4-12    ¼룺  
 


        ߣ(΢мֵר)

 

: 2004-4-12

 

ժ        Ҫ:ӦAccessбӡʱ,ЩۺԽǿıҪķѼľ,ͬʱ,ҪŴСʱ,AccessıΪԱ,ʱ,ExcelƳɱģ,AccessExcelģнʧΪһֽ,òͬı˼·Բ

 

      ģ

 

һݵExcel

1.       EXCELģúƺñ(ʾļǡ۱.xls)ʽ(1)ĺʽ趨ã磺

   ַֹ˾=SUMIF(queۻ!$A$1:$A$11,A4,queۻ!$B$1:$B$11)


 

SUMIF˵

ָɵԪ͡

﷨

SUMIF(range,criteria,sum_range)

Range    ΪжϵĵԪ

Criteria    ΪȷЩԪ񽫱͵ʽΪ֡ʽı磬ԱʾΪ 32"32"">32"  "apples"

Sum_range    Ҫ͵ʵʵԪ

ָqueۻܡA1A11ֵڡ1A4ԪʽڵԪֵڡqueۻܡB1B11ĵԪֵ֮͡

 

2.       Accessļд۷ֹ˾ܵݣʾ£

 

Function out_Excel()

'һ,Excelúñ,ٵݵEXCEL

    On Error GoTo out_Excel_Err

    Dim xlsPath As String

    'ָExcelļ·

    xlsPath = CurrentProject.Path + "\۱.xls"

    'ExcelļǷ,

    If Dir(xlsPath) = "" Then

        MsgBox "۱.xlsļ,뽫ExcelļmdbļͬһĿ¼", vbCritical, "ʾ"

        End

    End If

    DoCmd.TransferSpreadsheet acExport, 8, "queۻ", xlsPath, True, ""

    MsgBox "ɹ!", vbInformation, "ϲ"

 

out_Excel_Exit:

    Exit Function

 

out_Excel_Err:

    MsgBox Error$

    Resume out_Excel_Exit

End Function

 
 дݵExcel

1.       EXCELģƺñ(ʾļǡ۱.xls)ָ2

2.       ʾ£

Private Sub Command1_Click()

',Accessед뵽ExcelӦĵԪ

'ҪDAO 3.6 Microsoft EXCEL 9.0 Microsoft EXCEL 11

    Dim ExcelApp As excel.Application

    

    Dim Book As excel.Workbook

    Dim ws As Worksheet

    DoCmd.SetWarnings False

    Dim xlsPath As String

    xlsPath = CurrentProject.Path + "\۱.xls"

    Set ExcelApp = New excel.Application

    Set ws = ExcelApp.Workbooks.Open(xlsPath).Worksheets("2")

    'ۻܱ tblۻ

    DoCmd.OpenQuery "queۻ2", acViewNormal

    Dim rst1 As DAO.Recordset

    'ExcelļǷ,

    If Dir(xlsPath) = "" Then

        MsgBox "۱.xlsļ,뽫ExcelļmdbļͬһĿ¼", vbCritical, "ʾ"

        End

    End If

 

    Set rst1 = CurrentDb.OpenRecordset("tblۻ", dbOpenDynaset)

    rst1.MoveFirst

    Do Until rst1.EOF

        If rst1("۷ֹ˾") = "ַֹ˾" Then

            'ws.cells(4,3)ָ"2"ĵ43ཻĵԪ

            ws.Cells(4, 3) = rst1("ۼ")

        End If

        If rst1("۷ֹ˾") = "ֹ˾" Then

            ws.Cells(5, 3) = rst1("ۼ")

        End If

        If rst1("۷ֹ˾") = "׶طֹ˾" Then

            ws.Cells(6, 3) = rst1("ۼ")

        End If

        If rst1("۷ֹ˾") = "ŦԼֹ˾" Then

            ws.Cells(7, 3) = rst1("ۼ")

        End If

        If rst1("۷ֹ˾") = "Ƿֹ˾" Then

            ws.Cells(8, 3) = rst1("ۼ")

        End If

        rst1.MoveNext

    Loop

 ExcelApp.Visible = True

    Set ExcelApp = Nothing

    Set Book = Nothing

End Sub

 

    ᣺

ܵ˵ַʵָExcelļͱ߸˹۵˵ExcelƼʹ÷һȽϱһЩȻҪƯıExcelļ¹ɵı(ָque ۻܡ)һҲǷǳġ

ʾ     


