2 Replies Latest reply: Feb 1, 2011 6:00 AM by Åsa Runström RSS

    Using macro to send to excel

    Åsa Runström

      Hi

      I send table to excel using this code in a macro

      Set obj = ActiveDocument.GetSheetObject(ObjectID)

      obj.CopyTableToClipboard True

      Now I must put border around the table put the problem is that the size of the table change everytime when you use the application. I have no idea how to do it.

        • Using macro to send to excel

          hi,

          You can try this.

           



          Function Correos()

          Set Doc = ActiveDocument
          Set DocProp = Doc.GetProperties
          Directory = DocProp.MyWorkingDirectory

          Doc.GetApplication.Refresh

          Set AppExcel = CreateObject("Excel.Application")

          AppExcel.Visible = True

          AppExcel.WorkBooks.Add()

          Set Gra01 = Doc.GetSheetObject("CH16")
          Set Gra02 = Doc.GetSheetObject("CH17")

          Set Txt01 = Doc.GetSheetObject("TX13")

          // ***************** Se realiza filtro de Negocio y Dummy **********************************************
          ActiveDocument.ClearAll false

          ActiveDocument.Fields("Cia").ToggleSelect "010"
          ActiveDocument.Fields("Cia").ToggleSelect "100"
          ActiveDocument.Fields("Cia").ToggleSelect "500"
          ActiveDocument.Fields("Cia").ToggleSelect "700"

          ActiveDocument.getApplication.sleep 5000

          AppExcel.Sheets.Add()

          Txt01.CopyTextToClipboard
          AppExcel.ActiveSheet.Cells(1,3).Activate
          AppExcel.ActiveSheet.Paste

          AppExcel.ActiveSheet.Name = "Stock PT"
          AppExcel.ActiveSheet.Cells(4,1).Activate
          Gra01.CopyTableToClipboard True
          AppExcel.ActiveSheet.Paste
          AppExcel.ActiveSheet.Columns("B").Delete

          AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#,##0")

          AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40
          AppExcel.ActiveSheet.Columns("A").AutoFit

          ActiveDocument.getApplication.sleep 5000

          Set Gra02 = Doc.GetSheetObject("CH17")
          Gra02.CopyBitMapToClipboard
          AppExcel.ActiveSheet.Cells(17,1).Activate
          Gra02.CopyBitMapToClipboard
          AppExcel.ActiveSheet.Paste

          AppExcel.ActiveSheet.Cells(1,1).Activate

          // ********************* Salva y Cierra el Excel ***************************************

          Ruta = Directory & "\Stock" & Year(Now) & Month(Now) & Day(Now) & " a las " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS"

          AppExcel.ActiveSheet.SaveAs (Ruta)

          End Function