Slice out views to Excel in batch

Example files with this article:
  • Slice out views to Excel in batch
  • Introduction

    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!

    VBA code

    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 '
    ''''' ' VBA-code to slice out views to Excel in an automated way ' 12/17/15 '''''
    Dim 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.UserName
    End Sub

    You will want to have the code of this function too:

    Function CleanWorksheetName(ByVal strName As String) As String
    ' Wim Gielis '
    ''''' ' VBA-code to slice out views to Excel in an automated way ' 12/17/15 '''''
    Dim 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.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links