VBA 学习

Option Compare Database
Private Sub Command0_Click()
MsgBox "hello"
End Sub

Private Sub Command1_Click()
    DoCmd.Beep
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "火工品已发射星", "C:/Documents and Settings/Administrator/桌面/test/DEMO.xls", True, "ruix"
End Sub

Private Sub Command2_Click()

'导入一个excel文件到指定表格
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Employees", CurrentProject.Path & "/DEMO.xls", True

End Sub

Private Sub Command3_Click()
 '导出所有表到一个excel文件
 ExportTableToOneXls CurrentProject.Path & "/empExEg.xls"
End Sub

Public Function ExportTableToOneXls(ByVal XlsPath As String)
   
    '这里用 ADO 来获取所有的表名，当然，你完全可以用查询系统表的方法来实现
   
    Dim rstSchema As ADODB.Recordset
    Dim cnn2 As ADODB.Connection
    Set cnn2 = CurrentProject.Connection
    Set rstSchema = cnn2.OpenSchema(adSchemaTables)
    Dim i As Long
    Do Until rstSchema.EOF
        If rstSchema("TABLE_TYPE") = "TABLE" Then
            '对应 TABLE_TYPE 可以取值 "TABLE" "SYSTEM TABLE" "ACCESS TABLE"
            '在这里我们列出所有用户表的信息，系统表不列出
            For i = 0 To rstSchema.Fields.Count - 1
                Debug.Print rstSchema(i).Name & "-> " & rstSchema.Fields(i).Value
                If rstSchema(i).Name = "TABLE_NAME" Then
                    '用 TransferSpreadsheet 实现导出，当然，你完全可以用 in 子句来实现
                    '    《查询》如何查询其他数据库内的表(IN子句实例)？
                    '    http://access911.net/index.asp?u1=a&u2=71FAB21E17DC
                    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rstSchema.Fields(i).Value, XlsPath, True
                End If
            Next
        End If
        rstSchema.MoveNext
    Loop
    rstSchema.Close
    cnn2.Close
End Function