topic Re: Macro: Export as xlsx in QlikView

Macro: Export as xlsx

migle_purzelyte — Mon, 27 Nov 2017 16:14:53 GMT

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

YoussefBelloum — Mon, 27 Nov 2017 16:22:34 GMT

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

Michael_Tarallo — Mon, 27 Nov 2017 16:29:28 GMT

Hi Migle / Youssef - I don't have this setup - but I believe it is the same - just now supports .xlsx - in the past - it would create a .xlsx - but there would be an error opening the file. Can you give it a try using the extension .xlsx and let us know?

Thanks

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

Re: Macro: Export as xlsx

migle_purzelyte — Mon, 27 Nov 2017 16:36:31 GMT

Using ExportBiff with xlsx extension creates invalid file.

Re: Macro: Export as xlsx

gardenierbi — Wed, 28 Feb 2018 14:05:19 GMT

Hi migle.purzelyte,

Did you find a solution for this?

Re: Macro: Export as xlsx

migle_purzelyte — Wed, 28 Feb 2018 15:13:05 GMT

Unfortunately still no

Miglė Purželytė

Business IQ

Re: Macro: Export as xlsx

gardenierbi — Thu, 01 Mar 2018 15:37:36 GMT

Hi mto‌,

but I believe it is the same - just now supports .xlsx - in the past - it would create a .xlsx - but there would be an error opening the file. Can you give it a try using the extension .xlsx and let us know?

The ExportBiff function still generates a document with the old .XLS format. I get the same result as migle.purzelyte‌. Is there maybe a new function available in the API to get the new .XLSX format as output?

We appreciate your help for this. We aren't the only one with this question.

Regards,

Sander

Re: Macro: Export as xlsx

petercappelle — Fri, 23 Mar 2018 10:59:42 GMT

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

gardenierbi — Fri, 23 Mar 2018 11:45:21 GMT

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

nihalbuddy09 — Sat, 22 Sep 2018 18:45:54 GMT

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

gardenierbi — Sat, 22 Sep 2018 19:13:36 GMT

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

nihalbuddy09 — Sun, 23 Sep 2018 10:53:59 GMT

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

Re: Macro: Export as xlsx

gardenierbi — Sun, 23 Sep 2018 11:32:55 GMT

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

Re: Macro: Export as xlsx

nihalbuddy09 — Mon, 24 Sep 2018 17:34:39 GMT

Thank you Sander..

Regards,

Nihal

Re: Macro: Export as xlsx

gardenierbi — Mon, 24 Sep 2018 17:46:09 GMT

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

Re: Macro: Export as xlsx

nihalbuddy09 — Tue, 25 Sep 2018 11:55:40 GMT

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...

Re: Macro: Export as xlsx

migle_purzelyte — Tue, 25 Sep 2018 12:48:01 GMT

‌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.

Re: Macro: Export as xlsx

nihalbuddy09 — Tue, 25 Sep 2018 13:09:30 GMT

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

Re: Macro: Export as xlsx

andrzejdobiegal — Tue, 07 May 2019 21:24:59 GMT

You can use

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

QV November 2018 SR2

regards Andrzej

Re: Macro: Export as xlsx

andre_ficken — Thu, 28 Jan 2021 17:52:45 GMT

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