From Access vba I need to create an excel workbook having two sheets

DoCmd.OutputTo acOutputForm, stDocName, acFormatXLS, ExcelFileName, True
This will create an Excel file with a single spreadsheet.
changing the stDocName and Running this command again only overwrites the first sheet.
How can I create two sheets. in the same workbook?
durickAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Use TransferSpreadsheet instead, and you can define the worksheet where you want the export to land:

DoCmd.TransferSpreadsheet acExport, 3, stDocName, ExcelFileName, True, "Sheet 2"

More on TransferSpreadsheet: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx
0
 
IT Project MgrVBA/SQL developerCommented:
Here's another option - this function will create a new workbook, and export the contents of your table to a specified worksheet, and also allows for some formatting of the sheet.  Note, the value of "myFileName" needs to contain the entire path including the filename.

Function ExportTable2Excel(myTable As String, myFileName As String, mySheet As String, myWSName As String)
On Error GoTo HandleErr

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim intCount As Integer
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
    
    Set db = CurrentDb()
    Set rst1 = db.OpenRecordset(myTable, dbOpenSnapshot)
    
    'check if the spreadsheet already exists, if so, delete it.
    If FileExists(myFileName) Then
        SetAttr myFileName, vbNormal
        Kill myFileName
    End If
    
    'Open a new Excel workbook
    Set ApXL = CreateObject("Excel.Application")
    
    Set xlWBk = ApXL.Workbooks.Add
    xlWBk.Application.Visible = True
    xlWBk.Parent.Windows(1).Visible = True
        
    Set xlWSh = xlWBk.Worksheets(mySheet) 'Where the initial sheet name is "Sheet1", "Sheet2", etc.
    xlWSh.Activate
    xlWSh.Select
    xlWSh.Name = myWSName
    
    xlWSh.Range("A1").Select
    Do Until intCount = rst1.Fields.Count
        ApXL.ActiveCell = rst1.Fields(intCount).Name
        ApXL.ActiveCell.Offset(0, 1).Select
        intCount = intCount + 1
    Loop
    
    rst1.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst1
                  
    xlWSh.Range("1:1").Select

    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    
    xlWBk.SaveAs FileName:=myFileName
    xlWBk.Close
    
    rst1.Close
    Set rst1 = Nothing
    

ExitHere:
Exit Function

HandleErr:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Function

Open in new window


It calls this function to see if the file already exists first:
Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Open in new window

0
 
durickAuthor Commented:
yes it works
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If it works, then why did you award a B grade?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.