acCmdPivotTableExportToExcel - Run-time error '91' object variable or with block variable not set

Question

I'm trying to save the my exported pivot table in excel but I'm revcieving the following error. I can then run through the code fine. How can I fix this?

set xlApp = new Excel.Application

xlApp.Visible = true

docmd.openquery "report",acViewPivotTable, acReadonly

docmd.RunCommand acCmdPivotTableExportToExcel

with xlapp

.ActiveWorkbook.Activate   <--- I get the error here

End With

Thanks

SCP1234



Answer 1

I can't see why the Activate statement should be required. What are you trying to accomplish?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 2

Hey Doug - The command to export the pivot table creates a spreadsheet (ex. PivotTable12345.htm) that I need to associate with an excel object so I can save it to a specific location. When my code runs it throws an error. If press play it runs to completion saving the file (I did not provide all my code).

It seems like the excel object is not active to take control.

...Basically I need to save the file but can't seem to get aroud the errors.

If you have any suggestions they would be greatly appreciated.

Regards

Scot

Answer 3

You do not need to use Activate if all you're trying to do is save the workbook. Use the SaveAs method of the Workbook object.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 4

How do you get control of the spreadsheet?

I have used the following code, but it had the same issue.

SET xlApp = getObject(,"Excel.Application")

Answer 5

Hi,

the problem here is a bit different from what you're trying to do. This is some kind of asynchronous execution. The code doesn't wait Excel workbook to be created and go to the next line where you're trying Activate a workbook. Of course, there is no workbooks at all if you have no already opened any Excel file.

So the idea is to wait the workbook to be created and only then try to find it.

Dim xlApp As Object, xlWB As Object
    DoCmd.OpenQuery "report", acViewPivotTable, acReadOnly
    DoCmd.RunCommand acCmdPivotTableExportToExcel
    On Error Resume Next
    Do Until Not xlApp Is Nothing
        Set xlApp = GetObject(, "Excel.Application")
    Loop
    On Error GoTo 0
    Set xlWB = xlApp.ActiveWorkbook
    Debug.Print xlWB.Name

There is also a good thing. This workbook will be ActiveWorkbook anyway, even if you have already opened some other workbooks. So you don't need to bother with it. You'll get exactly what you're expecting to get.

UPDATE: my fault. Good thing is incorrect. And you should think how to find this newly created workbook. So we need another one test:

Dim xlApp As Object, xlWB As Object, wbn%
    DoCmd.OpenQuery "PTQ", acViewPivotTable, acReadOnly
    DoCmd.RunCommand acCmdPivotTableExportToExcel
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Do Until Not xlApp Is Nothing
            Set xlApp = GetObject(, "Excel.Application")
        Loop
    Else
        wbn = xlApp.Workbooks.Count
        Do Until xlApp.Workbooks.Count <> wbn
        Loop
    End If
    Set xlWB = xlApp.ActiveWorkbook
    On Error GoTo 0
    Debug.Print xlWB.Name

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 6

You already have control of the spreadsheet through xlApp in your existing code. All you should need to do is use xlApp.ActiveWorkbook.SaveAs ...

Perhaps put in a DoEvents before that statement, to accomodate the time delay Andrey describes.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 7

Doug,

all I need is to put DoEvents after DoCmd.RunCommand? I tried this but with no luck. Am I missing anything here or it isn't guaranteed that it should help?

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 8

No guarantees. :( To be honest, I've never used the DoCmd.RunCommand acCmdPivotTableExportToExcel command, so I'm not really sure whether you can supply it with a file name or not.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 9

Hey Doug - I'm not having any luck with either approach. Any thoughts on where to go from here? 

Thanks in advance for your help,

Scot 

Answer 10

Sorry, no. As I said earlier, I've never used the DoCmd.RunCommand acCmdPivotTableExportToExcel command. Hopefully someone else will pipe in with additional suggestions. Good luck!

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 11

Hi Scot,

Welcome to the MSDN forum.

Please take a look at the following code:

Sub TestExportPivotTable()
Dim xlapp As Excel.Application
Dim xlWB As Excel.Workbook

DoCmd.OpenTable "tbProducts", acViewPivotTable, acReadOnly
DoCmd.RunCommand acCmdPivotTableExportToExcel

On Error Resume Next
Do Until Not xlapp Is Nothing
    Set xlapp = GetObject(, "Excel.Application")
Loop
   
Set xlWB = xlapp.Workbooks(1)
xlWB.SaveAs "D:\Test.HTM", Excel.XlFileFormat.xlHtml
xlWB.Close
xlapp.Quit

Set xlapp = Nothing
Set xlWB = Nothing
End Sub

Note: We need to add the reference for "Microsoft Excel 14.0 Object Library".

Hope it helps and have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 12

My post seems to be completely ignored but it works.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 13

Hey Andrey - I gave your solution a try without success. Can you provide the references that you have selected for VB.

Thanks

Scot

Answer 14

Hi Scot,

I tested Andrey's solution and it works fine.

We need to check the reference for "Microsoft Excel 14.0 Object library" if it is Excel 2010.

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 15

Hi Scot,

there are no additional references needed. I use late binding for Excel automation, thus you don't need Excel Object library reference. If you have any issues with my code sample, please, specify what exactly do you have.

Of course, "PTQ" is a kind of typo, it's the name of my query I tested this sample with, so change it to your "report". + my code just prints the name of the active workbookt. To save it change Debug.Print line to xlWB.SaveAs ... and check whether the result meets your requirements.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 16

Audrey - I'm getting the following error when implementing your code. I'm using Access and Excel 2007

Run-time error '429'

ActiveX Component can't create object.

Thanks

Scot

Answer 17

Hey Yoyo - I tried you example with the same error. When I click run ,after the error, it completes successfully. 

Run-time error '429'

ActiveX component can't create object.

Thanks for you help,

Scot



Answer 18

Scot,

are you sure you use GetObject rather than CreateObject? Did you copy&paste these code sample w/o any modifications or did you add anything? This error seems to be really strange in this context and it shouldn't be there.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 19

Hi Scot,

Please check the following KB:

GetObject and CreateObject behavior of Office automation servers
http://support.microsoft.com/kb/288902

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 20

Hi Scot,

I'm writing to check the status of the problem.

Please feel free to let us know if you have any problem, and you're welcome to share your idea on this issue.

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us