Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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