0 Replies Latest reply: Aug 4, 2010 3:35 PM by ptung17 RSS

    Export to Excel with macro - failed with selections

      Hi,

      The application has a button that generates excel reports.

      When nothing selected, it worked fine.

      But once selection is made, it shows "Macro parse failed.."

      I don't understand for the same code, why does "with or without selections" give different results?

      Here is my code

       

       

       

       


      Sub ExportToXL
      ' Set Excel App
      set XLApp = CreateObject("Excel.Application") ' Define Object
      XLApp.Visible = True 'Visible set as true
      set XLDoc = XLApp.Workbooks.Add 'Open new workbook
      ' Get TB2
      set table = ActiveDocument.GetSheetObject("CH162")
      ' Copy table and paste into Excel
      set XLSheet = XLDoc.Worksheets(1)
      table.CopyTableToClipboard true
      XLSheet.Paste XLSheet.Range("A1")
      Set xlRange1 = XLSheet.Columns("C:C")
      xlRange1.NumberFormat = "#,##0"
      Set xlRange2 = XLSheet.Columns("D:D")
      xlRange2.NumberFormat = "#,##0"
      Set xlRange3 = XLSheet.Columns("E:E")
      xlRange3.NumberFormat = "#,##0"
      Set xlRange4 = XLSheet.Columns("F:F")
      xlRange4.NumberFormat = "#,##0"
      Set xlRange5 = XLSheet.Columns("H:H")
      xlRange5.NumberFormat = "#,##0"
      XLSheet.Cells.EntireRow.RowHeight = 15
      XLSheet.Range("A1:A60000").ColumnWidth = 16
      XLSheet.Range("B1:B60000").ColumnWidth = 15
      XLSheet.Range("C1:C60000").ColumnWidth = 15
      XLSheet.Range("D1:D60000").ColumnWidth = 15
      XLSheet.Range("E1:E60000").ColumnWidth = 15
      XLSheet.Range("F1:F60000").ColumnWidth = 15
      XLSheet.Range("G1:G60000").ColumnWidth = 15
      XLSheet.Range("H1:H60000").ColumnWidth = 15
      XLSheet.Range("I1:I60000").ColumnWidth = 15
      XLsheet.PageSetup.Orientation = 2 'Landscape
      XLsheet.PageSetup.LeftMargin = xlApp.CentimetersToPoints(1)
      XLsheet.PageSetup.RightMargin = xlApp.CentimetersToPoints(1)
      XLsheet.PageSetup.HeaderMargin = xlApp.CentimetersToPoints(0.5)
      XLsheet.PageSetup.TopMargin = xlApp.CentimetersToPoints(1.3)
      XLsheet.PageSetup.BottomMargin = xlApp.CentimetersToPoints(1.3)
      XLsheet.PageSetup.FooterMargin = xlApp.CentimetersToPoints(0.5)
      XLSheet.PageSetup.Zoom = False
      XLSheet.PageSetup.FitToPagesWide = 1
      XLSheet.PageSetup.FitToPagesTall = 100
      XLSheet.PageSetup.PrintTitleRows = xlSheet.Rows(1).Address
      XLSheet.PageSetup.PrintGridlines = True
      XLSheet.name = "Region"

      end sub