I have code that creates a new workbook and saves it under a new name, and
transfers the results of a query and formats the sheet and renames it. Then I
have 2 more transferspreadsheet commands that are supposed to transfer to 2
new worksheets and rename them. Unfortunately, it has the bizarre behavior of
somehow writing over the existing spreadsheet, adding another sheet named
after the
query being transferred, but with garbled data in this new sheet, and now,
ruined data in the existing sheet. Has anyone ever come across this behavior
before? Here's my code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryQTreeAndAppChanges", strNewWkBk

If ExcelIsRunning Then
Set xlObj = GetObject(, "Excel.Application")
Else
Set xlObj = CreateObject("Excel.Application")
End If

Set wkBk = xlObj.Workbooks.Open(strNewWkBk)

With wkBk
.ActiveSheet.Name = "QTree and Owner Changes"
.Save
End With

************************************************** **************
(a lot of formatting stuff successfully executes)
The following ruins the 1st sheet:
************************************************** **************

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

Any suggestions would be appreciated,

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200804/1