Excel API - simple formatting

[peter_turner]

Hello!

Does anyone have some simple examples of formatting with Excel API via a macro?

I have a table in QV9 and I would like to send this to excel, together with some additional information and formatting.

An example would be to have a Title in cell A1, bold, underlined in times 14 font.
Then my table in B1 onwards, that sort of thing.

To start I have:

sub simple_export_test
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set XLDoc = XLApp.Workbooks.Add
set XLSheet = XLDoc.Worksheets(1)
XLSheet.Range("A1")="Hello World!"
XLSheet.Range("B1").select
XLSheet.Paste
end sub

Any tips/examples for be greatly appreciate!
Peter.

[peter_turner]

Thanks everyone!

I can now manipulate the fonts, and insert variables.
Last bit i'm stuck on is changeing the border and following actions (from a macro i recorded in excel)

 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 Selection.Borders(xlEdgeLeft).LineStyle = xlNone
 Selection.Borders(xlEdgeTop).LineStyle = xlNone
 Selection.Borders(xlEdgeBottom).LineStyle = xlNone
 Selection.Borders(xlEdgeRight).LineStyle = xlNone
 Selection.Borders(xlInsideVertical).LineStyle = xlNone
 Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 Selection.Interior.ColorIndex = xlNone

How would you edit the above for QV macro to run?

Thanks<
Peter.

[#3]

I'm guessing the xl Constants are giving you the problem. I'd just convert them to straight values (although Talha showed how you can define them within QlikView).

Inside Excel, open the VBA editor. Hit F2, which should bring up the object browser. Type each xl Constant into the search box and hit enter. Click on the item in the Members Of box and below that box you should see:

Const xlDiagonalDown = 5
 Member of Excel.XlBordersIndex

Since xlDiagonalDown = 5 and xlNone = -4142, your first line would be rewritten as:

Selection.Borders(5).LineStyle = -4142

In your QlikView macro, you would need to preceed each line with your sheet reference (oSH. in my examples).

[#4]

Hi guys,

This code is really useful. however, I keep getting a subscript out of range error when I try to apply a similar method to my own macro. Any ideas?

Thanks

[#5]

What is the line that is throwing the error?

[#6]

Sorry NMiller, the exception/error being thrown up occurs when I try to reference my Worksheet when formatting the cell outlines of the table. I thought by coding:

set s=ActiveDocument.Sheets("tab2")

ActiveDocument.Sheets("tab2").Activate

set XLSheet = XLDOC.Worksheets(2)

ActiveDocument.GetSheetObject("TB01").Restore

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

that i could put the XLSheet prefix before eg:

Selection.Borders(6).LineStyle = -4142, therefore:

XLSheet .Selection.Borders(6).LineStyle = -4142 but i get the error Object doesn't support this property or method?

The object is a table box so it should support this property?

[#7]

Excel Automation in QlikView doesn't like the Selection thing. You can't use XLSheet.Range("A3").Select and you can't use XLSheet.Selection... I think you may have to refer to a specific cell. You may also be able to use a pre-defined range, but I'm not too sure.

What you need to do is something like:

XLSheet.Range("A3").Borders(6).LineStyle = -4142

I can't figure out what you're trying to do there though. That line would say set the Diagonal Up border in Cell A3 to have Line Style of None. Is that what you are trying to do?

[#8]

Sorry, Actually meant to put 7 instead of 6 there which is the edgeleft linestyle as 0.

Thanks alot for your reply!