Exporting Pivot with Macro with delimiter

[#1]

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

[settu_periasamy]

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

like

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

[el_aprendiz111]

HI

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

[#4]

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

[#5]

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

[el_aprendiz111]

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

[#7 · Accepted solution]

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!