topic Excel API - simple formatting in QlikView

Excel API - simple formatting

peter_turner — Thu, 09 Jul 2009 00:15:22 GMT

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.

Excel API - simple formatting

stephencredmond — Thu, 09 Jul 2009 00:53:41 GMT

Hi Peter,

The very simplest way to find out how to do anything in Excel is to start recording a Macro, do what you want to do in Excel, stop recording then look at the code that has been generated.

Regards,

Stephen

Excel API - simple formatting

Thu, 09 Jul 2009 02:38:30 GMT

Peter this is the code i am using in one of the application and it works for me , try it out and let me know if you wount understand anything.

Sub Export_To_Excel
Const xlShiftDown = -4121

Set oShell = CreateObject("WScript.Shell")
set fso = CreateObject("Scripting.FileSystemObject")

set C = ActiveDocument.Variables("Rep_Condition")
if C.GetContent.string = "1" then
set obj = ActiveDocument.getSheetObject("CH1")
else
set obj = ActiveDocument.getSheetObject("CH2")
end if

set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True

Application.Sleep(1000)

' msgbox path

obj.sendToExcel

set XLDoc = XLApp.Workbooks.item(1)

Set objRange = XLApp.Cells(1, 1).EntireRow
objRange.Insert(xlShiftDown)

Set objRange = XLApp.Cells(2, 1).EntireRow
objRange.Insert(xlShiftDown)

ActiveDocument.GetSheetObject("TX1").CopyTextToClipboard
XLApp.Worksheets(1).Range("A1").Select()
XLApp.Worksheets(1).Paste()

ActiveDocument.GetSheetObject("TX2").CopyTextToClipboard
XLApp.Worksheets(1).Range("A2").Select()
XLApp.Worksheets(1).Paste()

XLApp.Worksheets(1).Range("A1").Font.Bold = True
XLApp.Worksheets(1).Range("A2").Font.Bold = True

XLApp.Worksheets(1).Range("A1").Font.Size = 12
XLApp.Worksheets(1).Range("A2").Font.Size = 12

XLApp.Worksheets(1).Range("E1").Select()

End Sub

Talha

Excel API - simple formatting

peter_turner — Thu, 09 Jul 2009 15:01:00 GMT

Hi Talha,

Thanks for the example, i'm not entirely sure what this command does:

Set objRange = XLApp.Cells(1, 1).EntireRow
objRange.Insert(xlShiftDown)

Maybe select cell(1,1) and highlight the row, then insert a new row?

XLApp.Worksheets(1).Range("A1").Font.Size = 12
XLApp.Worksheets(1).Range("A1").Font.Bold = True

These are easy to guess the function, but is there a document or guide that says what you can add to the end of .Font.? and all the other things you can do, i.e underline and change font style.

Thanks,
Peter.

Excel API - simple formatting

stephencredmond — Thu, 09 Jul 2009 15:12:42 GMT

Hi Peter,

The code that Talha has given you is great but it is only great if it does exactly what you want to do in Excel. To find out how to do exactly what you want to do, you need to do what I suggested - record a macro.

When you press Alt+F11 in Excel, it will open the VBA editor and the help file in there has the full API reference.

One "gotcha" is that Excel VBA includes many constants (e.g. xlShiftDown) which will not be known to the QlikView VBScript engine. You will need to define them as Const in your script or just use the values instead.

Regards,

Stephen

Excel API - simple formatting

Thu, 09 Jul 2009 15:27:16 GMT

Hi Peter

You're right. That part of the script inserts a new row. I've yet to find a API-guide specifically made for Excel. The most useful stuff until now has been by recording a macro within Excel and use snippets of that script. Otherwise you might find something on msdn.microsoft.com

/Martin

Excel API - simple formatting

peter_turner — Thu, 09 Jul 2009 18:47:07 GMT

Hello,

I've figured out how to change the font size, type, bold, underlined.

Next is how to insert the value of a QV variable into my excel document.
Also i need to try and insert the current selection information, any ideas?

Thanks,
Peter.

Excel API - simple formatting

Thu, 09 Jul 2009 19:01:04 GMT

Hello Peter,

This lines of code is used to put the cursor on perticular position and then move the cursor down one cell
Set objRange = XLApp.Cells(1, 1).EntireRow
objRange.Insert(xlShiftDown)

What i did is downloded the guide from msdn website for vb script there is one good help file
by the name 'hey scripting guy' -- google it and you can download from msdn

Talha

Excel API - simple formatting

peter_turner — Thu, 09 Jul 2009 19:13:29 GMT

Hi Talha,

Thanks for the reply, but my question was more todo with how to get some QV variables into Excel, and also how to get the QV current selection information into excel.

All feedback is very welcome, and i plan to write a little guide on basic formating for anyone else to review at a later date.

Thanks,
Peter.

Excel API - simple formatting

Thu, 09 Jul 2009 20:42:19 GMT

Getting variables and selection info is half QlikView macro, half Excel. In Excel, you can use Range to write to a field. To write the contents of a variable, use:

oSH.Range("A1") = ActiveDocument.Variables("VariableName").GetContent.String

Where oSH is the reference to your worksheet.

Getting selections into an Excel file would be similar. For me, I would create a QlikView variable set to GetFieldSelections(FieldName) and then use the code above to write the value of that variable to a field in Excel.

Excel API - simple formatting

peter_turner — Thu, 09 Jul 2009 22:37:31 GMT

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.

Excel API - simple formatting

Thu, 09 Jul 2009 22:53:39 GMT

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

Excel API - simple formatting

Tue, 26 Oct 2010 16:30:21 GMT

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

Excel API - simple formatting

Tue, 26 Oct 2010 16:32:23 GMT

What is the line that is throwing the error?

Excel API - simple formatting

Thu, 28 Oct 2010 16:24:04 GMT

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?

Excel API - simple formatting

Thu, 28 Oct 2010 20:08:26 GMT

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?

Excel API - simple formatting

Fri, 29 Oct 2010 09:34:59 GMT

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

Thanks alot for your reply!