I am running some code that exports data based on a query to an Excel file (.xlsx) executed by a button. Previously I was exporting to a .xls file but since I have changed it to an Excel 2007 format I noticed that the file is not being overwritten. The crazy this is that when I navigate to the directory where the file is going the modified date/time is updated and the size of the file increases but when I open it I see the exact same data in there that I exported on the first execution of the code. Below is the code that I am running.
Dont know if this matters but I am exporting the file to a Sharepoint site although I stating the fully resolved path. Any assistance with this would be much appreciated.
Public Function ExportFormData()
On Error GoTo ErrorHandler
Dim strDashboardPath As String
Dim strQryVacRecords As String
strDashboardPath = "\\teamspace\sites\RD_TEAM\ExcelExports\"
strQryVacRecords = strDashboardPath & "VAC_RECORDS_2014.xlsx"
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="QRY_VAC_RECORDS", Filename:=strQryVacRecords, HasFieldNames:=True
MsgBox "Error No: " & Err.Number _
& ";Description: " & Err.Description