Macro stopped without error

[#1]

my code is pasting qlikview objects to Excel, Qlikview contains 57 tabs and around 10 tabs are having 32 items. sometimes my code works perfectly sometimes it breaks without any error. Nowadays i am testing on windows server 2012 R2 so it is breaking without error and connecting server through Team viewer also breaks code. Does excel version or running on Virtual machine is the reason to break code.

My code:

Sub AAAA

Dim xlApp

Dim xlBook

Dim xlSheet

Dim xlNewSheet,intSheetCount

Dim wscount

Dim value

    SET xlApp = CREATEOBJECT("Excel.Application")

xlApp.Visible = false

SET xlBook = xlApp.Workbooks.Add

SET xlSheet = xlBook.Worksheets("Sheet1")

    ws_count = ActiveDocument.NoOfSheets

FOR i=0 to ws_count-13

        set Doc = ActiveDocument

        set ss = Doc.Getsheet(i)

        Call ss.Activate()

        value = ss.GetProperties.Name

      For j = 0 to ss.NoOfSheetObjects - 1

     sheetobj = ss.SheetObjects(j).GetObjectId

     set  objType=Doc.GetSheetObject(sheetobj)  

if objType.GetObjectType = 11 or objType.GetObjectType = 10 then

     objType.Activate()

     objType.Maximize

  ActiveDocument.GetApplication.WaitForIdle 60

     objType.CopyTableToClipboard(true)

     xlApp.Sheets(i+1).Select

    xlApp.Sheets(i+1).Paste xlApp.Sheets(i+1).Range("A"&j*100+1)

    xlApp.CutCopyMode = False

           Set WshShell = CreateObject("WScript.Shell")

           WshShell.Run "cmd.exe /c echo. >NUL  | clip", 0, True

             objType.Minimize

         End If   

           Next

xlApp.Sheets(i+1).Name = value

Doc.GetApplication.WaitForIdle 60

IF i < ws_count-13 THEN

SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))

xlApp.Worksheets(1).SELECT

END IF

NEXT

xlBook.SaveAs( ActiveDocument.Variables("vPath").getcontent.string&"\ExportFile.xlsx")

xlBook.Close

set xlApp = nothing

set xlBook = nothing

End sub

[marcus_sommer]

It's quite a large task and therefore various disruptions could be happens. This meant it could be useful to slice this task into several smaller tasks and to add them again afterwards. Beside possible issues with the network/storage could from the excel-side things like auto storing/recovering effect the execution.

Especially if there is no error-handling included and the waiting/sleep-logik isn't very advanced (WaitForIdle has no time-parameter) and I see that you removed the clipboard content within each iteration which indicates that there are problems with the clipboard. I'm not sure if this is always helpful. Beside this it could be useful to log the most important parts (variable-values, loop-counter and similar stuff - each with a timestamp) of the routine - maybe within another excel or within a text-file - to see if there is any pattern if it failed.

Further you should take a look on the VM settings - a qlik environment reacts very sensitive of any sharing of the system-resources which needs to be avoided.

- Marcus