Results 1 to 2 of 2

Exporting Queries From Access DB to Excel Workbook - Exclude Default Worksheet..?

  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    74

    Question Exporting Queries From Access DB to Excel Workbook - Exclude Default Worksheet..?

    Hi Folks,



    I have a form in my database which is designed to output 3 queries onto an .xlsx file. The idea is to output each query onto a separate worksheet in that .xlsx workbook.
    This is all working as designed, but I'm trying to figure out how to exclude (or delete) the default "Sheet1" worksheet. Below is a snippet of the code being used. As far as exporting the 3 queries, I'm using the DoCmd.TransferSpreadsheet acExport method (x3 - once for each of the 3 queries).
    So once the code finishes, it creates the .xlsx file as intended, and outputs the 3 queries onto 3 separate worksheets - but then the workbook still has a blank "Sheet1" which I'd like to exclude. Thoughts?


    Code:
    Call Output_To_Excel(strPath, strFileName)

    This calls the below function which creates a placeholder Excel workbook on a specified network path. The file path and file name is passed in from the Class Module on the form.


    Code:
    Function Output_To_Excel(strPath As String, strFileName As String)
    
    Dim XL As Excel.Application
    Dim WB As Excel.Workbook
    Dim WKS As Excel.Worksheet
    
    
    Set XL = New Excel.Application
    
    
    XL.Visible = False
    
    
    Set WB = XL.Workbooks.Add
    
    
    Set WKS = WB.Worksheets(1)
    
    
    WB.SaveAs FileName:=strPath & strFileName, _
    FileFormat:=xlOpenXMLWorkbook
     
    WB.Close SaveChanges:=False
    
    
    Set WB = Nothing
    XL.Quit
    
    
    Set XL = Nothing
    
    
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    Maybe:

    WB.Sheets("Sheet1").Delete
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Worksheet within Excel Workbook
    By rpkfish in forum Macros
    Replies: 4
    Last Post: 05-06-2016, 03:02 PM
  2. Replies: 1
    Last Post: 04-05-2012, 01:08 PM
  3. Replies: 1
    Last Post: 11-21-2010, 10:26 PM
  4. Exporting Queries to Excel Sheets, 1 Student per Workbook
    By StudentTeacher in forum Programming
    Replies: 8
    Last Post: 09-02-2010, 11:48 AM
  5. Exporting Query Results to an existing Excel Workbook
    By Dnphm in forum Import/Export Data
    Replies: 3
    Last Post: 07-13-2010, 11:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums