sql - 将带有结果的多个查询导出到 Excel 到一个工作表中
问题描述
我想将我的数据库中以“WWEI”开头的所有查询(有记录)导出到一个 Excel 工作表中,并在彼此下方列出。
strFullPath = "C:\Users\test.xlsx"
Set wb = xl.Workbooks.Add
Set wb = xl.Workbooks.Open(strFullPath)
Set ws = wb.Worksheets(1)
Set r = ws.Range("a1")
r = "Possible Mistakes"
Set r = r.Offset(2, 1)
For Each qdf In CurrentDb.QueryDefs
If Mid(qdf.Name, 1, 4) = "WWEI" Then
querybezeichnung = qdf.Name
If DCount("*", querybezeichnung) > 0 Then
Set rs = CurrentDb.OpenRecordset(querybezeichnung)
With rs
For i = 1 To .Fields.Count
r.Cells(1, i) = .Fields(i - 1).Name
r.Cells(1, i).Font.Bold = True
'r.Cells(1, i).AutoFilter
Next i
End With
Set r = r.Offset(1, 0)
r.CopyFromRecordset rs
rs.Close
Set r = r.End(xlDown).Offset(2, 0)
End If
End If
Next qdf
我有一个运行时错误“1004”在线:
Set r = r.End(xlDown).Offset(2, 0)
解决方案
Set r = r.End(xlDown).Offset(2, 0)
instead of dis can't you use
Set r = r.Offset( recordcountofrs + 2, 0)
'________________________________________________________
strFullPath = "C:\Users\test.xlsx"
Set wb = xl.Workbooks.Add
Set wb = xl.Workbooks.Open(strFullPath)
Set ws = wb.Worksheets(1)
Set r = ws.Range("a1")
r = "Possible Mistakes"
Set r = r.Offset(2, 1)
For Each qdf In CurrentDb.QueryDefs
If Mid(qdf.Name, 1, 4) = "WWEI" Then
querybezeichnung = qdf.Name
If DCount("*", querybezeichnung) > 0 Then
Set rs = CurrentDb.OpenRecordset(querybezeichnung)
With rs
For i = 1 To .Fields.Count
r.Cells(1, i) = .Fields(i - 1).Name
r.Cells(1, i).Font.Bold = True
'r.Cells(1, i).AutoFilter
Next i
End With
Set r = r.Offset(1, 0)
r.CopyFromRecordset rs
LstRow = rs.RecordCount '<== this line added
rs.Close
Set r = r.Offset(LstRow+2, 0) '<== this line changed
End If
End If
Next qdf
推荐阅读
- pyomo - 访问约束/目标
- odoo - 按categ_id 过滤行并计算数量
- php - 如何在登录时创建(注册)用户?
- 32bit-64bit - 使程序在 32 位 Window 操作系统上运行
- css - CSS :checked 选择器不起作用
- git - 为什么 Bibucket Server 在本地存储 LFS 对象?
- java - 解决办法是什么
- ios - UIScrollView 在我更新 contentInset 时更改 contentOffset
- python - SQLAlchemy 可以将 grouped_by 查询作为字典返回吗?
- bash - 如何在 sed 命令中使用带有 \ 的变量