topic Re: Qvw file data exporting to excel via Macro creating problem. in Qlik Automate

Qvw file data exporting to excel via Macro creating problem.

rruthan — Wed, 06 Mar 2024 04:41:03 GMT

Dear All, I am new with Qlik view & don't know about much of QlikView automation.

I found one article where we can export the data from qvw file to xlsx format with the help of post reload macro, i tried the same macro and getting correct data in excel till not making other copy/paste job on my local system, once we copy any other text from my system then the same qvw file generating last copied text in excel file.

Request you to all, please support me to make this automation successful, please refer the attached Macros, before and after exported excel.

Macros --

sub Export
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = True
Set xlDoc = objExcel.Workbooks.Add
Set xlSheet = xlDoc.Worksheets("Sheet1")
set obj = ActiveDocument.GetSheetObject("CH03")
xlDoc.WorkSheets("Sheet1").Range("A:I").EntireColumn.NumberFormat = "@"
obj.CopyTextToClipboard
objExcel.Range("A2").PasteSpecial
xlDoc.SaveAs "D:\Master_Data\Data\Master Data.xlsx"
xlDoc.Close
end sub

Re: Qvw file data exporting to excel via Macro creating problem.

marcus_sommer — Thu, 07 Mar 2024 10:48:55 GMT

Using the clipboard worked mostly fine if these actions are directly performed. In any automated scenarios it has some risks because the clipboard is a system service in which always the last call wins.

More stable are solutions which export the data, for example like:

set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportBiff "C:\test.xls"

More background and examples could you find within the APIGuide.qvw within your install folder of QlikView.

Re: Qvw file data exporting to excel via Macro creating problem.

rruthan — Fri, 08 Mar 2024 04:26:49 GMT

Hi Marcus, thank you for your reply, & really this is very helpful for me.

Just one more question in that, can we export data in .xlsx format instead of .xls, because my data is having more than 1.60 lakhs rows.

Re: Qvw file data exporting to excel via Macro creating problem.

marcus_sommer — Fri, 08 Mar 2024 07:31:57 GMT

Instead of ExportBiff you may try ExportEx with 6 or a higher number for the parameter for the export file-format. In my APIGuide related to QV 11 it's not documented but I assume this option should be available. By using ServerSideExportEx it's also not documented but it worked definitely:

set tb = ActiveDocument.GetSheetObject("CH01")
tb.ServerSideExportEx "C:\test.xlsx" , ";" , 6 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF

Re: Qvw file data exporting to excel via Macro creating problem.

rruthan — Fri, 08 Mar 2024 09:14:18 GMT

Wow..!!

you are genius, thank you for your wonderful reply because this resolved my problem, which i was facing since long time. Once Again thank you for your support.