VBA创建查询并批量导出excel
VBA代码:重阳诗句’ 新建一个查询，根据查询条件把access数据批量导出为N个Excel文件特殊符号大全Sub accessTOexcel()
Dim strsql As String
Dim qry As DAO.QueryDef
Set qry = CurrentDb.QueryDefs('导出查询')
Dim rst As New ADODB.Recordt
strsql = 'Select DISTINCT 供应商运营是什么 FROM 采购价格'
rst.Open strsql, CurrentProject.Connection, adOpenKeyt, adLockOptimistic
rst.MoveFirst
Do Until rst.EOF
qry.Name = rst(0)
qry.SQL = 'lect 商品名称,供应商 from 采购价格 where 供应商='' & rst(0) & '''
If Len(Dir(CurrentProject.Path & '\' & qry.Name & '.xls')) > 0 Then Kill CurrentProject.Path & '\' & qry.Name & '.xls'
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qry.Name, CurrentProject.Path & '\' & qry.Name & '.xls', True
rst.MoveNext
Loop
qry.Name = '导出查询'
End SubSub 新建查询()
英华殿
Dim SQL0 As String
SQL0 = 'lect * from 采购价格 where 1<>1'
If SQL0 <> '' Then
CreateQuery SQL0, '导出查询'
Application.RefreshDatabaWindow
End If
End Sub
邓超主演的电影Private Sub CreateQuery(ByVal ssql As String, QueryName As String)
Dim Qdef As QueryDef
西安到关山牧场 On Error Resume Next
If DCount('*', 'MSysObjects', 'Type=5 and Name='' & QueryName & ''') = 0 Then
Set Qdef = CurrentDb.CreateQueryDef(QueryName)
El
Set Qdef = CurrentDb.QueryDefs(QueryName)
End If
Qdef.SQL = ssql
Qdef.Clo
Set Qdef = Nothing
End SubSub 删除查询()
1078年 On Error Resume Next
DoCmd.DeleteObject acQuery, '导出查询'
绳文人 Application.RefreshDatabaWindow
End Sub
Public Function QueryExists(strQueryName As String) As Boolean
Dim accQry As AccessObject
QueryExists = Fal
For Each accQry In CurrentData.AllQueries
If strQueryName = accQry.Name Then
QueryExists = True
Exit For
End If
Next accQry
End Function
