Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 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

16 Experts available now in Live!

Get 1:1 Help Now