TM1 Forum

Code: Select all

Reporting.QuickReports.Get(qrIDNumber).Commit

Code: Select all

Reporting.QuickReports.Get(qrIDNumber).Refresh

Code: Select all

Reporting.GetCurrentReport(Range("B2")).Refresh

Code: Select all

Sub tm1ellist_test()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim vMDX As String

' recalc for mdx formula update based on selection
Application.Calculate

' mdx formula is on same worksheet as 3 quick reports
vMDX = Range("C2").Value

' activate sheet that has the TM1ELLIST range
Worksheets("Sheet1").Activate

' clear existing values in range
Range("K2").Select
Do Until ActiveCell = ""
    ActiveCell.Delete
    ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False


' insert TM1ELLIST formula
Range("k2").Formula2R1C1 = _
        "=TM1ELLIST(""<server>:<dimension>"",,,,,""" + vMDX + """)"

' refresh sheet with TM1ELLIST formula
'Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet

' OR select cell formula pasted into and refresh just that selection (excel spill will still return full array)
Range("k2").Select
Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSelection


' paste values and clear clipboard
Range("K2").Select
Do Until ActiveCell.Offset(1, 0) = ""
    ActiveCell.Offset(1, 0).Select
Loop
vStop = ActiveCell.Address
Range("K2", vStop).Copy
Sheets("Sheet1").Range("K2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
    


' return to worksheet with quick reports
Worksheets("Sheet2").Activate
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

Code: Select all

Debug.Print Range("K2").SpillingToRange.Address(0, 0)

Code: Select all

Range("K2").SpillingToRange.Value = Range("K2").SpillingToRange.Value

Code: Select all

Sub create_ellist()

    Dim vMDX As String
    
    vMDX = "TM1FilterByLevel( TM1SubsetAll( [Year] ), 0 )"

    'creating a TM1ELLIST function as a dynamic array
    Range("A1").Formula2 = "=TM1ELLIST(""servername:Year"",,,,,""" & vMDX & """)"
    Range("B1").Formula2 = "=""Y"" & A1#"

End Sub

Code: Select all

Sub test()

    Dim r As Range

    With ActiveCell
    
        If IsEmpty(.Offset(1).Value) Then
        
            Set r = .Cells(1)
            
        Else
        
            Set r = Range(.Cells(1), .End(xlDown))
        
        End If
    
    End With
    
    MsgBox r.Address

End Sub

Code: Select all

… «brilliant code above here» …

  'We need the target range active/selected, so make that happen (even if temporary)
    If (ActiveSheet.Name <> vwks.Name) Then
        vwks.Activate
    End If
    
    Set rng = vwks.Range(strAdrElement)
    rng.Select
            
  'Clear original values out, we don't care about anything under it
    rng.Resize(RowSize:=50000).ClearContents
     
  'Update the range with the TM1ELLIST, calc, and force Cognos to freshen up
    rng.Formula2 = "=TM1ELLIST(Core.Server&"":""&" & uDIMFILTER_ADR_NAME_REMOTE_ALIAS & ",,,,," & strAdrMDX & ")"
    vwks.Calculate
    cog.Reporting.RefreshSelection  'OMFG THIS IS AMAZING THANKS ASCHEEVEL
    cog.Reporting.Wait
    
  'Determine new count and update named ranges
    lngRowCount = WorksheetFunction.CountA(rng.Resize(RowSize:=50000))
    
  'Value-paste formula range
    Set rng = rng.Resize(RowSize:=lngRowCount)
    rng.Formula2 = rng.Value2
    
 … «brilliant code continues» …

Code: Select all

  Dim varDat as Variant
  Dim lngRow as Long, lngRowCount as long
  Dim rng as Range
  
    Set rng = ActiveSheet.Range("A1:D432921")
    lngRowCount = rng.Rows.Count
    varDat = rng.Value2
    For lngRow = 1 to lngRowCount
        If (varDat(lngRow, 3) = "Muffin") Then
            ExecuteBrilliantProcedureOnRow varDat, lngRow
        End If
    Next lngRow
 
    rng.Value2 = varDat
    

Code: Select all

lngRowCount=ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Row

Code: Select all

Sub test()

    Dim rRealUsedRange As Range
    Dim lLastRow As Long
    
    With ActiveSheet.UsedRange
    
        Set rRealUsedRange = .Cells(1).Resize(.Rows.Count, .Columns.Count)
        
        lLastRow = .Cells(1).Row + rRealUsedRange.Rows.Count - 1
        
    End With
    
    MsgBox rRealUsedRange.Address(0, 0)
    MsgBox lLastRow

End Sub