Export to Excel with macro - failed with selections

Wed, 04 Aug 2010 19:35:53 GMT

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

topic Export to Excel with macro - failed with selections in QlikView

Export to Excel with macro - failed with selections