请问如何用vba将access的查询写入到excel表的指定区域？

Question

问题比较复杂，access的查询有用到窗体里的值，这个是一定要的。注意是用access写入excel，不是excel读取excel

Answer 1

ACCESS的VBA和EXCEL的VBA语言有差别，不能直接用ACCESS直接执行EXCEL的数据处理。
但你可以形成一个ACCESS的TABLE，填写好数据后导出为EXCEL表格。
另外，你可以利用ACCESS的引用表来引用EXCEL，这样就可以直接添加和修改EXCEL的数据，当然也可以用查询去修改数据。但这种情况不支持多用户操作，会出现意外错误。

Answer 2

用ADO把查询表的数据导到Excel表上（记得引用ADO和 Microsoft Excell 11.0 Objec Library)，给一个例子的代码，供参考：
Private Sub Command1_Click()
On Error Resume Next
Dim oExcel As Object
Dim oBook As Object
Dim K As Integer
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add()
With oBook.Worksheets(1)
With oBook.Worksheets(1).PageSetup ’设定页面边距
.LeftMargin = 0.275590551181102
.RightMargin = 0.275590551181102
.TopMargin = 0.275590551181102
.BottomMargin = 0.275590551181102

.Columns("A:A").ColumnWidth = 6 '设定Excel各列的宽度
.Columns("B:B").ColumnWidth = 5.2
.Columns("C:C").ColumnWidth = 10.5
.Columns("D:D").ColumnWidth = 25.7
.Columns("E:E").ColumnWidth = 6
.Columns("F:F").ColumnWidth = 4
.Columns("G:G").ColumnWidth = 3
.Columns("H:H").ColumnWidth = 4
.Columns("I:I").ColumnWidth = 2
.Columns("J:J").ColumnWidth = 7
.Columns("K:K").ColumnWidth = 1
.Columns("L:L").ColumnWidth = 9.9
.Columns("M:M").ColumnWidth = 2.5
.Rows(8).RowHeight = 20.1 '设定Excel各行的高度
Rows(9).RowHeight = 18
.Cells(1, 4) = " 购 销 合 同"
.Cells(1, 4).Font.Size = 20
.Cells(1, 4).Font.Bold = True
.Cells(3, 8) = " 编号:"
.Cells(4, 8) = " 日期:"
.Cells(5, 8) = " 项目名称:"
.Cells(3, 1) = "卖方:"
.Cells(4, 1) = "地址:"

.Cells(6, 1) = " 买卖双方同意订立下列条款进行购销XXXXXXXXXXX"
.Cells(8, 1) = "数 量"
.Cells(8, 3) = " 货 号"
.Cells(8, 4) = " 品 名"
.Cells(8, 5) = " 颜 色"
.Cells(8, 6) = " 含 量"
.Cells(8, 8) = "箱 数"
.Cells(8, 10) = " 单 价"
.Cells(8, 12) = " 金 额"
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeTop).LineStyle = xlContinuous '刬线
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeTop).Weight = xlThick
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeBottom).Weight = xlThin
Dim CN As New ADODB.Connection
Dim ST As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
CN.Open "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\表名.mdb"

ST = "select * from 查询表"

rs.Open ST, CN, adOpenKeyset, adLockOptimistic
rs.MoveFirst

For K = 1 To rs.RecordCount '把查询表的内容写到Excel表中指定位置
.Cells(K + 8, 1) = rs("QTY")
.Cells(K + 8, 2) = rs("OUN")
.Cells(K + 8, 3) = rs("ITEM")
.Cells(K + 8, 4) = rs("CDES")
.Cells(K + 8, 5) = rs("COL")
.Cells(K + 8, 6) = rs("OPACK")
.Cells(K + 8, 7) = rs("OUN")
.Cells(K + 8, 8) = rs("CTN")
.Cells(K + 8, 10) = Left(rs("PRICE"), 6)
.Cells(K + 8, 10).NumberFormatLocal = "0.00_ "
.Cells(K + 8, 12) = Left(rs("AMT"), 9)
.Cells(K + 8, 12).NumberFormatLocal = "0.00_ "
rs.MoveNext
Next K
.Cells(K + 9, 10) = "合计:"
.Cells(K + 9, 13) = "元"
End With

rs.Close
Set rs = Nothing
Set Cn=nothing
oExcel.Visible = True
Set oBook = Nothing
Set oExcel = Nothing

End Sub本回答被提问者采纳