Armygeddan
Board Regular
- Joined
- Apr 6, 2016
- Messages
- 79
Final product :
1. Loop both Slicers (Slicer_Region, Slicer_Customer)
2. Save each Slicer loop as a PDF and Excel file
3. Only save certain Tabs in the whole file when exporting to PDF and Excel (Array)
4. Have the File Save Format as YYYY_MM(Previous Month)_FilePath(NamedRange).Pdf/Excel
5. Create a Year and a Month Folder in the File Save Destination if it doesn’t exist
I’ve got #3, #4, and #5 completed but need help with #1 and #2
When running the Loop and Converting the Slicer Items to PDF and Excel, it needs to save each selection in a certain name save format of YYYY_MM_CELLA_CELLB
For Region, I’ve got
“Middle East/Africa” as “MEA” “Asia/Pacific” as “AP” Single names like Canada are standalone as themselves When running the Loop, Asia/Pacific would need to be converting to the format of AP(Slicer_Customer)
So per Save, it will need to look like the following
<CODE> Save 1
– CELLA_CELL1
Save 2
– CELLA_CELL2 Etc
Save 16
– CELLB_CELL1
Save 17
– CELLB_CELL2
</CODE>I’d also like an initial save of both the Slicer Dashboards with nothing selected on the Slicers and name it as “ALL_ALL”
That’s a lot of loops to save but sure beats doing it manually.
Here is how my Slicer looks like (Edited out confidential information)
I’ve only been working with VBA for a couple of months so all of the above is quite a bit advanced for me but would give me a huge learning curve for future projects like this. I’ve got the Basics of VBA down but working with Slicers has thrown me in for a Loop (Pun Intended)
Please note that all my data was initially pulled with OLAP but it is all saved in a PowerPivot Data Model so there should be no worries of external sources when gathering data from external sources when the Slicers are selected.
Thank you so much in advance, I know this is asking a lot.
Here is what I have so far:
(Please Note I am current getting an error on Line 20) Says “For Each Slice In MySlicer.SlicerItem” And the error says “Run-time error '1004' Application-defined or object-defined error”
1. Loop both Slicers (Slicer_Region, Slicer_Customer)
2. Save each Slicer loop as a PDF and Excel file
3. Only save certain Tabs in the whole file when exporting to PDF and Excel (Array)
4. Have the File Save Format as YYYY_MM(Previous Month)_FilePath(NamedRange).Pdf/Excel
5. Create a Year and a Month Folder in the File Save Destination if it doesn’t exist
I’ve got #3, #4, and #5 completed but need help with #1 and #2
When running the Loop and Converting the Slicer Items to PDF and Excel, it needs to save each selection in a certain name save format of YYYY_MM_CELLA_CELLB
For Region, I’ve got
“Middle East/Africa” as “MEA” “Asia/Pacific” as “AP” Single names like Canada are standalone as themselves When running the Loop, Asia/Pacific would need to be converting to the format of AP(Slicer_Customer)
So per Save, it will need to look like the following
<CODE> Save 1
– CELLA_CELL1
Save 2
– CELLA_CELL2 Etc
Save 16
– CELLB_CELL1
Save 17
– CELLB_CELL2
</CODE>I’d also like an initial save of both the Slicer Dashboards with nothing selected on the Slicers and name it as “ALL_ALL”
That’s a lot of loops to save but sure beats doing it manually.
Here is how my Slicer looks like (Edited out confidential information)
I’ve only been working with VBA for a couple of months so all of the above is quite a bit advanced for me but would give me a huge learning curve for future projects like this. I’ve got the Basics of VBA down but working with Slicers has thrown me in for a Loop (Pun Intended)
Please note that all my data was initially pulled with OLAP but it is all saved in a PowerPivot Data Model so there should be no worries of external sources when gathering data from external sources when the Slicers are selected.
Thank you so much in advance, I know this is asking a lot.
Here is what I have so far:
(Please Note I am current getting an error on Line 20) Says “For Each Slice In MySlicer.SlicerItem” And the error says “Run-time error '1004' Application-defined or object-defined error”
Code:
Sub LoopSlicer()
Dim strGenericFilePath As String: strGenericFilePath = Range("FilePath")
Dim strYearSlash As String: strYearSlash = Year(Date) & "\"
Dim strMonthSlash As String: strMonthSlash = CStr(Format(DateAdd("M", -1, Date), "MM")) & "\"
Dim strYearBracket As String: strYearBracket = Year(Date) & "_"
Dim strMonthBracket As String: strMonthBracket = CStr(Format(DateAdd("M", -1, Date), "MM")) & "_" & "Smart_Data_"
Dim strFileName As String: strFileName = "Standard_Customer"
Dim IntSliceCount As Integer
Dim IntLoop As Integer
Dim SliceLoop As Integer
Dim Slice As SlicerItem
Dim MySlicer As SlicerCache
Set MySlicer = ActiveWorkbook.SlicerCaches("Slicer_Region")
IntSliceCount = 0
' Count slicer options
For Each Slice In MySlicer.SlicerItem
IntSliceCount = IntSliceCount + 1
Next Slice
' NOTE:--------------------------------------------------------------------------
' When selecting a slicer, all the other slicers in the field must be de-selected
' -------------------------------------------------------------------------------
' Activate slicers one by one and print
With MySlicer
For IntLoop = 1 To IntSliceCount
' Activate the current slicer in loop, deactivate rest
For SliceLoop = 1 To IntSliceCount
If IntLoop = SliceLoop Then
.SlicerItems(IntLoop).Selected = True
Else
.SlicerItems(SliceLoop).Selected = False
End If
Next SliceLoop
Application.DisplayAlerts = False
' Check for the Year Folder and create it if it does not exist
If Len(Dir(strGenericFilePath & strYearSlash, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYearSlash
End If
' Check for the Month Folder and create it if it does not exist
If Len(Dir(strGenericFilePath & strYearSlash & strMonthSlash, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYearSlash & strMonthSlash
End If
On Error Resume Next
On Error GoTo 0
' Converts to PDF and saves with YYYY_MM_ Format + Filename based off of FilePath String
ThisWorkbook.Sheets(Array("TestSheet1", "TestSheet2", "TestSheet3")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:= _
strGenericFilePath & strYearSlash & strMonthSlash & strYearBracket & strMonthBracket & strFileName, _
Quality:=QualityStandard, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.DisplayAlerts = True
' Popup Message that the conversion and save is complete as YYYY_MM_Standard_Customer
MsgBox "File Saved As:" & vbNewLine & "\" & strYearBracket & strMonthBracket & strFileName
' De-activate the current slicer in loop, activate the rest
For SliceLoop = 1 To IntSliceCount
If IntLoop = SliceLoop Then
.SlicerItems(IntLoop).Selected = False
Else
.SlicerItems(SliceLoop).Selected = True
End If
Next SliceLoop
Next IntLoop
End With
End Sub
