Access to Excel 2007 export annoyance

[jim wv]

Hello,

I've globally disabled the "Always create backup" general option for Excel 2007, but this only works when I start up from Excel. When I export to Excel 2007 within Access 2007, which I do very frequently, the resulting spreadsheet has this (quite annoying and unnecessary feature for me, anyway) turned back on. I can export to .xls without this issue, but prefer the more compact .xlsx format.

Anyone have a fix for this? thanks

[ajetrumpet]

is that feature in the excel options? if it is, record a macro while turning the option off through the interface and copy the code excel creates over to access.

[jim wv]

Ah, I see the CreateBackup:=False, I take it that I should create a macro to do this in access. Will try! Thanks

Sub macro1()

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\My Documents\Book1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Application.Goto Reference:="macro1"
End Sub

[bluebell]

Hi,

Finally this one works

here is the full code

Dim DB As Database
Dim XLApp, WB As Object

Set DB = CurrentDb

'YourFileName - Access Query Name or TableName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourFileName", "C:\TEST\TEST.xlsx"

Set XLApp = CreateObject("Excel.Application")
Set WB = XLApp.workbooks.Open("C:\TEST\TEST.xlsx")
XLApp.DisplayAlerts = False
WB.SaveAs FileName:="C:\Test\Test.xlsx", CreateBackup:=False
WB.Close SaveChanges:=True
XLApp.DisplayAlerts = True
DB.Close
Set DB = Nothing