wimgielis.com

Slice out views to Excel in batch

• Dec. 17, 2015

	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
' https://www.wimgielis.com
'''''
' 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
' https://www.wimgielis.com
'''''
' 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.

Download

At the top of this page you can download an Excel file with my code and an interface to include cubes and views.

• « Previous TM1 article
• Next TM1 article »