1 Reply Latest reply: Aug 18, 2015 5:09 PM by Petter Skjolden RSS

    Dynamic column size in excel macro

    Daniel Pereira

      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