17 Replies Latest reply: Sep 25, 2018 9:09 AM by nihal n RSS

    Macro: Export as xlsx

    Migle Purzelyte

      Hello,

       

      I wonder what is VBScript function to export table as xlsx in Qlikview November 2017.

      For xls I was using ExportBiff but I cannot find what to use for xlsx.

       

      Thanks in advance,

      Migle

        • Re: Macro: Export as xlsx
          youssef belloum

          Hi,

           

          if i'm not wrong, you can only export on xlsx with the latest release..

           

          mto maybe can confirm this ?

          • Re: Macro: Export as xlsx
            Peter Cappelle

            Hi Everyone,

             

            This is how I do the export to xlsx in QV (november release).

             

            sub Export

             

            set obj = ActiveDocument.GetSheetObject("CH1")

             

            set objExcel = CreateObject("Excel.Application")

            objExcel.Visible = False

            objExcel.Workbooks.Add

             

            Set ASheet = objExcel.ActiveSheet

            ASheet.Application.DisplayAlerts = False

            ASheet.Range("A1").Select

            obj.CopyTableToClipboard true

            ASheet.Paste

            ASheet.SaveAs "c:\test.xlsx"

             

            objExcel.Quit

             

            end sub

              • Re: Macro: Export as xlsx
                Sander Gardenier

                This will only work if MS Excel is installed on your machine. On most Qlik servers this isn't the case.

                • Re: Macro: Export as xlsx
                  nihal n

                  Hi Peter,

                   

                  Thank you for posting the macro which I am going to use to export the data into .xlsx file format. But in my requirement while saving the file I need the today's date and timestamp along with the file name.

                   

                  I have to run the macro multiple times in a day so that I will have a new file every time, can you please help me out how to pass the date and timestamp in this macro.


                  example file names:

                  Account_A1_2018-09-23_05-30-05.xlsx

                  Account_A1_2018-09-23_15-20-15.xlsx


                  Thanks,

                  Nihal


                    • Re: Macro: Export as xlsx
                      Sander Gardenier

                      You can use something like this:

                       

                      myFile = "Account_" & cDate(DATE) & ".xls"

                       

                      FUNCTION cDate(myDate)


                          strYear = YEAR(myDate)

                        

                          IF LEN(MONTH(myDate)) = 1 THEN

                              strMonth = "0" & MONTH(myDate)

                          ELSE

                              strMonth = MONTH(myDate)

                          END IF

                        

                          IF LEN(Day(myDate)) = 1 THEN

                              strDay = "0" & Day(myDate)

                          ELSE

                              strDay = Day(myDate)

                          END IF

                        

                          cDate = strYear & "-" & strMonth & "-" & strDay


                      END FUNCTION


                      And then the same for the time.

                        • Re: Macro: Export as xlsx
                          nihal n

                          Hi Sander,

                           

                          Thank you very much for your time and helping me on this. This is my first time to use Macros and not much familiar with it.

                           

                          I have tried this way and can you please have a look and advise if this is correct as per the code.

                           

                          Right now I am getting the file name as: Account_A1_2018-09-23_11-38-15.xlsx

                           

                           

                          myFile = "Account_" & cDate(DATE) & ".xlsx"

                           

                          FUNCTION cDate(myDate)

                              strYear = YEAR(myDate)

                              IF LEN(MONTH(myDate)) = 1 THEN

                                  strMonth = "0" & MONTH(myDate)

                              ELSE

                                  strMonth = MONTH(myDate)

                              END IF

                              IF LEN(Day(myDate)) = 1 THEN

                                  strDay = "0" & Day(myDate)

                              ELSE

                                  strDay = Day(myDate)

                              END IF

                              cDate = strYear & "-" & strMonth & "-" & strDay

                           

                          END FUNCTION

                           

                           

                          myFile = "Account_" & cTime(Now) & ".xlsx"

                           

                          FUNCTION cTime(myTime)

                              strHour = Hour(myTime)

                              IF LEN(Hour(myTime))= 1 THEN

                                  strHour = "0" & Hour(myTime)

                              ELSE

                                  strHour = Hour(myTime)

                              END IF

                              strMinute = Minute(myTime)

                              IF LEN(Minute(myTime))= 1 THEN

                                  strMinute = "0" & Minute(myTime)

                              ELSE

                                  strMinute = Minute(myTime)

                              END IF

                              strSecond = Second(myTime)

                              IF LEN(Minute(myTime))= 1 THEN

                                  strSecond = "0" & Second(myTime)

                              ELSE

                                  strSecond = Second(myTime)

                              END IF

                              IF LEN(Day(myTime))= 1 THEN

                                  strDay = "0" & Day(myTime)

                              ELSE

                                  strDay = Day(myTime)

                              END IF

                              cTime = strHour & "-" & strMinute & "-" & strSecond

                           

                          END FUNCTION

                           

                           

                           

                           

                          sub exportExel

                           

                           

                          set obj = ActiveDocument.GetSheetObject("CH05")

                           

                          set objExcel = CreateObject("Excel.Application")

                           

                          objExcel.Visible = False

                           

                          objExcel.Workbooks.Add

                           

                          Set ASheet = objExcel.ActiveSheet

                           

                          ASheet.Application.DisplayAlerts = False

                           

                          ASheet.Range("A1").Select

                           

                          obj.CopyTableToClipboard true

                           

                          ASheet.Paste

                           

                          ASheet.SaveAs "D:\Account_A1_" & cDate(DATE) & "_" & cTime(Now) & ".xlsx"

                           

                          objExcel.Quit

                           

                          end sub

                           

                           

                          Thanks,

                          Nihal