topic Format Excel during Export Macro in QlikView

Format Excel during Export Macro

Anonymous — Thu, 09 Dec 2010 21:08:45 GMT

After several hours I've given up and beg for help from the QV community. My users are asking that after a pivot chart has been exported to Excel blank rows should be inserted betweeen each row of data so that they are able to write comments concerning the data in the row above. I've mashed together some code I found until I am able to export the chart to Excel, but I can't seem to get the row insertion part to work. I keep getting an error whenever I try to refer to the ActiveCell. Here's a portion of the relevant code I've got so far:

XLDoc.Worksheets.Add

XLDoc.Worksheets("Sheet"&m).activate

XLDoc.Worksheets("Sheet"&m).Name = vTitle

set vObj = ActiveDocument.GetSheetObject(vCurrentPivot)

vObj.CopyTableToClipboard true

XLDoc.WorkSheets(vTitle).Paste XLDoc.Sheets(vTitle).Range("A1")

XLDoc.WorkSheets(vTitle).Cells.select

XLDoc.WorkSheets(vTitle).Cells.mergecells = false

XLDoc.WorkSheets(vTitle).Cells.EntireRow.RowHeight = 12.75

XLDoc.WorkSheets(vTitle).Cells.EntireColumn.AutoFit

XLDoc.WorkSheets(vTitle).Cells.NumberFormat = "0"

xlDown=-4121

XLDoc.WorkSheets(vTitle).Range("A1").select

With XLDoc.WorkSheets(vTitle).Range("A1")

.End(xlDown).select

End With

'msgbox XLDoc.WorkSheets(vTitle).ActiveCell.Value

Do While XLDoc.WorkSheets(vTitle).ActiveCell.Row > 2

'Insert blank row.

'XLDoc.WorkSheets(vTitle).EntireRow.Insert shift:=xlDown

'Move up one row.

XLDoc.WorkSheets(vTitle).ActiveCell.Offset(-1, 0).activate

Loop

Format Excel during Export Macro

Thu, 09 Dec 2010 21:21:54 GMT

You need to reference a Range to insert a row. QlikView automation cannot use the Select/Selection code.

Try:

XLDoc.WorkSheets(vTitle).Range("A2").EntireRow.Insert

Also, you can't use the shift:=xlDown format. I think you can make Insert a function and then use the value of the xlDown constant (-4121).

XLDoc.WorkSheets(vTitle).Range("A2").EntireRow.Insert(-4121)

In order to do it in a loop, I would first determine the last used row. I used to use Range("A65536") and then do an xlUp. Then store the Row in a variable. I'm not sure if that all works in QlikView automation though. Then you could use a For Next.

You may also be able to use QlikView to determine the number of rows and then put that in your For Next. Or you could just use a sufficient number, because if you insert rows after all of your data, they all just look like blank rows.

Format Excel during Export Macro

Anonymous — Thu, 09 Dec 2010 21:30:43 GMT

That works for inserting a static row, thanks. But my code needs to insert a row in between each data row exported. The method I was trying to use was after pasting in the data, move to the last data row then, using a loop, insert a blank row between the data rows.

Format Excel during Export Macro

Thu, 09 Dec 2010 21:38:47 GMT

Yes, you need to put it into a For Next loop and then use:

XLDoc.WorkSheets(vTitle).Range("A" & i).EntireRow.Insert(-4121)

Try:

For i = 50 To 2 Step -1
 XLDoc.WorkSheets(vTitle).Range("A" & i).EntireRow.Insert(-4121)
Next

You could probably reduce that 65536 to a lower number based on how many records you have. That should work, but it may be slower.

You can't use the code to move up rows, because QlikView automation does not allow for the Select/Selection stuff. QlikView won't hold a place, so you have to reference cells using Range.

EDIT: Start small on the loop. I'm sitting here waiting for it to run down from 65536.

It worked fine looping from 65536 to 2, but it took about a minute. If you can reduce that to a lower number (even like 2000 or something), you should get better results.

Format Excel during Export Macro

Anonymous — Thu, 09 Dec 2010 21:58:13 GMT

I get a Macro Parse Failed error with that For...Next code.

Format Excel during Export Macro

Thu, 09 Dec 2010 22:15:58 GMT

This is taken directly from the macro I tested it on, so the references are a little different:

For i = 65536 To 2 Step -1
 oSH.Range("A" & i).EntireRow.Insert(-4121)
Next

I added this one (your references) without error:

For i = 9 To 1 Step -1
 XLDoc.WorkSheets(vTitle).Range("A" & i).EntireRow.Insert(-4121)
Next

Format Excel during Export Macro

Anonymous — Thu, 09 Dec 2010 22:22:25 GMT

I think it's because I'm trying to put a For...Next loop within a For...Next loop (I'm exporting multiple charts to to different worksheets).

Format Excel during Export Macro

Thu, 09 Dec 2010 22:48:42 GMT

Did you use a different letter for each loop? If you use i for the first, then use j for the second. Nested loops seem to work, but require different letters to prevent the parse error.

For i = 1 to 2
 For j = 1 to 3
 MsgBox(i & j)
 Next
Next

should work.

Format Excel during Export Macro

Anonymous — Thu, 09 Dec 2010 22:54:02 GMT

That did it! Thanks so much for your help!!!