Export Spreadhseet

[wsaccess]

Hi,

I am trying to run a query, and export the data to an existing spreadsheet. I would like to replace the existing data in the spreadsheet with the new export. The reason being, there are other sheets which never change.
Is this possible with Docmd.TransferSpreadsheet?
I used this statement:

DoCmd.TransferSpreadsheet acExport, , strQryName, strPath & strFileName & ".xlsx!SourceData"

This gives an error saying the file is read only.

Right now, I am opening the spreadsheet, clearing the contents of Sheet: SourceData, replacing it with Query results.

I would like to know if there is an easier option.

Thanks!

 

[Ranman256]

If you have a workbook with many sheets and want to replace 1 but keep the others, then you must take control of Excel, clear the sheet, THEN run the TransferSpreadsheet .
If not the transferspeadsheet will overwrite SOME of the data in the sheet, and may not get it all. Hence the clear Sheet.

'you MUST put Excel in the program REFERENCES, in VBE menu: TOOLS , REFERENCES

vFile = strPath & strFileName & ".xlsx"
vSheet = "SourceData"
ClearSheet vFile, vSheet
   'NOW export the data
DoCmd.TransferSpreadsheet acExport, , strQryName, vFile, vSheet

Sub ClearXLsheet(pvFile, pvSheet)
Dim xl As Excel.Application
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open pvFile
    .ActiveWorkbook.Sheets(pvSheet).Select
    .Cells.Select
    .Selection.ClearContents
    .Range("A1").Select
    
    .ActiveWorkbook.Save
    .Quit
End With
Set xl = Nothing
End Sub

 

[wsaccess]

Thanks! It worked....