Macro Issue

[dmac1971]

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

[jonathandienst]

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     

[dmac1971]

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

[tamilarasu]

Hi Dermot,

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

For i = 1 to Val.count - 1

             To

For i = 0 to Val.count

[tunoi]

this line

For i = 1 to Val.count - 1

should be

For i = 0 to Val.count - 1