topic Re: Table borders in macro in QlikView

Table borders in macro

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

Dear All

I am using a macro to extract tables to excel.

I try to produce as much of the formatting as possible already in Qlikview (I cannot produce a target file as the exported tables vary in size). Even if I change the pivot table style to classic in Qlikview, it does not alter the output.

How can I maintain the border style (or produce a border style) in a macro:

This is what I get:

This is what I would like to get:

I would like to be able to select the area of the table I just put into excel and then make borders.

My macro code is:

sub exportLossRun2

'==============================================================

' File Path & Name

Path = "C:\temp\"

FileName = "Test_"

strSaveFile = Path & FileName

'==============================================================

'Open Excel

set oXL=CreateObject("Excel.Application")

oXL.visible=True

oXL.Workbooks.Add

aSheetObj=Array("Name2","Main","Summary1","Summary2") ' Chart ID's here

'==============================================================

for i=0 to UBound(aSheetObj)

Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count

If num_rows = 1 then

oSH.Range("A2").Select

Else

oSH.Range("A" & num_rows+4).Select

End If

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

set obj=Nothing

oSH.Cells.Select

oSH.Columns("A").ColumnWidth = 15

oSH.Columns("B").ColumnWidth = 15

If num_rows = 1 then

oSH.Range("A" & num_rows).Value = sCaption

oSH.Range("A" & num_rows).Font.Bold = True

'oSH.Range("A" & num_rows).Font.ColorIndex = 3

oSH.Range("A" & num_rows).Interior.ColorIndex = 40

Else

oSH.Range("A" & num_rows+3).Value = sCaption

oSH.Rows(num_rows+4).Font.Bold = True

'oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40

End If

'oXL.Selection.Columns.AutoFit

'==============================================================

oSH.Range("A1").Select

' oXL.Sheets("Sheet2").Delete

' oXL.Sheets("Sheet3").Delete

oSH.Name = "Data"

oSH.Rows("1").delete

oSH.Rows("1").Font.Bold = True

oSH.Rows("1").Font.Size = 14

oSH.Rows("1").WrapText = False

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

set oSH = Nothing

set oXL=Nothing

end sub

Many thanks for your help.

Best regards,

Jan

Re: Table borders in macro

sudeepkm — Fri, 07 Sep 2018 15:47:05 GMT

I think there is way to achieve it but the solution is not that great. I'll see if any other way can be possible.

If you use a target file and have multiple sheets for a single QlikView table but each sheet designed per the number of columns as per your QlikView table and the border accordingly.

Again visibility of those target sheets be controlled based on variable set from QlikView.

Based on the # of columns in your QlikView table the target sheet to be set in the macro and you can manage the border style in your target xls template file.

Again just for border style, implementing such solution won't be wise.

Re: Table borders in macro

schumi1980 — Fri, 07 Sep 2018 16:04:57 GMT

It would be great, if you found something. I have not managed that today.

Re: Table borders in macro

andrey_krylov — Fri, 07 Sep 2018 16:11:19 GMT

Hi, Jan. I spend a lot of time doing something like that. The easiest way is to record the macro with all you need using excel's macrorecorder and then transfer it to QV with small modifications.

Re: Table borders in macro

schumi1980 — Sun, 09 Sep 2018 15:01:15 GMT

Fully agree, but 2 days of trial and error did not accomplish anything: All I want is a sharp border (and possibly get rid of the gridlines) 😞

Re: Table borders in macro

Frank_Hartmann — Sun, 09 Sep 2018 18:12:05 GMT

Hi Jan,

have a look at the attached example.

Not the most ellegant solution, but it works!

Just edit the path in line 102 inside the module according your environment!

hope this helps!