Trouble saving excel and then doing transfer spreadsheet

[ACTHelp]

Hi Everyone,

Here's the high level view of what I'm doing.

Opening a PowerPlay ppx file
Saving the data into an excel file
opening that excel file and doing some cleanup
- deleting unneeded headers, etc
save and close excel file
transfer spreadsheet

The transfer spreadsheet fails.

"The microsoft Jet database engine could not find the object 'c:/data/Central Area Summary MTTR by Instrument.xls'


Here's the code:

Set rst = New ADODB.Recordset
' AllOpenDataAccessPages
'get Power Play Reports
strSQL = "select * from [tblPowerPlayInputFiles]"
rst.Open strSQL, CurrentProject.Connection

While Not rst.EOF

ImageDisplay
gfrmStatus.Caption = " Creating Excel Spreadsheets from PowerPlay"
gfrmStatus.updateText "Creating Excel Spreadsheets from PowerPlay" ''
gCurSection = gCurSection + 1: gfrmStatus.updatePercentage gCurSection / gNumSections, Format(gCurSection / gNumSections, "###%")
gstrFile_type = rst("File_type")
'get application object
Set objPPApp = CreateObject("CognosPowerPlay.Application")
strpprptName = gstrPPFilePath & rst("file_name") & ".ppx"
'open PowerPlay Rpt
Set objPPRpt = objPPApp.Reports.Open(strpprptName)
If Err <> 0 Then
MsgBox "Unable to Open PowerPlay report -" & strpprptName, vbOKOnly
End If

'save as excel file
strExcelName = "C:\data\" & rst("file_name") & ".xls"
objPPRpt.SaveAs strExcelName, 4

' Set objXLApp = New Excel.Application
Set objXLApp = New Excel.Application



objXLApp.Workbooks.Open (strExcelName)
Set objXLWB = objXLApp.ActiveWorkbook
objXLApp.Visible = True

ImportExcelObject

objXLWB.SaveAs strExcelName, 4

DoCmd.TransferSpreadsheet acExport, 8, strExcelName, _
strExcelName, True, ""


objXLWB.Application.Quit

objXLApp.Quit
Set XLRange = Nothing
Set objXLWB = Nothing
Set objXLWS = Nothing
Set objXLApp = Nothing


rst.MoveNext
Wend
'close report

 

[ACTHelp]

Problem has been resolved!