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

[gardenierbi]

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.

[nihalbuddy09]

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

[gardenierbi]

If it works then it's okay right? 😉

In your time function you've still the day part available. This can be removed.

I have given you an easy to understand example but it takes some lines. You can get the same result with less code. For example :

MSGBOX "D:\Account_A1_" & cDate(NOW) & "_" & cTime(NOW) & ".xlsx"

Function cDate(myDate)

    cDate = Year(myDate) & "-" & SetDigits(Month(myDate), 2) & "-" & SetDigits(Day(myDate), 2)

End Function

Function cTime(myTime)

    cTime = SetDigits(Hour(myTime), 2) & "-" & SetDigits(Minute(myTime), 2) & "-" & SetDigits(Second(myTime), 2)

End Function

Function SetDigits(myValue, myDigits)

      SetDigits = String(myDigits - Len(myValue), "0") & myValue

End Function

There are many examples available. For example, look at:

Useful Qlikview Macros

[nihalbuddy09]

Thank you Sander..

Regards,

Nihal

[gardenierbi]

If you're happy with my solution mark my reply as the answer for your question please.

[nihalbuddy09]

I am very happy with your suggested solution and it works perfectly but unfortunately I am not the one who opened this question.

I have used this old thread but not sure if migle.purzelyteCan mark this as answered...

[migle_purzelyte]

‌This is not the correct answer because you didn’t answer the initial question. Instead of creating your own topic you just entered and spammed some random topic making community users harder to find the answer they are looking for.

[nihalbuddy09]

Yes, I made a wrong call here. I should have opened the new topic, Sorry about that.

[andrzejdobiegal · Accepted solution]

You can use

sub ChartExport()
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ServerSideExportEx "export\Export.xlsx", ";" , 6
end sub

QV November 2018 SR2

regards Andrzej

[andre_ficken]

In your Qlikview document create a variable eg. vFileDateTime and populate that using timestamp( Now(2), 'YYYY-MM-DD_hh-mm-ss'). In your macro you add these 2 lines: 

VarDate=ActiveDocument.Variables("vFileDateTime").GetContent.String
VarFile="D:\Qlikview\Exports\ExcelData_"&VarDate&".xlsx"

You can use the VarFile variable to name your Excel file

ASheet.SaveAs VarFile

Hope this helps