Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Didn´t work either, unfortunately. Actually I saw now that the first calculation (xlWSheet.ActiveCell.FormulaR1C1 = "=R22C/R22C3*RC3") wasn´t even performed.
Try it with another Formula-Property like:
Visual Basic für Applikationen |
---|
|
- Marcus
Still nothing happens. Does this work for you?
Have you tried: xlWSheet.ActiveCell.Formula = "=D$22/$C$22*$C23" ?
Perhaps this is also helpful: Macro to autofill excel
- Marcus
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....
...and the link helped, thank you very much