6 Replies Latest reply: Sep 27, 2016 2:59 AM by Daniela Meyersieck RSS

    Exporting Pivot with Macro with delimiter

    Daniela Meyersieck

      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 Periyasamy

          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

            • Re: Exporting Pivot with Macro with delimiter
              Fer Fer

              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

                • Re: Exporting Pivot with Macro with delimiter
                  Daniela Meyersieck

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

                      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

                        • Re: Exporting Pivot with Macro with delimiter
                          Daniela Meyersieck

                          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!

                    • Re: Exporting Pivot with Macro with delimiter
                      Daniela Meyersieck

                      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