Results 1 to 2 of 2
  1. #1
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    81

    Can I use DoCmd.TransferSpreadsheet acExport to replace content of a sheet in a workbook?

    My best attempt so far has been to use DoCmd.TransferSpreadsheet acExport in order to export four separate queries to an excel workbook. These four sheets feed a fifth sheet that has formulas built into it. However, I need the queries to replace the content last export on each sheet, not create new sheets. I do not want to create a linked file as I need to be able to share the contents of the excel file with others, some of which probably do not have Access.

    Here is the code I have so far: Any ideas?

    Private Sub Command78_Click()
    Dim strFullyQualifiedFilename As String
    strFullyQualifiedFilename = "C:\[Destination String].xlsx"
    DoCmd.TransferSpreadsheet acExport, , "qryExpenseActual_ExcelExport", strFullyQualifiedFilename


    DoCmd.TransferSpreadsheet acExport, , "qryExpenseBudget_ExcelExport", strFullyQualifiedFilename
    DoCmd.TransferSpreadsheet acExport, , "qryIncomeActual_ExcelExport", strFullyQualifiedFilename
    DoCmd.TransferSpreadsheet acExport, , "qryIncomeBudget_ExcelExport", strFullyQualifiedFilename
    DoCmd.TransferSpreadsheet acExport, , "lktEventsQ_ExcelExport_EventFiltered", strFullyQualifiedFilename
    DoCmd.TransferSpreadsheet acExport, , "qryPercentageofIncome_ExcelExport_EventFilter ed", strFullyQualifiedFilename
    MsgBox "File has been exported to " & strFullyQualifiedFilename








    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, sQry, sFile, True, vTabName

    now, if you have existing data on that tab, the it may not erase it all.
    if it already had 100 recs,
    and you transfer 50, the result will still have 100.
    your 50,
    and the old remaining 50. (since you didnt write over them)

    youd have to delet the old file, before you transfer.
    If you cant delete the workbook, then you will need a VB solution to extract into a working workbook.

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

Similar Threads

  1. DoCmd.TransferSpreadsheet acExport Question
    By MTSPEER in forum Programming
    Replies: 5
    Last Post: 09-08-2016, 01:11 PM
  2. How do I use DoCmd.TransferSpreadsheet acExport
    By morerockin in forum Access
    Replies: 6
    Last Post: 09-24-2015, 03:28 PM
  3. Replies: 1
    Last Post: 08-08-2013, 03:54 PM
  4. Replies: 1
    Last Post: 01-22-2013, 03:42 PM
  5. Replies: 4
    Last Post: 11-09-2011, 08:40 AM

Posting Permissions

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