topic Re: Excel export macro issue in QlikView

Excel export macro issue

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

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?

Re: Excel export macro issue

big_dreams — Thu, 28 Dec 2017 10:06:53 GMT

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,

Re: Excel export macro issue

Anonymous — Thu, 28 Dec 2017 10:14:53 GMT

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

Re: Excel export macro issue

Anonymous — Thu, 28 Dec 2017 10:50:48 GMT

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