0 Replies Latest reply: Apr 5, 2018 7:44 AM by Capricon User RSS

    copy tables in excel

    Capricon User

      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




      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




        ActiveDocument.GetApplication.WaitForIdle 60





          if ss.GetProperties.SheetId = "Document\INV7"  THEN

          xlApps.Sheets(i+1).Paste xlApps.Sheets(i+1).Range("A"&j*2500+1)



          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



               End If   



        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))



        END IF



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



      set xlApps = nothing

      set xlBook = nothing

      End sub