9 Replies Latest reply: Aug 4, 2016 1:37 PM by Marcel van den Dobbelsteen RSS

    Create Excel Files via Macro on QlikView Server

      Hey all,

       

      I was searching the forum, but couldn't find a related post to my question. I really hope you can help me.

       

      Here is the situation: The user clicks on a button. The button triggers a macro opens an excel file on our server, edits it and after that saves it into a specific folder. This works really fine with QlikView Desktop. But on our QlikView Server the macro does absolutely nothing. It never creates an Excel.Application Object nor does it save an excel file into a folder.

      This is a short vbscript code just for testing:

      Sub test

      Set xls = CreateObject("EXCEL.APPLICATION")

      Set wb = xls.Workbooks.Open ("D:\Mediaplanvorlagen\Mediaplan Vorlage_SollIst.xls")
      xls.Visible = true

      xls.ActiveWorkbook.SaveAs "D:\Mediaplan.xls"
      xls.Quit

      End Sub

       

      Excel is installed on the server. We use the QlikView server version 10.0 x64. "Allow macro execution on server" and "Allow unsafe macro execution on server" are both enabled.

      Can anyone test the code above with some example excel-files? Or is there any reason why it doesn't work?

       

      Greetings

      Daniel

        • Create Excel Files via Macro on QlikView Server

          Hi

          Have you mark

          Requested module security = System access

          Current local security=Allow system access

          in Edit module?

          Regards

          Anders

          • Create Excel Files via Macro on QlikView Server

            Hi

            I have this macro that creates a new excel-document and it´s work.

            Regards

            Anders

            Sub SaveToExcel


            NewFileName = "C:\QlikView\Test.xlsx"
            set obj = ActiveDocument.GetSheetObject("CH18")
            set XLApp = CreateObject("Excel.Application")
            XLApp.Visible = true
            set XLDoc = XLApp.Workbooks.Add
            set rngStart = XLDoc.Sheets(1).Range("A1")
            set XLSheet = XLDoc.Worksheets("Sheet1")
            Const xlCenter = -4108

            obj.CopyTableToClipboard true
            XLDoc.Sheets("Sheet1").Paste()
            XLDoc.WorkSheets("Sheet1").Cells.select
            XLDoc.WorkSheets("Sheet1").Cells.EntireRow.RowHeight = 12.75
            XLDoc.WorkSheets("Sheet1").Cells.EntireColumn.AutoFit


            set Selection = XLSheet.Columns("A:H")
            With Selection
            .VerticalAlignment = xlCenter
            .Borders.ColorIndex = 0 'black border
            End With
            XLSheet.Name = "Test"

            XLDoc.SaveAs NewFileName

            XLApp.Quit

            end sub

              • Create Excel Files via Macro on QlikView Server

                Hi Anders,

                 

                thank you for your code. It also works with QlikView Desktop, but unfortunately, when I try it on AJAX Zero Footprint Client on QlikView Server, the code doesn't work. This is the whole problem. I can create and edit simple textfiles, but not excel files.

                 

                This code works with AJAX ZfC:

                Sub textfile

                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set objFile = objFSO.CreateTextFile("D:\test.txt")

                End Sub

                 

                I guess AJAX doesn't allow Excel.Application Objects :/

                  • Create Excel Files via Macro on QlikView Server
                    s j

                    It will work only in IE Plug In.

                      • Create Excel Files via Macro on QlikView Server

                        Hey,

                         

                        thank you for your help. I understood that you have limited macro functionality with the AJAX ZfC. But I found a solution!

                        If you want to export your data into a specific Excel file and Excel automatically do something with the data, you can do the following:

                         

                        In your QlikView Macro export the data by writing them into a txt file with the "FileSystemObject" properties. With the "WScript.Shell" Object you then can open an Excel file. In that Excel file you have to write a macro which is automatically triggered by opening [Workbook Open event]. Then this macro has to open the txt file with the exported data, writes in into a sheet and finally you have successfully exported the data into excel. The excel-macro can now work with that and create charts, overviews, reports or anything else you want to create.

                        Hope you understood, what I tried to explain. Smile

                         

                        Daniel

                  • Create Excel Files via Macro on QlikView Server

                    Hi

                    I use IE Plugin.

                    Regards

                    Anders

                    • Re: Create Excel Files via Macro on QlikView Server
                      Jason Rathgeber

                      Thanks. I worked on this same issue for ages and have settled on using the iePlugin. Works now. Actually the iePlugin makes everything look on the web look a lot more like QV desktop ...