Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Excel export macro with formating

Hi Community, I hope you can help.

I have a table which has been formatted in various ways, which when I right click and use the 'Send to Excel' function works great, because the formatting is also sent to Excel.

For reasons which i won't go into, I have to use a macro to export the table though. But the macro i am using doesn't transfer any of the formatting!

How can I edit to macro to ensure the formatting is also exported?

FUNCTION ExcelStatus(CH125)

  set obj = ActiveDocument.GetSheetObject( CH125)

  w = obj.GetColumnCount

  if obj.GetRowCount>1001 then

    h=1000

  else h=obj.GetRowCount

  end if

  Set objExcel = CreateObject("Excel.Application")

  objExcel.Workbooks.Add

  objExcel.Worksheets(1).select()

  objExcel.Visible = True

objExcel.Worksheets(1).Columns(2).ColumnWidth = 0

  objExcel.Worksheets(1).Columns(3).ColumnWidth = 50

    objExcel.Worksheets(1).Columns(4).ColumnWidth = 50

      objExcel.Worksheets(1).Columns(5).ColumnWidth = 50

        objExcel.Worksheets(1).Columns(6).ColumnWidth = 50

          objExcel.Worksheets(1).Columns(7).ColumnWidth = 50

            objExcel.Worksheets(1).Columns(8).ColumnWidth = 50

              objExcel.Worksheets(1).Columns(9).ColumnWidth = 50

                objExcel.Worksheets(1).Columns(10).ColumnWidth = 50

 

  set CellMatrix = obj.GetCells2(0,0,w,h)

  column = 1

  for cc=0 to w-1

   objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text

   objExcel.Cells(1,column).EntireRow.Font.Bold = True

   column = column +1

  next

  c = 1

  r =2

  for RowIter=1 to h-1

    for ColIter=0 to w-1

      objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text

      c = c +1

    next

   r = r+1

   c = 1

next

END FUNCTION

SUB ExportStatus

   ExcelExport( "CH125" )

END SUB

Tags (1)
3 Replies
MVP & Luminary
MVP & Luminary

Re: Excel export macro with formating

Your macro isn't really an export-routine it's more a read and write routine and only on the values which doesn't cover any formattings. Most often you need to adjust formattings directly within excel and how this could be done is better covered in office-foren.

More suitable instead might be to use a copy+paste routine like:

rem ** copy full table to clipboard **

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

to which you find here more: Export Multiple Chart Objects to Excel and Loop by Field

An alternatively especially by complicated formattings could be to use an excel-masterfile which contained all the needs and an export-routine changed only the data within the file.

- Marcus

Not applicable

Re: Excel export macro with formating

I'm  a bit of a beginner at this. So how would your suggestion translate to a module for a button action?

Surely more code would be needed?

MVP & Luminary
MVP & Luminary

Re: Excel export macro with formating

Take a deeper look on the above mentioned link and here: https://community.qlik.com/search.jspa?q=excel+export you will find many more examples.

- Marcus