导出至Excel实例:
Dim theTempData As Variant, fd As FileDialog, thePath As String
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
.Title = "导出至 .xls类型"
.AllowMultiSelect = False
.ButtonName = "确定(&O)"
End With
theAddTextBox "请选择一个 Excel格式(.xls)文件，或单击“取消”按钮以退出！"
If fd.Show = -1 Then
thePath = fd.SelectedItems(1)
Set fd = Nothing
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT 猪群档案表.耳号, 猪群档案表.性别, 选项表.属性值 AS 种类, 选项表_1.属性值 AS 品种, 选项表_2.属性值 AS 状态, 选项表_3.属性值 AS 饲养员, 选项表_4.属性值 AS 所在猪舍 INTO 合格猪群档案 " & _
"FROM 选项表 AS 选项表_4 INNER JOIN (选项表 AS 选项表_3 INNER JOIN (选项表 AS 选项表_2 INNER JOIN (选项表 AS 选项表_1 INNER JOIN (选项表 INNER JOIN 猪群档案表 ON 选项表.属性ID = 猪群档案表.种类) ON 选项表_1.属性ID = 猪群档案表.品种) ON 选项表_2.属性ID = 猪群档案表.状态) ON 选项表_3.属性ID = 猪群档案表.饲养员) ON 选项表_4.属性ID = 猪群档案表.所在猪舍 " & _
IIf(stWhereString = "", ";", "WHERE (" & stWhereString & ");")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "合格猪群档案", thePath & IIf(Right(thePath, 3) = "xls", "", ".xls")
DoCmd.DeleteObject acTable, "合格猪群档案"
DoCmd.SetWarnings True
End If
原理是:先将子窗体中的记录生成一个临时表,然后将之导出,之后,再将临时表删除之.SQL语句,是生成表查询
注意:要打开另存为对话框 ,要进行Office库的引用.
