topic Re: Column width for Excel export macro in QlikView

Column width for Excel export macro

Thu, 29 Oct 2015 09:43:13 GMT

How can I edit my macro to export a table to excel so that Columns C-J have a width of 250?

FUNCTION ExcelExport(CH176)

set obj = ActiveDocument.GetSheetObject( CH176)

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

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 ExportKanban

ExcelExport( "CH176" )

END SUB

Re: Column width for Excel export macro

jzimolong — Thu, 29 Oct 2015 10:00:17 GMT

See if this post can help answer your question:

https://community.qlik.com/thread/16718

Re: Column width for Excel export macro

Thu, 29 Oct 2015 10:01:59 GMT

thanks, but i have seen that and am not sure how to get it to work in my script

It didn't work when i tried

Re: Column width for Excel export macro

jzimolong — Thu, 29 Oct 2015 10:17:51 GMT

I put the following into one of my macros and it worked:

xlSheet.Columns(1).ColumnWidth = 250

Just number the columns accordingly.

Re: Column width for Excel export macro

Thu, 29 Oct 2015 10:30:48 GMT

where in the script did you insert that line?

doesn't seem to work. I'm kind of guessing where it's supposed to go

Re: Column width for Excel export macro

jzimolong — Thu, 29 Oct 2015 10:52:38 GMT

FUNCTION ExcelExport(CH176)

set obj = ActiveDocument.GetSheetObject( CH176)

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

'Column C

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

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

Re: Column width for Excel export macro

Thu, 29 Oct 2015 10:54:30 GMT

thanks!