0 Replies Latest reply: Oct 21, 2018 10:11 PM by Wong Chee Ken RSS

    How to select from table when connected to excel?

    Wong Chee Ken

      Hi,

       

      When i use the following code to run i encounter an error message, it says "there is no open data connection".
      Then i tried hiding the select statement, and another error message was received, "table not found".ow to resolve this?

       

      May i know how to solve this error?

      /**/
      LIB CONNECT TO 'Mystics';
      
      
      [MysticsTable]:
      LOAD
          Source,
          Table
      FROM [$(vSourcePath)\DATA\SOURCE\MANUAL_TEMPLATE\TABLE_EXTRACTION_SETUP.xlsx]
      (ooxml, embedded labels, table is ACC)
      Where Exclude <> 'x' //Exclude here is a table column.
      ;
      
      
      
      
      FOR i = 1 to NoOfRows('MysticsTable')
      
      
        Let vSource = Peek('Source',$(i)-1,'MysticsTable');
        Let vTable = Peek('Table',$(i)-1,'MysticsTable');
      
          [$(vTable)]:
           LOAD 
               *
          ;
          SELECT 
               *
          FROM "$(vSource)"."$(vTable)"
          ;
      
      
      STORE $(vTable) INTO '$(vSourcePath)\QVD\EXTRACT_$(vTable).qvd' (qvd);
          STORE $(vTable) INTO '$(vSourcePath)\QVD\EXTRACT_$(vTable).csv' (txt);
          
          DROP TABLE $(vTable);
      
      
      Next i
      
      
      Drop Tables MysticsTable;
      
      
      Let vSource =;
      Let vTable =;
      
      
      //*/
      

       

      Thank you.