Solved

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

Posted on 2015-02-16
4
166 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
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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:itprojks99
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 84
ID: 40619082
If it works, then why did you award a B grade?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now