Comment from derek
Time: Friday, May 8, 2009, 1:29 pm

I have, once or twice, managed to use CSV files and Excel ODBC to have pivot tables and pivot charts work on otherwise unmanageably large data sets. That way I could use more than 65,000 records, or send the files through email as a megabyte or two instead of something that broke inboxes.

But I was distressed to find I couldn’t share the solution unless I could instruct the recipient to place both Excel file and CSV file in *exact* copies of the directory structure I developed the solution in, including the drive letter (good luck persuading a director to jump through that hoop!)

Do you or any other MVPs have a way to break that limitation?

Comment from Bob
Time: Friday, May 8, 2009, 2:12 pm

Hi Jon,

All 300 are gems.
Peppered with a distaste for chart junk and pies you can’t eat as well.

Cheers,

Bob

Comment from Debra Dalgleish
Time: Friday, May 8, 2009, 8:07 pm

Jon, congratulations on the 300th post!
Derek, if the recipient puts the two files in any directory, and refreshes the pivot table, they should be prompted to select the source file. That would update the connection string, and they could save the Excel file with the revised information.

Comment from derek
Time: Saturday, May 9, 2009, 5:37 am

Thanks Debra, but when I move them to a new directory and refresh, I don’t get any of that, only a complaint that it can’t find the CSV file and an admonition to check that the file and path name is exactly correct.

If I try to step back through the pivot table wizard I can’t make a new query, and if I try to step forward through “Get Data”, it complains it can’t edit the existing query, then repeats the original error above.

This is Office 2000 SR-1 with MS Query installed, under Windows XP SP-3. The original problem on work machines was the same Office installation, under Windows 2000.

I asked here because of Jon’s reference to “use a chart to see patterns in your data”, but if it’s more appropriate to continue the conversation on a suitable place on your web site, please let me know, and I’ll recap there.

Comment from Debra Dalgleish
Time: Saturday, May 9, 2009, 8:44 am

Thanks Jon, we’ll try to keep the noise down. ;-)

Derek, what happens if you click OK in that error message? For me, it opens a Select Directory dialog box.

Comment from derek
Time: Saturday, May 9, 2009, 10:10 am

I wish that was what would happen! It’s what I would expect. What actually happens is that another error box comes up saying “Problems obtaining data”.

More news on the “Get Data” in pivot table wizard method. I played with it a bit just now, and I have found that after it tells me I can’t edit the query, it opens the query, and indeed things like View.. Tables and Add Tables are grayed out. But if I open the SQL, I can edit the path by hand in that box, or if I succeed in removing all SQL instructions, I can then rebuild the Query by hand (but see my remark about hoop-jumping; this is not a workable solution).

Comment from Debra Dalgleish
Time: Saturday, May 9, 2009, 3:55 pm

Sorry Derek, I can’t recreate the problem. Could you add a bit of programming to let the user select a file, and change the connection string based on their selection?

Comment from derek
Time: Saturday, May 9, 2009, 5:49 pm

That’s what I hoped might be possible, and feared feared would be necessary. It may not just be me, as I see that Microsoft’s own support pages suggest it used to be a known problem, and they supplied a bit of VBA that might help.

But it has hard coded directories, when I’d rather a button that searched out the directory in which the Excel pivot table currently resides, and assumes the CSV file is in the same place.