Please feel free to contact me about anything on this blog @ ptebraak@insightdm.net
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
Please feel free to contact me about anything on this blog @ ptebraak@insightdm.net
Paul te Braak 
Hello Paul,
while searching my issue with excel slicer macro, I came across your post and thought to ask you if you can help me on this. I’m trying to create a macro to copy a chart from excel to power point (2010). Very easy right? But, excel sheet has a slicer with 14 different region and has to click each one to populate the chart! Please help my job depends on this and I’m very new with vba and for loop. Thanks and will be waiting for your response.
Mash
Hello Mash,
You can cheat this (for want of a better word) by detecting the Worksheet_Calculate event. This will be fired whenever the value of the slicer changes.
Paul
Hi Paul , your article ‘Creating Tables in Excel with VBA and External Data ‘ helped mein resolving a requirment for my develoment. Thanks a lot.
Regards,
Shiv
Thanks Shiv – I’m glad it helped!
Hi Paul,
For finding out how items are selected in a slicer I used your solution below:
I get a error in line :
Set sl = sc.SlicerCacheLevels(1)
“run-time error 1004
Application defined/object defined error”
Tried a lot of things but it does not do any good…
Hope you can help me out …
Thanks in advance,
Olav
Function SelectedCount() As Integer
Dim sc As SlicerCache
Dim sl As SlicerCacheLevel
Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
Set sl = sc.SlicerCacheLevels(1)
For i = 1 To sl.SlicerItems.Count
If sl.SlicerItems(i).Selected = True Then SelectedCount = SelectedCount + 1
Next
End Function
Regards,
Paul
Hi Olav,
Is you slicer cache set correctly (sounds like it is not). Just check the name property while debugging.
Regards,
Paul
Hi Paul,
I was wondering if you could help me with an issue. I’m importing data from a CSV flat file to OLEDB destination, Problem is, the CSV TextQualifier is “, a speech mark, but I can’t find the appropriate value / attribute to use in the FlatFileFormat element. I’ve tried TextQualifier in the FlatFileFormat, and i’ve tried using it in the child Column element. BIML refuses to recognise it.
What am I doing wrong?
Hi Paul, you’re a genious, just figured it out…
Typo, TextQualifier is actually called TextQualifer, no I before the E!!!! Its spelt with an extra I on the Varigence site
Hi Paul, thanks for your kind work, it do helps me a lot!
Thanks Thomas – I’m glad you found something useful 🙂
Paul do you know how to disable the shortcutmenu when you right click a pivot table slicer (generated by powerpivot). So I do not want people to be able to right click on the slicer. Thanks in advance.
Sorry Roel, No idea
Hi Roel,
If my assumption is correct and I understand what you mean….
• Select your slicer
• Right click
• Select ‘Size and Properties’
• Select ‘Position and Layout’
• Check the box: ‘Disable resizing and moving
HTH
Hi Jose,
Thanks for your reply. That is not what I am looking for, this does not work for my case.
Hi Paul, Have you got any BIML code to do a incremental DWH load?
Hi Troy,
I do but its not something I’ll willingly give out (sorry). The basic principles though are that you flag the table for incremental load and identify columns which identify the process (assuming there is always only one this can be part of the table meta-data but i prefer to use the column meta data to manage that).
Then, if the table is marked for incremental load, you do the same thing that you’d do in SSIS -> store the max value from the column in a variable and only select data from the source which is > the variable value.
HTH,
Paul