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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.