topic Re: About text control using the VB script export Excel problem in QlikView

About text control using the VB script export Excel problem

Tue, 08 Mar 2016 02:20:53 GMT

How to use the VB script, export the Excel report, with text to fight together form.PLS See attachment.

I find some examples in community, but They are list and table export scripts, the export of text did not find the relevant examples, please expert advice.

thank you!

Re: About text control using the VB script export Excel problem

marcus_sommer — Tue, 08 Mar 2016 08:02:14 GMT

For this you need an approach like this one: QlikTip #32: Exporting multiple QV objects to a single Excel document and you would need to replace .CopyTableToClipboard to . CopyTextToClipboard.

But I suggest to consider if you couldn't use a pivot-table for this - technically it's not a great problem, you would need to create a synthetic dimension with sales and inventory and 9 expressions which have a small if-loop to check the dimension. You are only restricted with the layout which isn't quite so flexible like a textbox.

Another option might be to use different sheets/objects for viewing within qlikview and for exporting and/or printing.

- Marcus

Re: About text control using the VB script export Excel problem

petter — Tue, 08 Mar 2016 08:08:00 GMT

This script fragment shows you how you can access the text property of all text objects of a sheet:

Sub ExportTextObjects
  Set doc = ActiveDocument
  Set sht = doc.ActiveSheet
  objs = sht.GetTextObjects
  l = ""
  For i=LBound( objs ) to UBound( objs )
  l = l + objs(i).GetText +  Chr(13) + Chr(10)
  Next
  MsgBox l
End Sub

You have to put this in context for your needs and possibly have a way to determine what is row and column of each text object. It could be determined by top and left coordinates of each object.

Anyway a pivot or straight table is a much easier way to go...

Re: About text control using the VB script export Excel problem

Tue, 08 Mar 2016 08:55:04 GMT

First of all thank you for your reply.

"CopyTableToClipboard to CopyTextToClipboard replace" is wrong,

This is not to get the value of the TEXT.

Re: About text control using the VB script export Excel problem

marcus_sommer — Tue, 08 Mar 2016 09:31:14 GMT

Yes, CopyTextToClipboard don't worked but the CopyTableToClipboard does it yet.

- Marcus

Re: About text control using the VB script export Excel problem

Tue, 08 Mar 2016 09:45:10 GMT

First of all thank you for your reply.

You said very good, but my idea is this, I have been studying the method of Test_David.qvw, through the control of the ID to locate the Excel cell. Now that it has received a value, in order to fill in the order to Excel. Because the rows and columns of the table are fixed, so you can avoid the problem of positioning.

How to write data to the Excel cell?