11 Replies Latest reply: Jan 23, 2015 2:48 AM by Raymond Stone RSS

    Macro for exporting sheets and objects as jpg

      Hi,

       

      I need to export different sheets and objects as jpg. I have this script today:

       

      sub ExportBitmapToFile
      GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
      ActiveDocument.Sheets("SH15").ExportBitmapToFile GraphPath & "Ekonomi_Alla.jpg"
      end sub


      sub ExportBitmapToFile_2
      GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
      ActiveDocument.Sheets("SH16").ExportBitmapToFile GraphPath & "Ekonomi_Detaljer.jpg"
      end sub


      sub ExportBitmapToFile_3
      GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
      ActiveDocument.GetSheetObject("CH115").ExportBitmapToFile GraphPath & "Ekonomi_Totalt.jpg"
      end sub

       

       

      But when this is run (OnPostReload) only the active sheet, or object from active sheet are exported. Can I activate sheet in some way to be able to export things on different sheets no matter how the app is saved?

       

       

      Best regards

       

      Torbjörn Ungvall (@Ungvall)

      Senior Business Discovery Manager

      Advectas AB

        • Re: Macro for exporting sheets and objects as jpg
          Miguel Angel Baeyens de Arce

          Hi Torbjörn,

           

          Indeed, you need to activate each sheet before exporting.

           

          For sheet = 1 to 12 Step 1
          
               sheetno = "SH" & sheet 
               ActiveDocument.Sheets(sheetno).Activate
               set t=ActiveDocument.ActiveSheet
          
               ' Do printing here calling to the functions you already have 
               ' Using t.Anything instead of ActiveDocument.Sheets
          
          Next
          

           

          Hope that helps.

           

          Miguel

            • Re: Macro for exporting sheets and objects as jpg

              Hi and thanks,

               

              I don't get the syntax - I guess not like this:

               

              For sheet = 9 to 11 Step 1

                    sheetno = "SH" & sheet
                    ActiveDocument.Sheets(sheetno).Activate
                    set t=ActiveDocument.ActiveSheet

              sub ExportBitmapToFile
              GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
              t.Anything("SH15").ExportBitmapToFile GraphPath & "Ekonomi_Alla.jpg"
              end sub


              sub ExportBitmapToFile_2
              GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
              t.Anything("SH16").ExportBitmapToFile GraphPath & "Ekonomi_Detaljer.jpg"
              end sub


              sub ExportBitmapToFile_3
              GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
              t.Anything.GetSheetObject("CH115").ExportBitmapToFile GraphPath & "Ekonomi_Totalt.jpg"
              end sub

              Next

               

              Best regards

               

               

              Torbjörn Ungvall (@Ungvall)

              Senior Business Discovery Manager

              Advectas AB

                • Re: Macro for exporting sheets and objects as jpg
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  My bad, probably this is closer (although untested) to your needs:

                   

                  Sub ExportBitmapToFile
                       ActiveDocument.Sheets("SH15").Activate
                       Set t=ActiveDocument.ActiveSheet
                       GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                       t.ExportBitmapToFile GraphPath & "Ekonomi_Alla.jpg"
                  End Sub
                  
                  Sub ExportBitmapToFile_2
                       ActiveDocument.Sheets("SH16").Activate
                       Set t=ActiveDocument.ActiveSheet
                       GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                       t.ExportBitmapToFile GraphPath & "Ekonomi_Detaljer.jpg"
                  End Sub
                  
                  Sub ExportBitmapToFile_3
                       '' SHXX is the SH ID where CH115 is
                       ActiveDocument.Sheets("SHXX").Activate
                       Set t=ActiveDocument.ActiveSheet
                       GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                       t.GetSheetObject("CH115").ExportBitmapToFile GraphPath & "Ekonomi_Totalt.jpg" 
                  End Sub
                  

                   

                  Hope that helps.

                   

                  Miguel

                    • Re: Macro for exporting sheets and objects as jpg

                      Hm - this was a bit strange. I only get the first one, and when I change the order - none...

                       

                      Sub ExportBitmapToFile_3
                            ActiveDocument.Sheets("SH14").Activate
                            Set t=ActiveDocument.ActiveSheet
                            GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                            t.GetSheetObject("CH115").ExportBitmapToFile GraphPath & "Ekonomi_Totalt.jpg"
                      End Sub

                      Sub ExportBitmapToFile
                            ActiveDocument.Sheets("SH15").Activate
                            Set t=ActiveDocument.ActiveSheet
                            GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                            t.ExportBitmapToFile GraphPath & "Ekonomi_Alla.jpg"
                      End Sub

                      Sub ExportBitmapToFile_2
                            ActiveDocument.Sheets("SH16").Activate
                            Set t=ActiveDocument.ActiveSheet
                            GraphPath = "X:\Development\Eskilstuna Energi och Miljö AB\Export\"
                            t.ExportBitmapToFile GraphPath & "Ekonomi_Detaljer.jpg"
                      End Sub

                       

                       

                      Best regards

                       

                      Torbjörn Ungvall (@Ungvall)

                      Senior Business Discovery Manager

                      Advectas AB

                • Re: Macro for exporting sheets and objects as jpg

                  Please note that this function is NOT creating JPG files, it always exports to BMP (Bitmap) format irrespective of what you call the file. You can verify this for yourself but looking at the file in a hex editor and seeing that the header records conform to BMP file structure and NOT JPG