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
