Create an additional worksheet unintentionally at exporting

[FuzMic]

Hi guys

i use following to export a table eg Tb01 to a Excel eg X01.xls

docmd.transferSpreadsheet acExport, 8, Tb01, X01, true

by doing so either
1. if there is no worksheet by the name of Tb01, it will create one & export content to it
2 if there is the folder Tb01 it will just replace it's contents

above is what i need

however if the x01.xls is change or whatever it create another worksheet Tb011 & not replace the old contents in Tb01 or create a new Tb01

Any advice to understand & satisfy my need

 

[theDBguy]

How about trying it like this?

docmd.transferSpreadsheet acExport, 8, Tb01, X01, true, Tb01

 

[FuzMic]

DB guy your €£$ worthed of placing a range at the end did not work. It is not range but a table in the database

I think range is mainly for import.

 

[theDBguy]

DB guy your €£$ worthed of placing a range at the end did not work. It is not range but a table in the database

I think range is mainly for import.

Sorry, I just thought it was worth a try.

 

[Gasman]

I cannot get that to happen.?

I copied your code and replaced table name and filename with my own. No such Excel xls file existed initially.
Ran the code, file created with table data and a sheet the name of the table.
Opened Excel workbook, amended data and saved.
Ran code again, same data on same sheet and my changes were lost.?

Hi guys

i use following to export a table eg Tb01 to a Excel eg X01.xls

docmd.transferSpreadsheet acExport, 8, Tb01, X01, true

by doing so either
1. if there is no worksheet by the name of Tb01, it will create one & export content to it
2 if there is the folder Tb01 it will just replace it's contents

above is what i need

however if the x01.xls is change or whatever it create another worksheet Tb011 & not replace the old contents in Tb01 or create a new Tb01

Any advice to understand & satisfy my need

 

[FuzMic]

You may have to identity path of x01.xls in the code

Ii is reproducible in all instances

 

[FuzMic]

may be the issue is behind the scene the tb01 worksheet still exist even though there is the nothing apparent or it may be hidden even when it don't show in format>unhide

 

[Gasman]

You may have to identity path of x01.xls in the code

Ii is reproducible in all instances

Sub TestXLSExport()
DoCmd.TransferSpreadsheet acExport, 8, "Transactions", "C:\Temp\SSAFA Test Transactions.xls", True
End Sub

Is your workbook shared at all.?

 

[FuzMic]

Not shared but it should not matter
I will prefer to have name of xls simple without space
So you have a table named transactions in your current project

 

[Gasman]

Not shared but it should not matter
I will prefer to have name of xls simple without space
So you have a table named transactions in your current project

Yes, I just used a table that was available. The excel file did not exist initially and will be deleted after this. Spaces ot not spaces, your choice.

I was just wondering if the file was shared that might cause the data to go to another sheet.? I cannot test that as I am a single user on my laptop.