i have many different sheets like 50 + where i have 6-5 tables in each sheet
i try to copy each sheet in excel separately
i.e.
if i have td1 sheet in qlikview and 4-5 tables in that then want to create sheet with name td1 in excel and copy all tables in excel same for further sheet
i try this .. but when tables copy in excel then at some end system is hang beacuse of large data so how i manage that hanging .. is there any solution through javascript code?
Sub BA
Dim xlApps
Dim xlBook
Dim xlSheet
Dim xlNewSheet,intSheetCount
Dim wscount
Dim value
SET xlApps = 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-1
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)
xlApps.Sheets(i+1).Select
if ss.GetProperties.SheetId = "Document\INV7" THEN
xlApps.Sheets(i+1).Paste xlApps.Sheets(i+1).Range("A"&j*2500+1)
ELSE
xlApps.Sheets(i+1).Paste xlApps.Sheets(i+1).Range("A"&j*100+1)
END if
xlApps.CutCopyMode = False
Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo. >NUL | clip", 0, True
objType.Minimize
End If
Next
xlApps.Sheets(i+1).Name = value
Doc.GetApplication.WaitForIdle 60
IF i < ws_count-1 THEN
SET xlNewSheet = xlApps.Application.Worksheets.Add(, xlApps.Worksheets(xlApps.Worksheets.Count))
xlApps.Worksheets(1).SELECT
END IF
NEXT
xlBook.SaveAs( ActiveDocument.Variables("vPath").getcontent.string&"\Test.xlsx")
xlBook.Close
set xlApps = nothing
set xlBook = nothing
End sub
