Access to Excel error (every other run) (1 Viewer)

[gblack]

I keep running into this same issue and it's driving me nuts. I have an Excel workbook with a Pivot Table in it.

Basically all I want to do is push data from an MS Access query into an Excel worksheet and then refresh the pivot table. I need to do this all from an MS Access form. With a button OnClick event I first open the workbook and delete the worksheet used by a pivot table on another worksheet (i.e. I delete "Sheet1"), then close the workbook... using: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml... I post the new data (i.e. new "Sheet1"). I then open the workbook and refresh the pivot... pretty straightforward, but I keep running into this issue:

The code works fine on the first run, then breaks when I try to run it again, leaving the Excel spreadsheet open... Then when I end the event and close the spreadsheet manually, the button click will work again just fine, running the code and closing the workbook... then I try again and it breaks with the workbook open... over and over.

Private Sub btnManipulateFiles_Click()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim outputFileName As String


outputFileName = CreateObject("WScript.Shell").specialfolders("Desktop") & "\EXWC_COMMITMENTS_AND_OBLIGATIONS.xlsx"


Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Application.DisplayAlerts = False
Set xlWB = xlApp.Workbooks.Open(outputFileName)
Set xlSh = xlWB.Sheets("Sheet1")
xlSh.Activate

'Delete sheet to make room for new
xlSh.Delete

xlApp.Application.DisplayAlerts = True

xlWB.Save
xlWB.Close
xlApp.Quit
Set xlApp = Nothing

'Output 00100_OPN_COMMITMENT_AND_OBLIGATIONS into the specified Workbook as Sheet1.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "00100_OPN_COMMITMENT_AND_OBLIGATIONS", outputFileName, True, "Sheet1"

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(outputFileName)


'Refresh Pivot Table
Set xlSh = xlWB.Sheets("Obs and Commits by LIRN")
xlSh.Activate
Sheets("Obs and Commits by LIRN").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "SBH[All]", xlLabelOnly, _
    True
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1", Version _
    :=xlPivotTableVersion14)


xlWB.Save
xlWB.Close
xlApp.Quit
Set xlApp = Nothing

End Sub

Also, see screen shots of the error and where it breaks

Can someone please tell me why my code is breaking, every other run?

 

[Rx_]

I posted this exact problem about a year ago.
You are missing an xlApp. reference in one (or more) of your statements.
For some strange reason, the automation runs fine the first time. Then failes on the 2nd time.
If you control +Alt + Delete and look at Task Manager, you will see there is an orphaned Excel process still running.


Didn't verify it, but just for grins try this modification:
xlApp.ActiveSheet.PivotTables("PivotTable1").ChangePivotCache xlApp.ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1", Version _
:=xlPivotTableVersion14)

There might be more.
When ever there is an object, even as an argument, the Excel Object reference must be used.
Let us know if this fixes it and please post your final code. It will help others looking for this solution later.

 

[gblack]

Yes, thanks... I was just reading up on that. I changed this section of my code:

xlWB.Sheets("Obs and Commits by LIRN").Select
xlWB.ActiveSheet.PivotTables("PivotTable1").PivotSelect "SBH[All]", xlLabelOnly, _
    True
xlWB.ActiveSheet.PivotTables("PivotTable1").ChangePivotCache xlApp.ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1", Version _
    :=xlPivotTableVersion14)

It runs fine every time... Thanks for the quick response, when I posted a similar issue last week, I got zero reponses... so I will link that one to this and dub it SOLVED!

Thanks again!

 

[Rx_]

Sorry, I was out on last week playing AE Officer at the launch range.
My youth group did launch fully instrumented and video rockets up to around 17,000 feet altitude.
We set a new altitude record for a youth program that literally fabracated assembled and launched the rockets. There was no internet or cell phone where I just got back from. I will try to be more prompt next time. LOL

 

[JHB]

.. Thanks for the quick response, when I posted a similar issue last week, I got zero reponses...

It is not true that you got zero responses.
spikepl pointed it out in post #4, that you where missing the references.
And I did it in post #10.

 

[gblack]

JHB I am sorry you're bent out of shape by my remark,

The post in question was started by me 4/26. To wit, I got very little response.

You state that:

spikepl pointed it out in post #4, that you where missing the references.

In fact this is all spikepl said in post #4:

So why don't you do it on the next line also?

That didn't help me. If it had, I would have thanked spikepl, like I did Rx.

I was asked, by you, to post a stripped version of my database on 4/28 which I did on 4/28... no one responded after two days and I posted another "please help me" post on 4/30... still no responses.

4 days, after posting the Db, you responded.

I apologize for mistating that I got zero responses in the week (in truth I posted Monday and you responded to me Friday... my bad)... the truth is, after 4 straight days with no real help... I gave up on that thread, so I never saw your delayed response.

If it makes you feel better, I'll retract my statement and say this to Rx:

Rx,
Thanks for the quick response, when I posted a similar issue last week, there were no immediate helpful reposnes, yours was right on the money and easy for me to understand and now my code works... I will link that old post to this thread and dub it SOLVED!

Thanks so much,
Gary