How to add a new sheet in excel using VBA?

Question

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?

Fionnuala · Accepted Answer · 2012-07-12 08:07:49Z

2

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

edited Jul 12, 2012 at 8:07

answered Jul 11, 2012 at 14:22

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add a new sheet in excel using VBA?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related