Qlik Community

QlikView Integration

Discussion Board for collaboration on QlikView Integration.

capriconuser
New Contributor III

copy tables in excel

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

Community Browser