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
