Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mihai_iov
Creator II
Creator II

Excel export macro issue

I keep getting an "unknown runtime error" at these lines:

XLsheet1.Range("A3:AD37").Borders(xlEdgeBottom).Weight = xlMedium

XLsheet1.Range("A3:AD37").Borders(xlEdgeLeft).Weight = xlMedium

XLsheet1.Range("A3:AD37").Borders(xlEdgeRight).Weight = xlMedium

XLsheet1.Range("A3:AD37").Borders(xlEdgeTop).Weight = xlMedium

I'm trying to put some thick borders in the said cells range by setting the weight as Medium, but the unknown runtime error happens on every line(tried commenting them).
Is there any workaround for the error or a different way of putting thick borders in the excel cells with a macro in QlikView, after exporting some pivot tables from QlikView?

1 Solution

Accepted Solutions
mihai_iov
Creator II
Creator II
Author

Managed to find a solution. Apparently VBScript doesn't support all excel-defined constants so you have to use the numerical value.
I ran:

XLsheet1.Range("A3:AD37").Borders(xlEdgeBottom).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeLeft).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeRight).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeTop).Weight = 4

and all worked well

View solution in original post

3 Replies
big_dreams
Creator III
Creator III

Is macro required?

If your range fixed then you can apply format in you template.

If you still want to use macro then instead of XLsheet1 use sheet1.

Regards,

mihai_iov
Creator II
Creator II
Author

The macro is required because besides formatting, it exports some pivot tables from QlikView

I've also tried replacing XLsheet1 with XLDoc.Sheets(1) but I still get the same error. Here are some of the "set"s I use.

set XLApp = CreateObject("Excel.Application")

set XLDoc = XLApp.Workbooks.Add

mihai_iov
Creator II
Creator II
Author

Managed to find a solution. Apparently VBScript doesn't support all excel-defined constants so you have to use the numerical value.
I ran:

XLsheet1.Range("A3:AD37").Borders(xlEdgeBottom).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeLeft).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeRight).Weight = 4

XLsheet1.Range("A3:AD37").Borders(xlEdgeTop).Weight = 4

and all worked well

View solution in original post