Activation of Bookmark within a macro - Qlik Community

stevietm · ‎2014-02-19

Hi All,

I want to activate a bookmark within a macro and then after the Bookmark has been activated it should export all of my selected object to excel without clearing the bookmark.

My macro works for exporting the objects but it never selects the bookmark.

I need to create reports with each bookmark created. So for example; I need to activate the first bookmark, and then export data to two different sheets. After this has been done I need to clear the bookmark that were selected, select the next bookmark and export data to the next two sheets in the same excel report.

Hope it makes sense and something like this is possible.

Thanks in advance

Regards

Stevie

Report Inappropriate Content · ‎2014-02-19

Hi,

this is how you activate a bookmark in macro:

ActiveDocument.RecallDocBookmark "MyBookmark"

I hope this helps and this is what you need.

regards,

MT

stevietm · ‎2014-02-19

Hi Magdalena,

Please see below a part of my script where I am using the above suggested.

Would you please be so kind just to have a look at the scrips and tell me where i am going wrong.

Thanks

sub exportToExcel_Variant3

ActiveDocument.RecallDocBookmark "Sumari - AI Oral Levofloxacin"

Dim aryExport(1,3)

aryExport(0,0) = "Price_File_TB01"

aryExport(0,1) = "Sales Overview"

aryExport(0,2) = "A1"

aryExport(0,3) = "data"

aryExport(1,0) = "LB182"

aryExport(1,1) = "Top Customers"

aryExport(1,2) = "A1"

aryExport(1,3) = "data"

ActiveDocument.UnlockAll

ActiveDocument.ClearAll true

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here

'// like saving the excel, some formatting stuff, ...

end sub

Regards

Stevie

Report Inappropriate Content · ‎2014-02-19

Is the Bookmarkname spelled right?? Is it a DocumentBookmark?

regards,

MT

Report Inappropriate Content · ‎2014-02-19

try only to select the bookmark with your macro?? if that doesnt work then please chech you security restrictions on the left side of the macro editor.

stevietm · ‎2014-02-19

Hi Magdalena,

The bookmark is spelled correct also it is a document bookmark. And see screen shot of security restrictions. I have tried system access as well as safe mode.

Thanks

Regards

Stevie

jerem1234 · ‎2014-02-19

If you are having problems with selecting the bookmark, I would suggest debugging with just using that code:

sub Test

ActiveDocument.RecallDocBookmark "Sumari - AI Oral Levofloxacin"

end sub

Then see if it actually selects this bookmark. Other than that, the code looks fine to me.

EDIT: That Clear Selections might be causing problem, you probably want to execute that last.

Hope this helps!

stevietm · ‎2014-02-20

Hi Jerem,

I Finally got it to select the bookmark and export that data. The problem was the clear selection that you mentioned as it were in the wrong position.

But now i have a new problem, Now i need the macro to clear the selections and select a new bookmark and export that data to the same document but to different sheets. Please have a look at my script below. The scrips works for the first export and selects the first bookmark but it does not go on to export data to sheet 2, 3 or 4 also it does not clear the selection made by the first bookmark.

Thanks

sub exportToExcel_Variant3

Dim aryExport(3,3)

ActiveDocument.RecallDocBookmark "Sumari - AI Oral Market" 'Select Bookmark

aryExport(0,0) = "Obj1"
aryExport(0,1) = "Sales Overview"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "Obj2"
aryExport(1,1) = "Top Customers"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

ActiveDocument.ClearAll true

ActiveDocument.RecallDocBookmark "Sumari - AI Oral Levofloxacin" 'Select Bookmark

aryExport(2,0) = "Obj3"
aryExport(2,1) = "Sales Overview2"
aryExport(2,2) = "A1"
aryExport(2,3) = "data"

aryExport(3,0) = "Obj4"
aryExport(3,1) = "Top Customers2"
aryExport(3,2) = "A1"
aryExport(3,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

ActiveDocument.ClearAll true

objExcelWorkbook.Worksheets ("Sales Overview").Rows("2:3").Delete = True
end sub

Kind Regards

Stevie

marcus_sommer · ‎2014-02-20

Are you going to the another sheets - like: objExSheet2.Activate? How looks the routine for "copyObjectsToExcelSheet"? Take also a look on http://community.qlik.com/search.jspa?q=macro+export&type=document.

- Marcus

jerem1234 · ‎2014-02-20

I am pretty sure your problem lies with how your function, copyObjectsToExcelSheet, works (I searched Qlikcommunity and found which one this was). For the second set of objects, I would create a new array, Dim aryExport2(1,3), and change the first array to only hold 2 objects, aryExport(1,3).

If you change this, it should create two excel documents.

Im guessing by sheets, you mean sheets in excel? If you only want one excel document with four sheets, you'll have to take out the part of creating the excel file in the function copyObjectsToExcelSheet and put it inside your method exportToExcel_Variant3. This way it doesnt create an excel document every time the function is called (you'll also have to add this as parameters to your function and pass the necessary objects).

Hope this helps!