Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Leverage your QlikView investment to modernize BI – see how! Join Group
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Export To Excel Macro

Hi at the moment I don't have access to NPrinting, so have a little bit of code that loops through a data set, copies the data from a table, and pastes it into a preformatted Excel document.

Everything is working well, but I'd like to add some additional polish to the code.  The code to paste the data in is :

ActiveDocument.GetSheetObject("CH93").CopyTableToClipboard true

  set XLSheet = XLDOC.Worksheets("Open Orders")

  XLSheet.Paste XLSheet.Range("A16")

However I'd like to paste values only, rather than carry over the source formatting, how can I get this working, previous attempts have just failed.

Another failing in the code, which uses all available data in a listbox, rotating through each one in turn, is that it will also choose an account that has no open lines.  I created a variable counting the number of lines based on the list box selection and added an if statement in to say if the count was 0 just end, but the code wouldn't work, what could I be missing?

Thanks in advance.

2 Replies
MVP & Luminary
MVP & Luminary

Re: Export To Excel Macro

To paste not everything else then certain aspects like values, formattings and so on you will need PasteSpecial: https://msdn.microsoft.com/de-de/library/office/ff839476.aspx but this worked mostly only inside from a single excel-instance and not per clipboard. This meant you will need to paste the table somewhere temporary and copy it again to be able to use these PasteSpecial functionalities.

Another possibility (especially needed if the layout-requirements are more expensive) is similar to your approach with a predefined layout only that these areas and sheets pull the data per any lookup-functions from a (hidden) data-sheet in which you the table pasted (after cleaning those from the previous one).

Your listbox through which your routine runs (it mustn't be the same which the user used withinthe sheet) should not contain a field else an expression like: if(condition, field) eventually wrapped in an aggr-function if the condition contained aggregation-functions like sum or count - an alternatively for this might be to use chart-table to apply complex-conditions and run through them.

- Marcus

Creator III
Creator III

Re: Export To Excel Macro


I have somes example, so you can try these to meet the format to your preformatted Excel document




      curSheet.Columns("I:J").NumberFormatLocal = "# ##0,00"