Determine the query that feeds the form

[vbaInet]

Where did you run it from? You can't run it from the Immediate window like that.

 

[Victor70]

Sure. It would not let me to do that.
I ran it from a separate module I created.

 

[vbaInet]

Let me see the entire code you used and tell me where it failed.

 

[Victor70]

Thank you for your time. I renamed the form to olePivotTable by going to Sheet property / Name.

Sub PivotSource ()

Dim xlWkBk As Object Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)].olePivotTable.Action = acOLEActivate          
Set xlWkBk = Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)].olePivotTable.Object.Application.Workbooks(1)      
MsgBox xlWkBk.PivotCaches(1).CommandText     
Debug.Print xlWkBk.PivotCaches(1).CommandText

End Sub

 

[vbaInet]

Thank you for your time. I renamed the form to olePivotTable by going to Sheet property / Name.

I didn't say that. I said, rename the object, i.e. the OLE object not the form. Put the old form name back as it is.

Use this code:

Sub PivotSource ()

Dim xlWkBk As Object, frm As Form

Set frm = Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)]

Set xlWkBk = frm.olePivotTable.Object.Application.Workbooks(1)
MsgBox xlWkBk.PivotCaches(1).CommandText     
Debug.Print xlWkBk.PivotCaches(1).CommandText

End Sub

The form can be in Design View now.

Also, where did you place the code? In a Standard Module? And how did you call it?

 

[Victor70]

As per renaming, I hope, I did what you meant - right click the form in the Design View, select Properties, change the name of Unbound Object Frame back to PivotTable from olePivotTable. Is that it???

The code is in the standard module named Module1.

Now I am getting run-time error '2465' - application-defined or object-defined error.
The code stumbles at this line:

Set xlWkBk = frm.olePivotTable.Object.Application.Workbooks(1)

 

[vbaInet]

This really should be straightforward and we're just going round in circles. I tried this first time in the sample db and it worked.

Look at your previous post, you said that you renamed the FORM to olePivotTable whereas I said that you should rename the OLE object to olePivotTable. And now you're saying that you renamed the ole object back to PivotTable.

Are you confused as to what a FORM and an OLE OBJECT is? If you are just ask. The form is the object that you open, the ole object is the control that displays the Excel sheet. That ole object is that square that is inside your form. Two different things.

Please reread my post #45.

 

[Victor70]

Being stupid is really bad.

I am attaching the screenshot of where I changed the name.

Now I am getting "Subscript out of Range" error after I put that name in and ran the code again.

 

[vbaInet]

On what code line? When you mention that you get an error it's necessary to mention what code line it is.

 

[Victor70]

My bad.

This is where I get the error.

MsgBox xlWkBk.PivotCaches(1).CommandText

 

[vbaInet]

Ok, I see your problem. You probably initially got an Application defined error and decided to open Excel to stop that error so you missed out the authorisation step.

Follow these exact steps:
1. Put this code in a Standard Module:

Sub GetPivotSource()
    Dim xlApp As Object
    Dim xlWkBk As Object
    Dim frm As Form

    Set frm = Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)]
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    Set xlApp = frm.olePivotTable.Object.Application
    Set xlWkBk = xlApp.Workbooks(1)
    
    MsgBox xlWkBk.PivotCaches(1).CommandText
    Debug.Print xlWkBk.PivotCaches(1).CommandText
End Sub

CreateObject and Visible will ensure that an instance of Excel is always open so you can see the authorisation dialog box.

2. Open your form in Design View
3. Close Excel - all running instances
4. Run the code and Excel should pop-up with a dialog box asking your permission to run code. Click Open > then Enable (in a second dialog box)
4. Go back to Access and you should see the Msgbox (making sure that Excel is open).

 

[Victor70]

Wow. That's amazing. Thank you very much.
I ran the code on other forms by changing the name of the OLE object and referring to different form in the code. Works like magic.
My only concern (since I am not that sophisticated) is whether changing the name of the OLE object affects in any way other "features" of the form. In other words do I have to change the name back after I ran the code?
Thanks a lot again.

 

[vbaInet]

You're welcome!

If there's other code that is using or calling that ole object, yes it will affect it.

I thought you were running this code on a copy of the db. Don't change the ole object names, change the name in the code whenever you want to run it.

 

[Victor70]

But the code refers to olePivotTable in

 Set xlApp = frm.olePivotTable.Object.Application

[FONT=&quot] so I have to change the OLE object name anyway (I guess).

Again thank you for your time.
[/FONT]

 

[vbaInet]

As mentioned in my last post, just change the code to reflect the correct name if you feel it will affect your app.

You only need to change olePivotTable to whatever the name of the ole object is called and the rest of the code will run fine.