topic Re: Macro to autofill excel in QlikView

Macro to autofill excel

gerhardl — Wed, 11 Sep 2013 14:02:50 GMT

Hi there,

I have a macro that sends data to excel. I am trying to autofill the formula I am adding in Cell E2, all the way to the last row that has data in column A.

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLSheet2.Paste XLSheet2.Range("A1")

XLApp.Worksheets(2).Range("E:E").Insert

XLSheet2.Range("E2").Formula = "=concatenate(D2,A2)"

???????

In Excel VBA I would do this:

Selection.Autofill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)

Any idea how to do this in a QB macro?

Re: Macro to autofill excel

marcus_sommer — Wed, 11 Sep 2013 14:16:21 GMT

It should work - see here: VBScript - Autofill in Excel

If not you could also use a copy + select range (after counting) + paste.

- Marcus

Re: Macro to autofill excel

gerhardl — Wed, 11 Sep 2013 14:32:48 GMT

Thanks, I managed like this eventually:

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLSheet2.Paste XLSheet2.Range("A1")

XLApp.Worksheets(2).Range("F:F").Insert

LastRow = XLSheet2.Range("A65535").End(-4162).Row

XLSheet2.Range("F1") = "Card No"

XLSheet2.Range("F2").Formula = "=concatenate(D2,E2)"

XLSheet2.Range("F2").AutoFill XLSheet2.Range("F2:F"& LastRow)