vba 从 Access 导出到 Excel 到现有工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14562209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export from Access to Excel to an existing worksheet
提问by user2018125
Example: I have the excel file test.xls and in it the sheet1, sheet2, sheet3, sheet4 with some information in it. I want to export the result of 4 queries to those sheets, but I want to add the result after the last row with data and not to overwrite it.
示例：我有一个 excel 文件 test.xls，其中有 sheet1、sheet2、sheet3、sheet4，其中包含一些信息。我想将 4 个查询的结果导出到这些工作表，但我想在最后一行数据之后添加结果而不是覆盖它。
回答by Grant
I worked on something similar recently. In Excel I used VBA.
我最近在做类似的事情。在 Excel 中，我使用了 VBA。
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
Dim StartDate As Date, EndDate As Date, ModStartDate As Date, ModEndDate As Date
'Modify the Start date because Maint 24hr cycle is 930 - 930 not 12 - 12
ModStartDate = StartDate - 1
''Access database
strFile = "S:\IT\Databases\Main_BE.mdb"
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
'Get the info from Access
strSQL = "SELECT * FROM Work_Orders " _
& "WHERE Repair_Start_Date >= #" & ModStartDate & "# " _
& "AND Repair_Start_Date <= #" & EndDate & "# " _
& "ORDER BY Repair_Start_Date, Repair_Start_Time"
rs.Open strSQL, cn
'Paste the SQL query at A10 Sheet3
Sheet3.Cells(10, 1).CopyFromRecordset rs
''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
You would just need to modify the SQL statement, and then to add a new row you could use a variable to count the number of existing rows with:
您只需要修改 SQL 语句，然后添加一个新行，您可以使用变量来计算现有行的数量：
"=Counta(some range:some range)"
Then just add 1 to the variable for the next row.
然后只需将 1 添加到下一行的变量。
回答by user2018125
Thanks everybody! I could solve it using :
谢谢大家！我可以使用以下方法解决它：
Private Sub Command0_Click()
Dim rstName As Recordset
Set rstName = CurrentDb.OpenRecordset("query1")
Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object
Set objApp = CreateObject("Excel.Application")
Set objMyWorkbook = objApp.Workbooks.Open("c:/exportarexcell/teste.xls")
Set objMySheet = objMyWorkbook.Worksheets("FolhaTeste")
Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows.Count + 1, 1)
With objMyRange
rstName.MoveFirst 'Rewind to the first record
.Clear
.CopyFromRecordset rstName
End With
End Sub
