topic Re: Export To Excel Macro in QlikView

Export To Excel Macro

dmac1971 — Thu, 12 Nov 2015 08:54:18 GMT

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.

Re: Export To Excel Macro

marcus_sommer — Thu, 12 Nov 2015 12:45:59 GMT

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

Re: Export To Excel Macro

syukyo_zhu — Thu, 12 Nov 2015 12:53:05 GMT

Hi,

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

curSheet.Columns("A").ColumnWidth=5

curSheet.Columns("B:E").ColumnWidth=15

curSheet.Columns("F").ColumnWidth=40

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