Embed a Dynamic Qlik Sense Visualization in MS Excel

    I will use Qlik Sense Desktop version for this demo, but I am sure you can also use Enterprise version if you have the authorization to the Dev Hub.

    The first step, open an excel file where you want your qlik visualization to be, make sure saved it as xlsm format (Macro Enabled file)

     

    Step2, ALT + F11 to open Developer window in excel, and create a user form. Pick the Web Browser part from Toolbox and reshape the size to fit your user form

    Capture.JPG

    Step3, Go to  Dev Hub in QLik Sense

    Capture.JPG

    then go to Single Configurator, pick the App and the Sheet, then the URL will be generated in the box, Copy the URL and paste to a cell in the excel file.

    Capture.JPG

     

    Step4, Build some buttons in excel like below, the up and down buttons are controlling 12 period ,and 5 weeks(most periods have 4 weeks, some have 5 weeks)

    Capture.JPG

     

    Step5, Change the Cell where stores your URL info into a formula like below. the cell B2 and G2 refer to the period number and week number.

    ="http://localhost:4848/single?appid=C%3A%5CUsers%5Cilin%5CDocuments%5CQlik%5CSense%5CApps%5CCorperate%20KPI.qvf&sheet=hpjwm&opt=nointeraction&select=Period,"&Sheet1!B2&"&select=PdWeek,"&Sheet1!G2

     

    Step6, return to VBA developer window, and paste the code below to Sheet1

    Private Sub CommandButton2_Click()

    Unload UserForm1

    UserForm1.Show False

    End Sub

     

    and then go to the Userform code section, and paste the code below.

    Private Sub UserForm_Initialize()

    Me.StartUpPosition = 0

        Me.Top = Application.Top + 25

        Me.Left = Application.Left + Application.Width - Me.Width - 25

    Me.WebBrowser1.Navigate (Sheet2.Range("A1"))

    End Sub

     

    Step7, Test your result. Basically, when you spin the button changing period and week number, the visualizations changes accordingly.

    below is the screen I have on my side, it is fully interactive with Qlik. you see the filters update with my selections in excel.

    Capture.JPG

    I tested this method with enterprise version, and it worked too, but you need to key in user name and password to be able show the result, you are only required to key in credential once.

     

    Above is the thought on embedding analytics to other tools, if you have different thoughts and comments, please share with me as well.

     

    Many thanks

     

    Isaac