Microsoft Excel

Hi David

I actually have another question about pivot tables. unfortunately i cannot send the ones im talking about to you due to both client confidentiality and also the external datasources.

the problem i have is that this client has inherited some excel files with large pivot tables based on external datasources. We are trying to work out just what queries these pivot tables are built from. we know the database name but not the queries. is there any way in excel we can work that out?

In Excel 2007, the following should do the trick:

Data tab > Connections > select the connection used by the Pivot > Properties > click on the Definition tab :: the query is in the "Command Text" box.

I mentioned this at the Birds of a Feather session at the last PDC, but one feature that would be very useful in the financial space is if PivotTables (and their related charts) could recalculate based off of the Real Time Data interface.

Using the RTD API, suppose I pipe in live order data combined with live market data.  I'll want to put that into a pivot table and chart the results.  But I want the charts and the pivot tables to be "live" -- no hitting refresh, just update as the underlying data is updated.

--Oren

Er, sorry, not the Birds of a Feather -- it was at the experts roundtable with the dev team that one evening over dinner (Thurs?)

how about just some simple code that loads a series of workbooks and takes 5 times longer in vista/o2k7 than it takes in xp/o2k3?

where can we send this? this is totally unacceptable to me.

gay keramidas - yes, I would be happy to have someone take a look at your code.  Please use the same email address.

onovotny - thanks for the feedback.

I have uploaded a test file that has a problem for us and has HALTED our use of Office 2007 as we found that we have a problem with files that set our dual core 2.2GHz 1GB RAM Vista Business x32 systems to a stand still with SATA II drives, with 128MB Shared video.

ftp://Microsoft:Microsoft@ftp2.nuvera.com

or

ftp://Microsoft:Microsoft@ftp2.nuvera.com/Excel%20Large%20Chart%20Example.xlsx

This is from a user who worked on this files and gave me this.  I emailed this to Microsoft, but I wanted to see if someone could help me with it.

"Hi Matt –

A test file is attached for the Excel 2007 problem. I tried to keep it short enough that it only locks up the application for a minute or two, rather than half an hour.  To see the  problem, open the file, switch to the “Chart” tab, and try to select one of the lines on the plot. Usually two or three clicks is enough to bring up the shiny spinning circle of doom. At this point, at least one processor core should crank up to 100% load, and Excel won’t respond for a little while.

Let me know if the file produces the expected result."

email me at register@@nuvera...com

Thanks David

Is there a similar function in o2k3 for the external datasaource query question?

sent my files but the email was returned. i sent them again using another of my emaill accounts, and hopefully you received them.

Matthew - receioved files, someone from team will be in touch.

Gary - nothing as of yet.  Try sending email w/o attachements to see if that works.  I have received other files.

Tim - I don't have 2003 installed ... give me a day or two.

ok, i tried to send from my hotmail account, one with and one wihout the attachment (a zip file)

let me know if you receive either one.

Hi, I have an unrelated request.

I was wondering if you could make the preview window in the text import wizard bigger in the next version of Excel. It's difficult to properly place the fixed width separators for many reports I have with such a small preview window.

thanks

Jim - thanks for the feedback.  No promises, but I will pass the feedback along.