Change multiple Pivot Table connections without query or refresh !

[Gabriel222]

Hello,

I'm on Excel 2007, Windows 7


Issue:

I have about 35 pivot tables in one workbook, about 4 per sheet (fyi this is not by my choice).

I need to point all the Pivot Tables connections to the same shortcut on my computer which itself in turn points to the OLAP in the company's network.
(for whatever reason I need to point it to that specific shortcut - if I give a direct address it seems to block according to my attempts)

Every time I change a connection and "validate", an OLAP Query begins (the Pivot Table then refreshes)
this takes A LOT of time circa 15min per Pivot Table when all is smooth on the network.

Doing this for all Pivot Tables would take me a very long time and would effectively slow down my computer for the day.


Therefore I'm looking for a solution but I have difficulty creating a macro for either one of them !
(I'm no VBA wiz kid but I've always managed to get to the objective, I also know very little about OLAP or Cubes or ODBC)
Here are the different types of solutions I am considering


Potential Solutions:

1/
I don't mind waiting a long time, I'll let the computer run all night.

However 2 problems occur:
the first is how do I force the code
to wait until one pivottable finished changing connection
running the OLAP query
and refreshing etc...

before moving on to the next one ?

The second problem is how do I point it to the right shortcut ?

(all my tries in pointing it to the shortcut have failed,
I've tried the full file path and also only the name of the connection
(seen as it is already "seen" by Excel under the change my connection tab, in the ribbon, under "connection file in this computer"),
Ive also tried using it without an extension to the file/ filepath or with one (.lnk or .odc)


2/

I don't need it to refresh or do a query at all ! (at least not right away) - I simply want to change the connection !

However, I can't find any macros that would allow me to do that in Excel 2007.

The closest I have found was this

http://www.pcreview.co.uk/forums/ch...ant-change-original-copied-pivo-t3161681.html

which is only for 2003 and is quite complex for me and I'm not convinced it would work or apply in my situation.


3/

Somehow pull out all the data I need form the OLAP in flat format, .csv or whatever, and use it directly on my computer I strongly doubt this is possible though (corporate network) and don't know where/how to start.



Any help would be great! I can provide further details of course (I do hope I was thorough enough).

Thanks