Log In
Register
Home
July 11, 2016
Offline
Hi,
I have an excel 2010 workbook with a worksheet named Details. And have slicers with one table connect with external data source from ms access.Output is tables rows and columns.
Then I have another sheet named Chart where i create the new pivot tables and slicers (data source is from Details.)-output is charts
The two sheet is independent with each other for different slicer but with same source.The new pivots can't be associated to old slicers.
How do i use macro to connect all the pivot tables with slicers(the slicer can connect with Chart data as well).
I have search around regarding this and found some need to change the pivot cache but i do not know how to use it.
I am new on macro ,perhaps a tutorial will be great for me to learn.Thanks and appreciate if anyone can help!
Regards,
Hooi Thin
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Hooi,
You can try this macro to disconnect all slicers:
Dim SlicersDict As Variant
Set SlicersDict = CreateObject("Scripting.Dictionary")
Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant
'create a dictionary with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
SlicersDict.Add Key:=sl.Name, Item:=sl.PivotTables
Next
'take each slicer
For Each SlItem In SlicersDict.Keys
'remove pt connections for this slicer
For Each slpt In SlicersDict(SlItem) 'for each pivot table controlled by this slicer,
slpt.SaveData = True
ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (slpt)
Next slpt
Next SlItem
Set SlicersDict = Nothing
End Sub
To use the code, follow the instructions from here: how-to-use-vba-code-from-the-internet
July 11, 2016
Offline
Hi Catalin,
Thanks for your help.Glad to receive your response.
I am newbie on macro, sorry that i am still not understand the code meaning.
Why do we have to disconnect all the slicers?
For this below part, is it the meaning that i have to put the slicer name and the desired pivot tables name that i want to connect with?
'create a dictionary with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
SlicersDict.Add Key:=sl.Name, Item:=sl.PivotTables
Next
For the situation i am facing is like:
Slicer A connect with table B (not pivot table,connected with ms access)
Slicer A not able to connect with pivot table C (source from table B)
I wanted to connect Slicer A connect with pivot table C. How do i make it?
Hope you can explain for further details.Thanks again!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
yhooithin05 said
Why do we have to disconnect all the slicers?
Well, that's the name of the subject: "macro to detach all slicers from all pivot tables"
Why would you choose such a name and ask this question? 🙂
A defined table does not have a cache. Only a pivot table has a pivot cache, there is nothing to be shared between those 2 different objects.
All you can do is to write a code that will read the .Selected attribute from all items in a slicer, and apply it to the other slicer.
Check the attached file for an example. I used a code like this one, for pivot table vb sheet module:
Dim SlItem As SlicerItem
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_Field11").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
End Sub
On the other sheet, with a defined table, something like this should be used:
Dim SlItem As SlicerItem
MsgBox 1
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field11").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
End Sub
it's just an example, the code should be more complex, to take into consideration all possible problems, it might not work perfectly in this basic form.
Catalin
Answers Post
July 11, 2016
Offline
Hi Catalin,
I am sorry for my bad english. I have misunderstand the "detach" meaning. I wanted to ask how to connect slicers one 😀
I've tried it follow like your example. It works like charm! Don't know how to thank you 🙂 But now one of the thing is when i click on the item on slicer,it loads very slow only can filtered out result.I found out the reason might be because of i have too many slicers in the workbook.I have many tabs and slicers so total have more than 20 slicers 🙁
The codes is running through all the slicers so causing the process running slow?
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems----> Can i run through only the selected sheet but not whole workbook?And what code for worksheet?
Thanks again!!
Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
yhooithin05 said
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems----> Can i run through only the selected sheet but not whole workbook?
The code is not running through the entire workbook, it is looping through all Slicer Items from the SlicerCache named Slicer_Field1, the slicer cache is stored in ThisWorkbook, that's what the line of code you mentioned means.
The code has to check each record from that column, to see if it is visible (selected) or not, and apply the same setting to the mirrored slicer:
SlItem.Selected can be true or false.
If the code takes a long time to run, you may have a large number of unique entries in that column.
You can try to set ScreenUpdating to false at the beginning of the code, and set it back to true at the end, this may increase execution speed.:
Application.ScreenUpdating=False
The defined table code, should disable events before applying the attributes to the pivot table, otherwise the pivot table change event may trigger that code too.
Application.EnableEvents=False (set back to true at the end of code)
Catalin
July 11, 2016
Offline
I've tried to put Application.ScreenUpdating = False and even Application.EnableEvents=False on defined table code, but still not working. The slicers still loading very slow. When i try to debug and point to the code "Application.ScreenUpdating = False" (its showing true)
Is there any other method to speed them up?
Pivot table
Option Explicit
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim SlItem As SlicerItem
Application.ScreenUpdating = False
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
Application.ScreenUpdating = True
End Sub
Defined Table
Option Explicit
Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
Dim SlItem As SlicerItem
MsgBox 1
Application.EnableEvents = False
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
Application.EnableEvents = True
End Sub
Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
I do have to say that I don't really understand the need of filtering both the source table and the pivot table report made from that table.
Maybe if you can explain what is it you want to achieve, we can find a different approach, we don't have to struggle in a possibly wrong design concept. There is an old saying: "all the roads lead to Rome", and another interpretation is saying that: "there is more than 1 road to Rome". In excel, there are always multiple alternatives to obtain the same output.
Take your time and prepare a sample file with as many details as possible, so we can understand your situation. In most cases, there is no need for VBA, we have a lot of powerful tools that we can use.
Cheers,
Catalin
July 11, 2016
Offline
Thanks Catalin for your kind patience and efforts to help me clarify all the things.
Attached my example code hope can make clear situation.My intention is to connect slicer on order details with order charts, previously before using your code, i am not able to control the charts using the slicer on order details. That moment i have to create another slicer on the order charts so both the page is independent to each other.
Now i added the code that you given,currently my slicer on order details can control the defined table and charts as well.
And i am almost to complete the target i want but now facing speed issue on running the slicer. If it is no need for VBA it will be great, but i am stuck on here right now.
Thanks again for your assistance!
Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Hooi,
I suggest leaving the source data as is, use that table just for data entry, not for reports.
See the attached file, Sheet1 does exactly the same thing as you did with table slicers, it's a pivot table returning all columns from the database, no aggregation.
And you can connect all slicers to all reports, they have the same source now, and it's much faster.
Catalin
July 11, 2016
Offline
Oh Catalin, bravo! 🙂
You know that i have been tried to put like your way as pivot table previously ,but it showed as compact form by default, i don't even go to figure out actually it have a report design setting which can display as outline form like table forms and i just give it up to replace as a table, end up wasting time on other parts.
Luckily i've meet you if not i don't know how much time i need to waste again and maybe go to holland..XD
Now i know the powerful tools in excel, just a simple setting only..LOL
Anyway, really thanks you very much for all this way to help me and explain to me the details. You are my life saver! God Bless you 😀 😀
Thanks & Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Glad to hear that your problem is solved, we are here to help 🙂
This is a good example that shows why there should always be sample files uploaded with explanations, with a manual result displaying the desired outcome. The solution will come much faster, a description of the data will never be better than a sample file 🙂
Cheers,
Catalin
July 11, 2016
Offline
Hi Catalin,
Yes you are right.
Now i have another trouble again, i linked up the pivot table with my database server data, now the slicers does not show up the other pivot table connections from other tabs.
Last time you changed from the defined table into pivot table (by choosing data source from the defined table to create the pivot table right?), this way able to show all the pivot connections.
But if directly link up the pivot table with database,the slicer only show the pivot connection on that page only.I am not sure why.
Attached is the sample code which i connect with mysql on production live database.Not sure you able to view it.Can you guide me again please?
Thanks & Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
If you connect the pivot table to an external data source, it will have a different pivot cache then the rest of the pivot tables that are using the internal data table as the data source. I mentioned before that the pivot tables must have the same data source if you want to control them with the same slicers, in this case they are not the same.
Why don't you use the same data source? If you want to use the data connection directly in the pivot table, to that for all pivot tables.
If you want to use the imported data table as the source, do that for ALL pivot table reports. If you mix the data sources, you will not be able to do what you need, without complications.
I would use Power Query to connect to MySQL database, load data as connection only, then use that connection to create any reports you want.
What version of excel are you using?
Catalin
July 11, 2016
Offline
Hi Catalin,
I am using excel 2013, pro plus.
Now i am trying the rest pivot tables to connect directly to database but the pivot tables won't sum now.I've search solution might be the "total cost" column in the MySQL database was VARCHAR. Then i've changed the datatype to integer,double,numeric,decimal data type but having error of data truncated issue.The total cost value consist from 0.00000343 until 14148 and i think the error happened because of the comma value(eg: 1,345)
Do you know which data type to store this kind of integers as well?
I've heard of power pivot but not power query.I know them is power tool on excel but not really know how to use them.
For my case, if i do not want to connect external data source into pivot table and not imported table for source(the file will be very big size as pivot and table is same data),the only way is power query?
I just tried to connect power query with mysql, on the setting going to the windows and database authentication there,i misclicked the next button without go to the database part to put server credentials. It cant roll back anymore to reset back after i click next, and keep stuck at the error of
Details: "Keyword not supported.
Parameter name: integratedsecurity"
I tried to uninstall the power query also same error 🙁
I am in another big issue again, would like to seek your help again.
Thanks & Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Hooi,
You have to provide more details. In the file you mentioned, there is no code for connecting to the database, I hoped to find there the information I need to understand the situation.
Is it a local database, on your computer? If so, you need to type localhost as the server name, in the next field type the database name.
In the next window, click on Database (not Windows authentication), and type your access credentials, if it's a protected database. You will then be able to select the tables you want from the database.
If it's not a local server, just type the IP of that server as the server name, with the database name.
Make sure that your IP is whitelisted on that computer, but I don't think you have a problem with that, as you mentioned that you are able to connect and extract data from that server.
July 11, 2016
Offline
Hi Catalin,
Its not local database,i am connecting to our company's live database.
Oh..IP an alternative way, why can't i think that 😀 can connect now finally thank you..i can connect to the power query now.
So i should just create the connection from the power query only right? And i will make the power query workbook as the data source.
Now come back the same issue for the sum of pivot table is not working. The data type of the total cost column on databse consist of integer with comma, currently is saved as varchar so it can't be sum up. Can you help for this?
Thanks & Regards,
Hooi Thin
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Yes, from Power Query tab, choose New Query-From Database-MySQL Database, type the IP and database name, then the credentials in the next window.
After selecting the table you want, choose Close and Load To, then in that window select Connection Only. (this will not load the data into worksheet).
When you create the pivot tables, instead of selecting a range, click the button: From Connection, and you should be able to find the new query listed there.
Use that connection to create all the reports you need.
Where can I see the data errors you mentioned? Can't work with data from my memory 🙂 , you have to upload sample files.
Power Query has very powerful tools to cleanup and reformat the data imported from any source, use them to reformat the data.
July 11, 2016
Offline
Yes. I can set up the connection on power query now 🙂
Attached sample file for your reference. Its great if can reformat the data from text to integer on power query without do anything on the database.
Catalin can you guide me? 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
You have to go to Power Query tab in Ribbon, click on Show Queries to view the Queries Pane, right click the query and select Edit (or just double click the query). This will open the Power Query window, with its own ribbon and tools. To reformat a column, right click that column , select ChangeType, you will have many options there.
Explore the menu, there are many useful transformations that can be done, including adding columns with specific calculations, replacing values, and so on.
See these tutorials for more informations: power+query+site:www.myonlinetraininghub.com
If the file is going to be used by other users, they need to have power query installed to refresh the data. ON the first use of the file, they should open the query and enter their credentials to access the database, data cannot be seen without that. The credentials are not stored in the file, they are stored in user's computer.
You can also join the Power Query course, this will be the standard power tools for data import and processing in excel.
1 Guest(s)
