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
Solved

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

Posted on 2015-02-16
4
177 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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