DoCmd.TransferSpreadsheet not consistently working

[imdl]

Hello,

I'm using the code below to transfer a query to a specific Excel file. It works, and when I click the command button again, it overwrites the existing Excel sheet. However, after several times of overwriting the Excel sheet, it stops working or it does anymore overwrite.

Is there an expiration for the code? I mean, does it work for only a maximum number of times? Say it can overwrite only up to 5 times. After which, the code won't work anymore.

DoCmd.TransferSpreadsheet acExport, 10, "queryname", "destinationExcelFile", False, ""

imdl

 

[DCrake]

Never heard of that before but you could try the following

Use Dir(DestinationFile) to see if file exists
If exists then use Kill DestinationFile to delete it
Then use TransferSpreadsheet as normal.

 

[winshent]

are you sure its overwritting and not appending to the existing spreadsheet?

 

[aman]

Definitely it overwrites the spreadsheet and doesn't append. You need to add the following atatements before transferspeadsheet statement.

If DCount("Name", "MSysobjects", "Name='qrytemp' and type=5") > 0 Then
DoCmd.DeleteObject acQuery, "qrytemp"
End If
Set qdf = CurrentDb.CreateQueryDef("qrytemp", strsql)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "query name", "destination name", False

I hope it will help you.

 

[imdl]

thanks guys, for your replies.

my understanding is that append means "add," right?

it should overwrite the sheet and should not delete the file. i have actually two sheets in the Excel file. the first sheet contains formulas based on the second sheet being overwritten using the code.

further thoughts please?

imdl

 

[DCrake]

Usually TransferSpreadsheet creates a new workbook. Are you getting messages that the workbook already exists? If you want to insert data into an existing workbook on an existing worksheet you cannot use this method.