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