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

       

      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