topic Re: Macro Issue in QlikView

Macro Issue

dmac1971 — Fri, 21 Oct 2016 07:30:55 GMT

Macro below loops though customers and copies table data into Excel report. Everything works fine but I always end up with a Customer report less than I should? Cant see why?

Sub ExportLines()

Dim strvPCT
Dim strvPeriod
DIM SaveName

vX=Year(now())&month(now())&day(now())

Set Val=ActiveDocument.Fields("CUSTOMER").GetPossibleValues(20000)
For i = 1 to Val.count - 1

'SET RowCount = ActiveDocument.Variables("vRowCount")
'RowCount.GetContent.String

set XLApp = CreateObject("Excel.Application")

set XLDOC = XLApp.Workbooks.Open ("C:\Qlikivew Extracts\Open Orders Blank.xlsm")
'set XLDOC = XLApp.Workbooks.Open ("C:\Qlikivew Extracts\Open Orders Blank.xlsm")

XLApp.Visible = True

strVal = Val.item(i).Text

'Use The Customer list box in the current open document and loop through possible choices
activedocument.fields("CUSTOMER").select Val.item(i).Text

vMySelectFieldValue = Val.item(i).Text

SaveName = strClean(strVal)

'With the selected customer ouput the data from table "CH168" to the open Excel Document.
ActiveDocument.GetSheetObject("CH168").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("PasteSheet")
XLSheet.Paste XLSheet.Range("A16")

'Save the document, close it and then start again

XLDoc.SaveAs "C:\Qlikivew Extracts\" & SaveName & " - Backlog " & vX & " .xlsm"

XLDoc.Close
XLApp.Quit
'End If

Next
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

End Sub

Re: Macro Issue

jonathandienst — Fri, 21 Oct 2016 07:34:53 GMT

Are you getting 20000 records and you expect more? This line is limiting the export for 20000 rows:

Set Val=ActiveDocument.Fields("CUSTOMER").GetPossibleValues(20000);

Adjust as required.

Or use the built in export functions, like:

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportEx "C:\test.qvd", 4

Re: Macro Issue

dmac1971 — Fri, 21 Oct 2016 07:35:59 GMT

No far less Jonathan, only around 50. That was in original code I borrowed, , from forums here last year. Thanks for reply though.

Re: Macro Issue

tamilarasu — Fri, 21 Oct 2016 07:43:57 GMT

Hi Dermot,

Not sure but try to run the script by changing the below line.

For i = 1 to Val.count - 1

For i = 0 to Val.count

Re: Macro Issue

tunoi — Fri, 21 Oct 2016 09:43:03 GMT

this line

For i = 1 to Val.count - 1

should be

For i = 0 to Val.count - 1