Macro stopped without error

Wed, 04 Oct 2017 07:44:21 GMT

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

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

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

xlBook.Close

set xlApp = nothing

set xlBook = nothing

End sub

Re: Macro stopped without error

marcus_sommer — Wed, 04 Oct 2017 09:38:04 GMT

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

topic Macro stopped without error in QlikView

Macro stopped without error

Re: Macro stopped without error