I upgraded TDP to 4.0 yesterday and upgrade my automation scripts. I have several that export data to a tab in Excel files with existing pivot tables. When the scripts ran this morning, the resulting Excel files were corrupt and needed to be repaired. It appears as if Toad collapsed a bunch of rows in the pivot table causing the problem. I was able to fix this by selecting the entire worksheet with the pivot table and double clicking the rows to expand, then saving the repaired file and re-running the automation script. I will document this in better detail, but I just wanted to get something on the forum in case anyone else is having the same problem.
Conditional formatting rules were also deleted from the Excel file. I am also seeing some files with grouped tabs, which were not previously grouped. This is preventing pivot tables from updating.
This is concerning. We did swap out a component that does the exporting to Excel. Can you open a support ticket and provide your excel files so we can fix?
I’m having a similar problem when exporting data into an excel file with pivot tables after upgrading to 4.0. I’m receiving a message when opening the file that the pivot table cache is corrupted. I’ve been using this workbook for a couple of years, most recently with 3.6 and have had no issues until the upgrade. I’ve tried creating new versions but I have the same result each time.
Paul - are you using the Select to File activity or the Export Wizard? I was running macros in my workbook, but I was using the select to file activity, forcing it to point to the macros enabled file by typing in the “xlsm” because that type of activity would not point to macro workbooks. I changed my export to the Export Wizard, running the macros after export. My initial test file opened with no error messages about the workbook being corrupt.
Let me know if that helps. I will do further testing just to make sure this is the solution for me.
We fixed the conditional formatting and some macro issues. This will be in a TDP 4.01 patch. I will try and get this out in Beta early next week.
Hi,
I’m using the Select to File. The location is a sheet in a standard xlsx file. The sheet is cleared prior to export using the option in the Advanced functions of the Select to File. No macros or conditional formatting but there are pivot tables built over the data.
Regards,
Paul
From: andy.corson [mailto:bounce-andycorson@toadworld.com]
Sent: Thursday, August 25, 2016 3:34 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
RE: TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
Reply by andy.corson
Paul - are you using the Select to File activity or the Export Wizard? I was running macros in my workbook, but I was using the select to file activity, forcing it to point to the macros enabled file by typing in the “xlsm” because that type of activity would not point to macro workbooks. I changed my export to the Export Wizard, running the macros after export. My initial test file opened with no error messages about the workbook being corrupt.
Let me know if that helps. I will do further testing just to make sure this is the solution for me.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
CAUTION: THIS MESSAGE ORIGINATED FROM THE PUBLIC INTERNET AND WAS NOT SENT FROM THE SMITHS EMAIL SYSTEM
PROPRIETARY: This e-mail contains proprietary information some or all of which may be legally privileged. It is intended for the recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the authority by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail.
I had been working with Andy on his similar issue. He is using SelectToFile like you are. But we are now suspecting the version of Excel to be at the bottom of the issue. What version of Excel was the file originally made with? There seems to be a change in Excel in how they are storing things. If you make a new Excel Pivot using latest version of Excel do you still have this issue?
Hi,
I’m using Excel 2010. There are a few points to note:
I was originally running this automation from a desktop machine using DataPoint 3.6. The desktop had Excel 2010 installed.
I migrated the automation from the desktop to a Windows server machine running Windows Server 2012 R2. I started with DataPoint 3.8 and had the issue beginning immediately.
Excel is not installed on the server.
I created a new workbook in Excel 2010 and replaced the old one but the issue continued. I also tried saving locally on the server and to a network location with the same result.
I upgraded DataPoint to 4.0 to resolve the issue with the Excel row count limitation (which was resolved with 4.0). The issue with the pivot table corruption continued with 4.0
The question in my mind is do I need to have Excel installed on the server to overcome this? I understand that DataPoint does not require this and I don’t want to buy an extra MS Office license if I don’t need to.
Regards,
Paul
From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Monday, August 29, 2016 11:36 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
RE: TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
Reply by Debbie Peabody
I had been working with Andy on his similar issue. He is using SelectToFile like you are. But we are now suspecting the version of Excel to be at the bottom of the issue. What version of Excel was the file originally made with? There seems to be a change in Excel in how they are storing things. If you make a new Excel Pivot using latest version of Excel do you still have this issue?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
CAUTION: THIS MESSAGE ORIGINATED FROM THE PUBLIC INTERNET AND WAS NOT SENT FROM THE SMITHS EMAIL SYSTEM
PROPRIETARY: This e-mail contains proprietary information some or all of which may be legally privileged. It is intended for the recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the authority by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail.
No. You do not need to have Excel installed on server for general export. You only need it if you are running macros.
I not convinced we have the fully understanding yet on the Pivot tables. So your shop is using Excel 2010, not 2013 or 2016?
I am on 2010 currently. We will be soon upgrading to office 365.
My assumption regarding having Excel installed on the server is based on my experience that I did not have this issue when executing from the desktop which had Excel installed on it, though it was using DP 3.6
Regards,
Paul
From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Monday, August 29, 2016 12:18 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
RE: TDP 4.0 Automation Scripts Exporting Data to Excel Files with Pivot Tables - corrupt files
Reply by Debbie Peabody
No. You do not need to have Excel installed on server for general export. You only need it if you are running macros.
I not convinced we have the fully understanding yet on the Pivot tables. So your shop is using Excel 2010, not 2013 or 2016?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
CAUTION: THIS MESSAGE ORIGINATED FROM THE PUBLIC INTERNET AND WAS NOT SENT FROM THE SMITHS EMAIL SYSTEM
PROPRIETARY: This e-mail contains proprietary information some or all of which may be legally privileged. It is intended for the recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the authority by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail.
There still might be someone here we are missing. Can you open a support ticket and give them copies of the files you use. I can take a deeper look to see what is occurring.
I’m having the same problem as Paul. I’m on Office 2013. My script does a File Copy and then Export Wizard to update a data sheet on a .xlsm. There is a pivot on another tab. When I try to open the .xlsm it gives me the corrupt error: “We found a problem with some content…” then “Repaired Records: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)”
It runs fine in 3.8.
Julie