?
Solved

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

Posted on 2015-02-16
4
Medium Priority
?
182 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:durick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 40612295
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
 
LVL 1

Expert Comment

by:IT Project Mgr
ID: 40614061
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
 

Author Closing Comment

by:durick
ID: 40618985
yes it works
0
 
LVL 85
ID: 40619082
If it works, then why did you award a B grade?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question