Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
danielpereira
New Contributor II

Dynamic column size in excel macro

I'm using macro below and it is working, but the file is generated with default column size insted of dynamic column size (according to text size). How can I fix this problem?

sub SendToExcel

'Set the path where the excel will be saved

filePath = "Z:\Qlikview\Reports\Follow Up.xlsx"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

'First chart object

Set curSheet = curWorkBook.WorkSheets(1)

curWorkBook.Sheets(1).Name = "Follow up"

curWorkBook.Sheets(2).Delete

curWorkBook.Sheets(2).Delete

curWorkBook.Sheets(1).Range("A1").Select

'loop

chartArray = Array("CHTabelalDinamica")

usedRows=0

For Each chart In chartArray

   Set i = ActiveDocument.GetSheetObject(chart)

    chartCaption = i.GetCaption.Name.v

    curSheet.Cells(usedRows+1, 1)=chartCaption

     i.CopyTableToClipboard true

     curSheet.Cells(usedRows+2, 1).Select

     curSheet.Paste

     usedRows=curSheet.UsedRange.Rows.Count+3 '--->function to get the first unused row

Next

'loop end

excelFile.Visible = true

'Save the file and quit excel

     curWorkBook.SaveAs filePath

     curWorkBook.Close

     excelFile.Quit

     'Cleanup

     Set curWorkBook = nothing

     Set excelFile = nothing

end sub

1 Reply
MVP
MVP

Re: Dynamic column size in excel macro

You can use something like this:

Worksheets("Sheet1").Columns("A:I").AutoFit

AutoFit is a method that can be used on an Excel Range object. So maybe:

curSheet.UsedRange.AutoFit