topic Re: Macro working wrong on postReload in QlikView

Macro working wrong on postReload

sibideepak — Tue, 17 Nov 2015 08:39:06 GMT

Hi ,

Following macro works fine when I manually export but working wrong on PostReload. Im trying to copy 5 different chart values into one excel. So First I'll export heading and copy and paste 5 chart values one by one but on PostReload "CH2030" values only coping 5 times.

Sub Exporttoexcel

dim filesys, newfolder, newfolderpath

newfolderpath = "C:\Temp"&(ActiveDocument.Evaluate("=Date(vDateMax,'DD-MMM-YYYY')"))

set filesys=CreateObject("Scripting.FileSystemObject")

Set newfolder = filesys.CreateFolder(newfolderpath)

set obj = ActiveDocument.GetSheetObject("CH2039")

obj.Export newfolderpath&"\Top 5 Performance Contributors(MTD).csv",","

chartArray = Array("CH2008","CH2027","CH2028","CH2029","CH2030")

usedRows=1

For Each chart In chartArray

Set F = ActiveDocument.GetSheetObject(chart)

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open(newfolderpath&"\Top 5 Performance Contributors(MTD).csv")

objExcel.DisplayAlerts = FALSE

objExcel.Visible = FALSE

Set objWorksheet = objWorkbook.Worksheets("Top 5 Performance Contributors ")

F.CopyValuesToClipboard()

objWorksheet.Cells(usedRows+1, 1).Select

objWorksheet.Paste

usedRows=objWorksheet.UsedRange.Rows.Count '--->function to get the first unused row

'' msgbox usedRows

'' msgbox chart

objWorksheet.SaveAs newfolderpath&"\Top 5 Performance Contributors(MTD).csv", 6

objWorkbook.Close

ActiveDocument.GetApplication.Sleep 2000

Next

objExcel.Quit

end sub

Thanks in Advance,

Deepak

Re: Macro working wrong on postReload

sibideepak — Tue, 17 Nov 2015 11:36:39 GMT

Any one to help.,

Re: Macro working wrong on postReload

marcus_sommer — Tue, 17 Nov 2015 12:39:59 GMT

This macro worked manually like expected and per reload-trigger it will be only the csv with CH2039 created?

I think before going deeper with searching on possible differences between both methods and if there are solutions or workarounds should the routine be changed. Then at first you creates a csv-file which you then within a loop each time in a new excel-instance opened and stored (you will have many open excel-instances if you looked into the taskmanager).

I suggest to create at first (after folder- and array-part) one excel-instance, create an sheet with a name * and copy then all objects one after another into this sheet and store then the excel again and add also ActiveDocument.GetApplication.WaitForIdle instead this sleep-statement to make sure that qv is finish with calculations if your routine goes to the next step.

* MrExcel Message Board

- Marcus

Re: Macro working wrong on postReload

sibideepak — Tue, 17 Nov 2015 12:59:55 GMT

In PostReload trigger 5th chart values are repeating 5 times. And previously I did the code which open the file before loop and paste values into file and save it after loop but that also causes some other issue like running the macro more than twice works correctly but not at first time. So changed to this routine.

Re: Macro working wrong on postReload

marcus_sommer — Tue, 17 Nov 2015 13:41:22 GMT

Then there were other things wrong, too. Have a look on these references:

Export Multiple Chart Objects to Excel and Loop by Field

QlikTip #32: Exporting multiple QV objects to a single Excel document

- Marcus