transferspreadsheet #VALUE!

[ragtopcaddy via AccessMonster.com]

First of all, how do I look up "#VALUE!" in this group? I enclose it in
quotes and return hundreds of "Value" threads which I then have to work
through in vain to find the #VALUE! threads I'm looking for.

Now, to my specific problem. I have 2 queries which I transfer to an excel
workbook. They both return the same data for different dates. One
transferspreadsheet action returns all the records properly. The other one
returns thousands of #VALUE! cells sporadically throughout the sheet.

wkBk.Close True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataOld",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataOld").Activate
.ActiveSheet.Name = Format(dtOld, "yymmdd") & " Data"
.Close True
End With
'returns #VALUE! cells

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataNew",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataNew").Activate
.ActiveSheet.Name = Format(dtNew, "yymmdd") & " Data"
.Sheets("QTree and Owner Changes").Activate
.Close True
End With
'returns accurate cells

It's a mystery to me!

 

[ragtopcaddy via AccessMonster.com]

One thing I've determined:

Whichever transfer runs first produces the #VALUE! cells. If I reverse them
(new and then old):

wkBk.Close True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataNew",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataNew").Activate
.ActiveSheet.Name = Format(dtNew, "yymmdd") & " Data"
.Sheets("QTree and Owner Changes").Activate
.Close True
End With

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataOld",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataOld").Activate
.ActiveSheet.Name = Format(dtOld, "yymmdd") & " Data"
.Close True
End With

Then the new data has the #VALUE! cells and the old data reads properly. So,
whichever one runs first has the #VALUE! cells. Therefore it has nothing to
do with the queries or the data in the db.

 

[ragtopcaddy via AccessMonster.com]

If I do only one of the transfers, it also has the #VALUE! cells. Also,
curiously, all but the 1st column header are also #VALUE!.

 

[ragtopcaddy via AccessMonster.com]

The following code, although not anything like what I would call a "solution",
works around whatever this difficulty is:

wkBk.Close True
Set wkBk = Nothing
xlObj.Quit

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataNew",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataNew").Name = Format(dtNew, "yymmdd") & " Data"
.Close True
End With
Set wkBk = Nothing
xlObj.Quit

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataOld",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
.Sheets("qryDataOld").Name = Format(dtOld, "yymmdd") & " Data"
.Sheets(Format(dtNew, "yymmdd") & " Data").Delete
.Close True
End With
Set wkBk = Nothing
xlObj.Quit

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDataNew",
strNewWkBk
Set wkBk = xlObj.Workbooks.Open(strNewWkBk)
With wkBk
' .Sheets("qryDataNew").Name = Format(dtNew, "yymmdd") & " Data"
***************************************************************************************
Before I commented this line out, the debugger was stopping on this line. It
couldn't find the sheet.
The code just above it successfully found its sheet and renamed it.
Even though the sheet I expected to be named "qryDataNew" apparently didn't
exist, a sheet named Format(dtNew, "yymmdd") & " Data", which should have
been deleted, does and no longer shows the #VALUE! cells
***************************************************************************************
.Sheets("QTree and Owner Changes").Activate
.Close True
End With