Macro, excel export and calculations

[Anonymous]

Hello, I´ve written a QV macro to export a chart to excel and do some calculations in excel, but I get an error message on rows commented with 'Error below. What I want to do is to autofill the formula created in the excel workbook cell D23, to the cells D23:K24. Is it possible to do this from a macro executed in QlikView? If there is a way to trigger a macro in excel that could be ok also. (the attached workbook shows what I want to do)

Sub ExportToExcel_Arsrapport_Elnat

On Error Resume Next

Set xlApp = createobject("Excel.application")

xlApp.Visible = True

Set xlWBook = xlApp.Workbooks.add

Set xlWSheet = xlWBook.Worksheets(1)
    xlWSheet.Name = "Rapport"
   
    ActiveDocument.GetSheetObject("CH194").CopyTableToClipboard true
xlWSheet.Range("A1").Select
  xlWSheet.Paste
 
  xlWSheet.Range("D23").Select
    xlWSheet.ActiveCell.FormulaR1C1 = "=R22C/R22C3*RC3"
    xlWSheet.Range("D23").Select
    xlWSheet.Selection.AutoFill Destination:=Range("D23:K23"), Type:=xlFillDefault     'Error
    xlWSheet.Range("D23:K23").Select
    xlWSheet.Selection.AutoFill Destination:=Range("D23:K24"), Type:=xlFillDefault     'Error
    xlWSheet.Range("D23:K24").Select
    xlWSheet.Range("D25").Select
    xlWSheet.ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    xlWSheet.Range("D25").Select
    xlWSheet.Selection.AutoFill Destination:=Range("D25:K25"), Type:=xlFillDefault     'Error
    xlWSheet.Range("D25:K25").Select

    Set xlWSheet = Nothing
    Set xlWBook = Nothing
    Set xlApp = Nothing
   
End Sub

[marcus_sommer]

I think xlFillDefault is a constante which probably only within vba worked - try it with its numeric value:

Name	Wert
xlFillCopy	1
xlFillDays	5
xlFillDefault	0
xlFillFormats	3
xlFillMonths	7
xlFillSeries	2
xlFillValues	4
xlFillWeekdays	6
xlFillYears	8
xlGrowthTrend	10
xlLinearTrend	9

- Marcus

[Anonymous]

Didn´t work either, unfortunately. Actually I saw now that the first calculation (xlWSheet.ActiveCell.FormulaR1C1 = "=R22C/R22C3*RC3") wasn´t even performed.

[marcus_sommer]

Try it with another Formula-Property like:

Visual Basic für Applikationen
Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

- Marcus

[Anonymous]

Still nothing happens. Does this work for you?

[marcus_sommer]

Have you tried: xlWSheet.ActiveCell.Formula = "=D$22/$C$22*$C23" ?

Perhaps this is also helpful: Macro to autofill excel

- Marcus

[Anonymous]

Tried a lot of combinations now and this eventually worked:

xlWSheet.Range("D23").Formula = "=R22C/R22C3*R23C3"

...so that´s a lot like your first suggestion. ActiveCell doesn´t seem to work when trying to add a formula. I´ll continue with the autofill now, hopfully your link will help....

[Anonymous]

...and the link helped, thank you very much