Slice out views to Excel in batch
- Dec. 17, 2015
|Example files with this article:|
Slicing out views from cubes to Excel can be a time-consuming process. Of course, we generally create slices in Excel, and refresh them using different selections. We do not need to slice the same view(s) over and over again. Yet from time to time the need exists the slice out views to Excel, and/or make snapshots of the same views. Just the raw data, no formatting. Then this article will save you many hours of work!
The code is worth investigating but the most important piece/function is the function VUSLICE. This function allows you to slice out a given cubeview to Excel. You specify servername, cubename and viewname (public or private) and presto !
Sub SliceOutViewsToExcel_InBatch()' Wim Gielis ' http://www.wimgielis.comDim sq As Variant Dim m As Long, n As Long Dim ErrNum As Long Dim sServer As String Dim bCreateSnapshot As Boolean Dim bDeleteEmptySheets As Boolean Application.ScreenUpdating = False Application.DisplayAlerts = False 'user input sServer = [inp_TM1Server] bCreateSnapshot = ([inp_Snapshot] = "Yes") bDeleteEmptySheets = ([inp_DeleteEmptySheets] = "Yes") sq = ActiveSheet.ListObjects("tbl_Views").DataBodyRange.Value With Workbooks.Add 'create sheets and slice the data For m = UBound(sq) To 1 Step -1 With .Sheets.Add 'naming sheets On Error Resume Next .Name = CleanWorksheetName(Format(m, "000") & "_" & sq(m, 2) & "_" & sq(m, 1)) If Err.Number > 0 Then ErrNum = ErrNum + 1 .Name = "Error_" & Format(m, "000") & "_" & Format(ErrNum, "000") Err.Clear End If On Error GoTo 0 'main function in this macro Run "VUSLICE", sServer & ":" & sq(m, 1), sq(m, 2) Application.ScreenUpdating = False Application.DisplayAlerts = False 'snapshot If bCreateSnapshot Then .UsedRange.Value = .UsedRange.Value 'delete empty sheets If bDeleteEmptySheets Then If .UsedRange.Cells.Count = 1 Then .Delete End If End If End With Next Application.ScreenUpdating = False Application.DisplayAlerts = False .Sheets.Add.Cells(1).Value = "Output in the next sheets" 'SheetsInNewWorkbook should always be 1 in Excel, but for those who don't, I delete useless sheets For m = 1 To Application.SheetsInNewWorkbook .Sheets(.Sheets.Count).Delete Next End With Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox "Ready", vbInformation, Application.UserNameEnd Sub
You will want to have the code of this function too:
Function CleanWorksheetName(ByVal strName As String) As String' Wim Gielis ' http://www.wimgielis.comDim varBadChars As Variant Dim varReplacementChars As Variant varBadChars = Array(":", "/", "\", "?", "*", "[", "]") varReplacementChars = Array("", "-", "-", "", "", "(", ")") 'correct string for forbidden characters For m = 0 To UBound(varBadChars) strName = Replace(strName, varBadChars(m), varReplacementChars(m)) Next 'correct string for worksheet length requirement CleanWorksheetName = Left(strName, 31)End Sub
Different starting cell
VUSLICE seems to slice out always to cell A1 in a worksheet, no matter what cell/selection is active when the function is called. I did not see an immediate way to change this, other than inserting rows and/or columns. You can add that bit in the code if you want.
At the top of this page you can download an Excel file with my code and an interface to include cubes and views.