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.

[stephencredmond]

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

[#3]

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

[peter_turner]

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.

[stephencredmond · Accepted solution]

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

[#6]

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

[peter_turner]

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.

[#8]

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

[peter_turner]

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.

[#10]

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.