topic Re: Exporting Pivot with Macro with delimiter in QlikView

Exporting Pivot with Macro with delimiter

Fri, 23 Sep 2016 10:21:46 GMT

Hey everyone,

I am using a Macro to export a pivot table which looks as follows:

My result in Excel is as follows:

What I want is:

This is the Macro I am using:

Sub ExportToExcel

set obj = ActiveDocument.GetSheetObject("CH19")
obj.ExportEx "C:\temp\Stichprobe_File.xls",1

MsgBox ("Export abgeschlossen, Datei unter Pfad C:\temp\... abgelegt")

End Sub

I already tried:

obj.ExportEx "C:\temp\Stichprobe_File.xls", " ", 1

but it is not working at all.

Help required and appreciated.

Thanks in advance.

Daniela

Re: Exporting Pivot with Macro with delimiter

settu_periasamy — Fri, 23 Sep 2016 16:08:53 GMT

Your code is working fine for me. May be try to change format value (Put 5 -> Biff instead of 1)

Sub ExportToExcel

set obj = ActiveDocument.GetSheetObject("CH19")
obj.ExportEx "C:\temp\Stichprobe_File.xls",5
MsgBox ("Export abgeschlossen, Datei unter Pfad C:\temp\... abgelegt")

End Sub

Note:

The above code is not working, check your excel, is there any delimited already set (Default : Tab)

Data -> Text To Columns -> Delimited

Re: Exporting Pivot with Macro with delimiter

el_aprendiz111 — Fri, 23 Sep 2016 16:26:30 GMT

Function Rpt_Excel(objID)

SET Chrt=ActiveDocument.GetSheetObject(objID)

SET x = Chrt.GetProperties

Chrt.SendToExcel

SET Chrt = Nothing

SET x = Nothing

End Function

SUB Enviar_Excel()

CALL Rpt_Excel("CH09")

End Sub

Re: Exporting Pivot with Macro with delimiter

Mon, 26 Sep 2016 06:11:22 GMT

Hi fer fer,

your code works as it gives me the correct format in Excel.

But now it only opens an Excel file with the exported data.

How would I integrate my path into your code so that it would also directly be stored at a defined location.

Before I used:

obj.ExportEx "C:\temp\Stichprobe_File.xls",1

Thanks in advance,

Daniela

Re: Exporting Pivot with Macro with delimiter

Mon, 26 Sep 2016 06:19:29 GMT

Changing the number does not work, unfortunately. Already tried before.

The default in Excel is set to "Tab", so the user could then manually change the layout.

But I would like to have that organized automatically by just pressing the button.

Regards,

Daniela

Re: Exporting Pivot with Macro with delimiter

el_aprendiz111 — Tue, 27 Sep 2016 01:10:42 GMT

Hi Daniela

a new function

REM -----------------------------*BY JALVAREZ- FER FER*------------------------------------------

FUNCTION Export_Excelll(myPath, Obj)

ActiveDocument.ClearAll False

SET XL_App =CreateObject("EXCEL.Application")

XL_App.Visible = TRUE

SET XLDoc = XL_App.Workbooks.Add

SET MyTable = ActiveDocument.GetSheetObject(Obj)

MyMsg = myPath & Obj

MyTable.ExportBiff myPath & Obj & ".xls"

MsgBox myPath & Obj

XL_App.quit()

Set X_LApp = Nothing

Set MyTable = Nothing

SET XLDoc = Nothing

SET XL_App = Nothing

END FUNCTION

rem in button

sub Save_Excel

REM -----------------------------*BY JALVAREZ- FER FER*------------------------------------------

CALL Export_Excelll("C:\Users\jalvarez\Desktop\H\Excell\FOLDER\FLODER_A\", "CH01")

CALL Export_Excelll("C:\Users\jalvarez\Desktop\H\Excell\FOLDER\FLODER_B\", "CH02")

CALL Export_Excelll("C:\excel\", "CH01")

CALL Export_Excelll("C:\chart\", "CH02")

end sub

Re: Exporting Pivot with Macro with delimiter

Tue, 27 Sep 2016 06:59:24 GMT

Hey,

unfortunately, your new code does not work for me.

However, I figured it out myself.

I integrated one row of your first code into my original code, integrated Settus suggestion and now it works just fine.

The code now is:

Sub ExportToExcel

set obj = ActiveDocument.GetSheetObject("CH19")
set x = obj.GetProperties
obj.ExportEx "C:\temp\Stichprobe_File.xls",5

End Sub

Thanks everyone!