java在excel模块中新增sheet,如何使用VBA在Excel中添加一个新的工作表？

I am new to VBA coding. I want to populate an excel sheet with different data. The requirement is to pull data into separate sheets of the same Excel. I need to use the same type of code below to maintain the integrity of the entire code.

outfile = "c:\" & Format(Date, "yyyymmdd") & "activedirectorydata.xls"

DoCmd.OutputTo acOutputQuery, "**MoActiveDirectoryData**", acFormatXLS, outfile, False

I am able to get data from a view/query MoActiveDirectoryData into one sheet. How should I go about getting data from another view/query into a different sheet of the same excel?

解决方案

If you use DoCmd.TransferSpreadsheet, sheets will be created in the same file as long as the table or query that you are exporting has a different name than the sheets that already exist.

outfile = "c:\" & Format(Date, "yyyymmdd") & "activedirectorydata.xls"

''Sheet is called Query1

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", outfile

''New sheet called Query2

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query2", outfile