Export to Excel Not Overwriting

[EHittner]

I have an export to Excel function as part of a macro. Each time it exports, it's supposed to overwrite the file. When I run this function in the Manage Data Tasks to test it, it prompts me to overwrite the existing file, then informs me that it "Successfully exported 'file_name'." However the destination file remained untouched and not updated.

I've tried deleting and recreating the export from both the top menu and from the right click menu. When I'm in the initial session after creation of the export, it works fine. But... when I leave it to the macro to execute each day, it's not overwriting. And... when I go back in on a different session, and re-run the function from the Saved Exports, Manage Data Tasks menu, I encounter this problem again.



I've searched the internet, and have found people who've had this same exact issue. But there's no sign of any solution available.

Anybody ever resolved this?

[Gicu]

Use VBA with Kill file\ Docmd.TransferSpreadsheet instead of macro:
https://learn.microsoft.com/en-us/of...kill-statement
https://learn.microsoft.com/en-us/of...ferspreadsheet

Cheers,

[ranman256]

note: you export a new file with 100 records
then if you export the same data to the same file, but with only 50 records, then the spreadsheet will still have 100 records.
The dumb thing only overwrites the 50, it does not start fresh, so now you have a mixed bag of records.

If your data fluxuates, its best to delete the previous file, then export.

[Mike505]

Whenever I have to do something like this I append the date & time to the filename. I then tell users that this is a feature. I'm doing them a favor by preventing an older version that may still be useful from being deleted. My users typically say "Thanks for taking the extra time to do that".

[EHittner]

Vlad... This is exactly what I needed! This puts to bed an issue I've been dealing with... spending so much time on, for a very long time. It preserves a completely automated process. Saving to a different file name with a naming convention would not have worked -- as this exported file is picked up by a Databricks command that imports it, updates the data, and delivers it back to Access. I tested this solution several times by monitoring the network location where the file is being saved. Worked great! Very much appreciated!

[Gicu]

[ArviLaanemets]

As you overwrite this Excel file/report always, it looks like you have this file in your LAN or on your computer. So why you need this hassle with overwriting the Excel table at all? Simply create a saved query in Access database which returns data you need in Excel, and in Excel you create an ODBC query which reads this saved Access query.

You can set the Excel query to be refreshed on open, and also users of this Excel report always can refresh the query manually - i.e. refreshing data is feature of Excel report, and Excel report user always gets the report with data from latest save of your Access DB.