Table borders in macro

[schumi1980]

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 

next

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

  oSH.Range("A1").Select 

' oXL.Sheets("Sheet2").Delete

' oXL.Sheets("Sheet3").Delete

    oSH.Name = "Data"

oSH.Rows("1").delete   

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

[sudeepkm]

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. 

[schumi1980]

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

[andrey_krylov]

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.

[schumi1980]

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) 😞

[Frank_Hartmann · Accepted solution]

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!