topic Re: Macro, excel export and calculations in QlikView

Macro, excel export and calculations

Anonymous — Mon, 03 Mar 2014 15:36:22 GMT

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

Re: Macro, excel export and calculations

marcus_sommer — Mon, 03 Mar 2014 15:56:20 GMT

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

Re: Macro, excel export and calculations

Anonymous — Mon, 03 Mar 2014 16:41:17 GMT

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

Re: Macro, excel export and calculations

marcus_sommer — Tue, 04 Mar 2014 08:45:14 GMT

Try it with another Formula-Property like:

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

- Marcus

Re: Macro, excel export and calculations

Anonymous — Tue, 04 Mar 2014 09:15:53 GMT

Still nothing happens. Does this work for you?

Re: Macro, excel export and calculations

marcus_sommer — Tue, 04 Mar 2014 09:25:46 GMT

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

Perhaps this is also helpful: Macro to autofill excel

- Marcus

Re: Macro, excel export and calculations

Anonymous — Tue, 04 Mar 2014 12:24:49 GMT

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

Re: Macro, excel export and calculations

Anonymous — Tue, 04 Mar 2014 14:58:57 GMT

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