Export Access query to existing sheet in Excel. VBA command always creates new sheets.

I have an Access query that I want to export regularly to a specific sheet in Excel.  This sheet has a chart that is dependent upon the cells' data.  

I am using the following command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query Name", "C:\test\test.xlsx", True, "Sheet with Chart"

However, it exports to Sheet "_Sheet with Chart".

It always creates a new sheet, named with an underscore immediately preceding whatever sheet name I choose.  In some instances, it will put a '1' immediately after a sheet name - for example, if I use "Sheet1" in the command, the resulting action is a new sheet with "Sheet11" showing the newly exported data.  

Is there a way to have it export to an existing sheet that I name?  Is the command different?  Is there a setting in Excel that needs to be changed?  Or is this not even possible?  Any help is appreciated.
Who is Participating?
Bill PrewCommented:
I'm 99% certain TransferSpreadsheet does not allow sending the data to an existing sheet, only to a new one, sorry.

One way to work around this is to do the TransferSpreadsheet to a new sheet, and then use Excel automation to copy the data on the new sheet to the existing sheet(s) where you want it, and then delete the new sheet.

ste5anSenior DeveloperCommented:
E.g. using late binding, something like this should do it:

Option Compare Database
Option Explicit

Private Function CopyData(ASheet As Object, ADataSource As DAO.Recordset) As Boolean
  Dim Field As DAO.Field
  Dim Count As Long
  If ADataSource.RecordCount <> 0 Then
    Count = 0
    For Each Field In ADataSource.Fields
      ASheet.Range("A1").Offset(0, Count) = Field.Name
      Count = Count + 1
    Next Field

    ASheet.Range("A2").CopyFromRecordset ADataSource
  End If

End Function

Public Function ReportOpenExistingExcel(ADataSource As String, AFileName As String) As Boolean

  On Local Error GoTo LocalError

  Dim Excel As Object 'Excel.Application
  Dim Workbook As Object 'Excel.Workbook
  Dim Field As DAO.Field
  Dim DataSource As DAO.Recordset

  Screen.MousePointer = ccHourglass
  Set DataSource = CurrentDbC.OpenRecordset(ADataSource, dbOpenSnapshot)
  Set Excel = CreateObject("Excel.Application")
  Set Workbook = Excel.Workbooks.Open(AFileName)
  CopyData Workbook.Sheets(1), DataSource ' Here you need to specficy the correct sheet.
  Set Field = Nothing
  Excel.DisplayAlerts = False
  Set Workbook = Nothing
  Excel.DisplayAlerts = True
  Excel.Visible = True
  Set Excel = Nothing
  ReportOpenExistingExcel = True
  Screen.MousePointer = ccDefault
  Exit Function

  Set DataSource = Nothing
  If Not Excel Is Nothing Then
    Excel.DisplayAlerts = True
    Excel.Visible = True
  End If
  Set Workbook = Nothing
  Set Excel = Nothing
  Screen.MousePointer = ccDefault

End Function

Open in new window

Rob HensonFinance AnalystCommented:
Alternatively, create a Connection to the Access Query from the Data tab in Excel and then refresh the connection. This will do a Pull (Import) from Excel rather than a push (Export) from Access.

The result of the connection will stay on the same sheet.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fabrice LambertFabrice LambertCommented:
I'm not sure that copying fields headers is needed in that case, charts rarely have dynamic columns as data source.

Additional comments:
Your function CopyData always return false, and the counter is never used.

In your ReportOpenExistingExcel function, in my opinions, saving, closing the workbook and quitting Excel will be better than making it visible.
Same goes for the error Handler, better quit Excel properly than poping it on user's face.
And the field variable doesn't appear to have any purpose.
rko9911Author Commented:
I am sure the other solutions are great.   The first suggestion is the first one I tried and it worked perfectly.  Thank you everyone for your help!!
ste5anSenior DeveloperCommented:
@Fabrice: They basic processing is part of the normal workflow in most of my Access applications. Cause it leads to lesser saved Excel workbooks, which is good for the user and for GDPR.

The "flaws" are just artefacts..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.