topic Re: Excel export macro with formating in QlikView

Excel export macro with formating

Thu, 05 Nov 2015 12:17:43 GMT

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

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

r = r+1

c = 1

END FUNCTION

SUB ExportStatus

ExcelExport( "CH125" )

END SUB

Re: Excel export macro with formating

marcus_sommer — Thu, 05 Nov 2015 14:01:35 GMT

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

Re: Excel export macro with formating

Thu, 05 Nov 2015 14:14:24 GMT

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?

Re: Excel export macro with formating

marcus_sommer — Thu, 05 Nov 2015 14:25:53 GMT

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