ALM analysis view customise excel reporting

Hey Folks,

Automation reporting through ALM for daily reporting, measures can be automated in ALM only by follow below process.

• Log into ALM using your username and password
• From the menu on the left hand side of theALM user interface click on the Dashboard dropdown Or  Press Ctrl + Shift + V
• Click on Analysis View
• On the public/private folder click on the drop down
• Click on ‘Plus’ icon and click on create excel report, give appropriate report name.
• Click on right hand side of “Details” and fill the appropriate data for which purpose you are creating report
• Click on right hand side of “Configuration” and go to “Query”
• Write any appropriate query using query builder (Shows at right side list of tables ofALM) ,test and run the query  which require for reporting for example, select * from defect where defected=’1’
• If we want to run multiple queries for the reports, we can add it on the same excel report. However, when we generate the reports result data shows in different sheet of excel file
• Click on Generate at the top of the page
• You will now be presented with the Save Report Screen. Give your File a name. Choose the place where you would like to save your report to, in the example shown the report will be saved to the Desktop. Click on Save
• Your report will now begin to generate this will take between 1-3 mins depending on the amount of test cases you have.
• Once generated you will get a completion message like this one but with the file name you have given it
• Click on “Post processing”, where you can write the post processing actions like macro for some format, formula etc
• User can give fix path of generate report using “Generation Setting”

Paste Shape using Excel Macro

Hi All,

Sometimes need to paste couple of shapes or logo or image to other sheet using macro to another file or sheet, use below code.

Function fnpaintLogo(strSheetName,strCellNumtoPaste)

  'Sheets("Estimate Response Form").Select

  Sheets(strSheetName).Select

    Range("L1").Select

  ActiveCell.FormulaR1C1 = "C:\Users\trupti.jethva\Documentsmacros\newlogo.png"

    On Error Resume Next

    Application.ScreenUpdating = False

    ' Set to the range of cells you want to change to pictures

    Set Rng = ActiveSheet.Range("L1")

    For Each cell In Rng

        Filename = cell

        ' Use Shapes instead so that we can force it to save with the document

        Set theShape =ActiveSheet.Shapes.AddPicture( _

            Filename:=Filename, linktofile:=msoFalse, _

            savewithdocument:=msoCTrue, _

            Left:=cell.Left, Top:=cell.Top, Width:=950, Height:=950)

        If theShape Is Nothing Then GoTo isnill

        With theShape

             .Name = "Logo"

            .LockAspectRatio = msoTrue

            ' Shape position and sizes stuck to cell shape

            .Top = cell.Top + 50

            .Left = cell.Left + 50

            .Height = cell.Height + 50

            .Width = cell.Width + 50

            ' Move with the cell (and size, though that is likely buggy)

            .Placement = xlMoveAndSize

        End With

        ' Get rid of the

        cell.ClearContents

isnill:

        Set theShape = Nothing

        Range("J1").Select

    Next

    Application.ScreenUpdating = True

    Debug.Print "Done " & Now

    ActiveSheet.Shapes.Range(Array("Logo")).Select

    ActiveSheet.Shapes("Logo").PictureFormat.Brightness = 1

    Selection.Cut

    Range(strCellNumtoPaste).Select

    ActiveSheet.Paste

    Selection.ShapeRange.IncrementTop 9

    Range("A1").Select

   ' Range("L2").Clear

End Function

Spell Check functionality using Macro

Hi folks

In excel file many times people enter wrong data on some template and need to check the spell. We can add macro button and add the below code.

can do that by adding Macro button on it and add the below code.

Sub SpellCheck_Click()

    

    Dim WorkRange As Range

    Dim FoundCells As Range

    Dim cell As Range

    Set WorkRange = ActiveSheet.UsedRange

    For Each cell In WorkRange

        If cell.Locked = False Then

            If FoundCells Is Nothing Then

                Set FoundCells = cell

            Else

                Set FoundCells = Union(FoundCells, cell)

            End If

        End If

    Next cell

    If FoundCells Is Nothing Then

        MsgBox "All cells are locked."

    Else

        FoundCells.CheckSpellingCustomDictionary:="CUSTOM.DIC", _

IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081

    End If

    

End Sub