Data changes between Access and Excel

[JBrooks]

Can anyone help?

I'm using the following code to create an Excel list from an Access query:

Dim objXL2 As Excel.Application
Dim objXL2Book As Workbook
Dim objXL2Sheet As Worksheet
Dim strPage1 As String ' Worksheet name
Dim strFileName As String ' Excel filename
Dim strQueryName As String ' Query to be exported

DoCmd.SetWarnings (False)

strFileName = "i:\planning\zp\MrpAssemblyLoading.xls"
strPage1 = "MRP Assembly Loading"
strQueryName = "qryMrpActivityForPivotTable"

DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, strFileName

' Assign Excel to an object variable (and open it)
' Valid for Access 2000
Set objXL2 = CreateObject("Excel.Application.8")

With objXL2
.Visible = True
.WindowState = xlMaximized
.DisplayAlerts = False
.Interactive = True

The query contains some date functions to take a date eg 14-07-2005 (I'm
from the UK!) and convert it to yyyyww format. In Access this returns eg
200528. However in Excel the same record shows 200530. I cannot figure this
out, and is causing problems with our manufacturing reports.

Any help gratefully received...

 

[John Nurick]

Without knowing what's in your query it's hard to help: please post the
SQL.

 

[JBrooks]

Thanks John, but I think I have solved the problem now. I added a line of
code to delete the existing spreadsheet file before creating the new one.
This is now giving me the expected results every time. It seems as if the
data transfer was getting corrupted because the Excel file Access was
creating already existed, and was not being overwritten, but somehow being
appended (ish).