Dynamic column size in excel macro

danielpereira — Tue, 18 Aug 2015 21:01:28 GMT

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(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

'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

Re: Dynamic column size in excel macro

petter — Tue, 18 Aug 2015 21:09:51 GMT

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

topic Re: Dynamic column size in excel macro in QlikView

Dynamic column size in excel macro

Re: Dynamic column size in excel macro