Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
schumi1980
Contributor III
Contributor III

Table borders in macro

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:

PictureE1.jpg

This is what I would like to get:

PictureE2.jpg

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

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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!

View solution in original post

5 Replies
sudeepkm
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

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

andrey_krylov
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Master II
Master II

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!