Transfer spreadsheet functions - unexpainable results...

[is49460]

Good afternoon!

I use transfer spreadsheet function the export data from one of the table into the excel spreat sheet. I use the following code:
DoCmd.TransferSpreadsheet acExport, 8, "qry Rejected Items", Path & "Output Files\Rejected Items.xls", True, "NewData"

Please note that the output should be placed in the "NewData" tab.

In the same excel file the output was made to, i have function that is excecuted on open:

Sub Auto_Open()
'msgbox "stop before running code"
Call CheckNewData
ActiveWorkbook.Names.Add "PivotData", "=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(D ata!$1:$1))"
Sheets("Pivot").PivotTables("PivotTable1").PivotCa che.Refresh
Sheets("Pivot").Activate
ActiveWorkbook.Save
End Sub

Public Sub CheckNewData()
On Error GoTo NoNewData
If Len(Sheets("NewData").Name) > 0 Then
'delete old
Application.DisplayAlerts = False
Worksheets("Data").Delete
'rename new
Worksheets("NewData").Name = "Data"
Application.DisplayAlerts = True
Else
'error no NewData
MsgBox "New Data Failed!" & Chr(13) & Chr(13) & "Contact Provider", , "Error"
Application.Quit
End If

NoNewData:
End Sub


The only thing all that should do is to check if there is a new tab called "NewData". if that's the case it should
1. delete old tab named "Data"
2. rename "NewData" tab to "Data" tab
3. define new name range (to be used in pivot table as a source)
4. refresh pivot table.


with all that i was able to export new data from access into the report in excel.

Looks all good so far, exept seems that the excel code got imbedded into the excel file. Seems like there is some code being excecuted before it ever gets to the auto_open module....

so if I put msgbox as my very first line in auto_open excel function, it will show me the message box exept all the data is already in place, without ever executing the code... I'll never see "NewData" tab, all the new data is in "Data" tab already!!!

How is that possible??? already spend hours doing the research with absolutely no luck.

I hope all that make sense.

I'll gladly send 2 sample files to show what happens (1 mdb file and 1 xls file).

Any help would be greatly appreciated.

[nico5038]

Why not disable the auto_open code (or even remove it) entirely ?

Nic;o)