5 Replies Latest reply: Oct 5, 2016 6:27 AM by Jacek Antek RSS

    How do I open an Excel worksheet within Qlikview?

    roos.niclas

      Hi all,

      I'm trying to create a solution for opening an Excel worksheet within the actual Qlikview application, i.e. not simply doing it by launching Excel and the specific file. I want to open the worksheet with one button, input my values and then save and close the worksheet and reload the qlikview application with another button.

      I've tried a few different macros, but I have to admit that it's not my strong side and in my opinion, the qlikview help could be better in this area!

      Should I be looking at an OCX-control (in the form of a Custom Object) or can this be solved via macros?

      Grateful for any help!

      /Niclas

        • How do I open an Excel worksheet within Qlikview?
          roos.niclas

          Ok,

          After a day of utter frustration and endless pages of VB script, I've decided to try and settle for a solution that simply allows me to launch and open an Excel file with one button, have one button for saving and reloading without closing and finally one button for saving, closing and reloading.

          I've been fiddling around with a couple of different scripts that I found here on the forum and this is my first macro, for opening the excel file:

          Sub Excel()

           

          Set oXL=CreateObject("Excel.Application")

          f_name="C:\ExcelFile.xlsx"

          Set oWB=oXL.Workbooks.Open(f_name)

          oXL.Visible = True

           

          End Sub

          It works like a charm so far! The problem is when I try to compose a script that will save and/or close the previously opened excel file, here is an example:

           

          Sub Close()

          Set oXL=CreateObject("Excel.Application")

          f_name="C:\ExcelFile.xlsx"

          Set oWB=oXL.Workbooks.Open(f_name)

          oWB.Save

          oWB.Close

          oXL.Quit

          ActiveDocument.Reload

          End sub



          I think the problem is that I'm not able to acces the previously created object (oXL) or edit instead of opening the previously opened excel file (Set oWB=oXL.Workbooks.Open(f_name)), thus only saving a a new version of ExcelFile.xlsx!

          Does anybody have any suggestions for how I could handle this? I'm a complete rookie when it comes to VB so any help is greatly appreciated!

          Best regards,



          Niclas

            • Re: How do I open an Excel worksheet within Qlikview?
              Jacek Antek

              Hi Guys,

               

              this code is not working for me.

               

              Sub Excel_Table_Export

              set XLApp = CreateObject("Excel.Application")

              set XLDOC = XLApp.Workbooks.Open ("\\nt-wro7-01\dane\IACC\DOKUMENT\Pliki QlikView\raport Ɓukasz 2.xls")

              XLApp.Visible = True

               

              End Sub

               

              There is nothing after this macro. I would like to only open excel file. and that is all.

               

              Please help,

              Warm regards,

              Jacek Antek

            • How do I open an Excel worksheet within Qlikview?
              Neil Miller

              I'm not sure if you are still working on this problem. I think you need to keep the entire macro up until the reload in one QlikView subroutine. You are correct that once the subroutine ends, QlikView no longer has the references needed to control the Excel file. If you include all of the Excel portions in one sub, you should be to do all of the Excel manipulation you described in your post.

              It may be possible to pass the required objects to another routine, but I'm not sure.

              • How do I open an Excel worksheet within Qlikview?
                jonathan.bowen

                Hi Niclas,
                I had similar issues to you a few months back as my 1st introduction to Qlikview; ie - how to paste charts etc from Qlikview into Excel and also PowerPoint as that is what 'my business' required as a must have!
                Conclusions from my experience are that the Qlikview side is actually not that bad once you get to grips with the api syntax, but on the vba side because you're working from Qlikview it can be a bit tricky to get the vba correct to control specific objects.
                Anyway, the attached is a snippet of a macro I've written that opens an existing Excel template & pastes a chart into an Excel sheet & then saves/names the template based on a couple of variables set up in Qlikview.
                I hope it helps to 'move' you a little bit forward.
                Sub Excel_Table_Export
                set XLApp = CreateObject("Excel.Application")
                set XLDOC = XLApp.Workbooks.Open ("C:\Templates\Data-v0.0.1.xlsx")
                XLApp.Visible = True

                Const XLPasteValues = -4163
                Const xlShiftUp = -4162

                set s=ActiveDocument.Sheets("Excel Exports")
                ActiveDocument.Sheets("Excel Exports").Activate
                ActiveDocument.ClearCache
                ActiveDocument.GetApplication.WaitForIdle
                ActiveDocument.GetSheetObject("CH38").Restore
                ActiveDocument.GetSheetObject("CH38").CopyTableToClipboard true
                set XLSheet = XLDOC.Worksheets("Sheet1")
                XLSheet.Paste XLSheet.Range("A1")

                XLSheet.SaveAs "C:\Data\Analysis-v0.0.1 - " & [mynamevar] & " - " & [mydatevar] & ".xlsx"

                set XLDOC = Nothing
                set XLSheet = Nothing
                set XLRange = Nothing

                End Sub

                Sub autoreload
                ActiveDocument.ClearCache
                ActiveDocument.GetApplication.WaitForIdle
                ActiveDocument.DoReload 2,false,false
                ActiveDocument.ClearCache
                ActiveDocument.GetApplication.WaitForIdle
                End Sub