I wanted to know if an already existing file with the same location and filename as that specified in the, gets either replaced or does a new one get created?Code:DoCmd.TransferSpreadsheet acExport, acSpreadsheetExcel12Xml, tableName, filename
I wanted to know if an already existing file with the same location and filename as that specified in the, gets either replaced or does a new one get created?Code:DoCmd.TransferSpreadsheet acExport, acSpreadsheetExcel12Xml, tableName, filename
It will automatically overwrite the file contents.
Be warned though it doesn't create a new file.
As an example you export to a sheet with 50 rows. You go and save some calculations (formulas) at the bottom of the data.
You save those changes.
Later you do another automated export - only 45 rows. It all works swimmingly. You open the sheet - and your formatting and formulas are still there at row 51 .
No problem.
Now you do another export. this time 70 rows.
You'll get an error.
When it reaches row 51 access will barf, as the cells already have info in them that doesn't match the formatting of the data being exported.
You'll be confused.
About 2 hours later you'll work it out if you are lucky.
So you have to handle this, and give users option to delete and overwrite the existing version, or keep their formatting and risk the error.
Also check the file isn't already open when attempting the transfer, another error is raised.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
If you do want a new file every time and not to overwrite any existing files, add a date/time stamp to your file name, i.e.
Code:filename = path & file name prefix & Format(Now(), "yyyymmdd_hhmmss") & ".xlsx"