6 Replies Latest reply: Sep 26, 2014 11:24 AM by Simon Brulotte RSS

    Append table to archive

    Simon Brulotte

      Hi,

      I have a table that has comments that are input fields.

       

      INPUT FIELD Commentaires;

      Référentiel_Règles:
      LOAD
      Timestamp(now(),'YYYY-MM-DD hh.mm') as Date_archive,

      NUM_REGLE,
       
      Description_regle,
       
      [Description du contrôle],
        
      Commentaires,
        
      applymap('BRIS_MAP', NUM_REGLE, NULL()) as Bris,
          
      Nom_variable_regle
      FROM
      [$(ClientDataDir)\Conformité.xlsx]
      (
      ooxml, embedded labels, table is Regles);

       

       

      I want to keep a log of this table, so I tried to create a chart object that I export via macro to csv file using

       

      Sub ExportToFile

      set sObject = ActiveDocument.GetSheetObject("CH01")

      sObject.Export "C:someplace\TestFile.csv", "; "

      End Sub

       

       

      Any suggestions on how to append new comments/now() values created from the first table to a log file?

        • Re: Append table to archive
          Simon Brulotte

          As of right now, I am just loading the exported file with matching name colomns.

           

           

          INPUTFIELD Commentaires;
          Archives_Regles:
          LOAD Date as Date_archive,
              
          Règle as NUM_REGLE,
              
          État as Bris,
              
          Description as Description_regle,
              
          Commentaire as Commentaires
          FROM
          [G:\someplace\TestFile.csv]
          (
          txt, utf8, embedded labels, delimiter is ';', msq);

          INPUTFIELD Commentaires;
          Référentiel_Règles:

          LOAD

           
          Timestamp(now(),'YYYY-MM-DD hh.mm') as Date_archive,
           
          NUM_REGLE,
              
          Description_regle,
               Commentaires,
              
          applymap('BRIS_MAP', NUM_REGLE, NULL()) as Bris  
          FROM
          [$(ClientDataDir)\Conformité.xlsx]
          (
          ooxml, embedded labels, table is Regles);

           

           

          One big table with all values archived.

          My problem is now to have a chart that shows only the max value for "Date_archive"

          so that the users input only for the now() rows...

           

          Not scripting, so I might ask elsewhere.

           

           

            • Re: Append table to archive
              konsta klmnsta

              use Max(Date_archive) in the expression

              • Re: Re: Append table to archive
                Simon Brulotte

                I am encountering a problem with this.

                 

                My chart has a set analysis to retrieve only the last value of my archive file:

                sum({<Date_archive ={$(vDate_archive)}>} Bris

                variable overview:

                vDate_archive  = chr(39)&maxstring(Date_archive)&chr(39)


                Here is the problem:

                 

                When I load the excel file that has the original data, the comments for the last date get replaced by what's in the excel file.

                I want to show the value from the last archived date.

                 

                I thus need a maxstring -1 or a second-to-last-string...

                 

                Does that exist?

                  • Re: Re: Append table to archive
                    Simon Brulotte

                    Also, for some reason, Now() is stuck at 25-09-2014 15:09...

                    I traced it in a seperate variable and always get that timestamp... :/

                      • Re: Append table to archive
                        Simon Brulotte

                        I ended up just using a macro to append my current second table, Référentiel_Règles, to an excel file that I never load in my model. It works great since the current Input field value is always in memory, never replaced, and there's no inteference with older log values.

                         

                        Here's the VBA used:

                         

                         

                        Sub Test 

                         

                           ExcelAppend "C:Some palce/TestFile.xlsx", "CH32" 

                         

                        End Sub 

                         

                        Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID) 

                         

                           ' Create an instance of Excel 

                         

                           SET objExcelApp = CREATEOBJECT("Excel.Application") 

                         

                            

                         

                           ' Open workbook 

                         

                           WITH objExcelApp 

                         

                              .DefaultSaveFormat = xlWorkbookNormal 

                         

                              .DisplayAlerts = FALSE 

                         

                              .Workbooks.Open strExcelAppenFile 

                         

                              .DisplayFullScreen = FALSE 

                         

                              .Visible = FALSE 

                         

                           END WITH 

                         

                            

                         

                           ' Set worksheet 

                         

                           SET objExcelSheet = objExcelApp.Worksheets(1) 

                         

                               

                         

                           ' Set Excel used range 

                         

                           SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 

                         

                           ' Last used row in column A 

                         

                           intExcelLastRow = objExcelRange.Row 

                         

                           ' Set object to append from 

                         

                           SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID) 

                         

                           ' Loop all rows of the object except first header row 

                         

                           FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 

                         

                              ' Loop all columns of the object 

                         

                              FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 

                         

                                 ' Get object data 

                         

                                 SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 

                         

                                 ' Add that data to Excel cell 

                         

                                 objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 

                         

                              NEXT 

                         

                           NEXT 

                         

                            

                         

                           ' Save and quit 

                         

                           objExcelSheet.SaveAs strExcelAppenFile 

                         

                           objExcelApp.Application.Quit 

                         

                           SET objExcelSheet = NOTHING       

                         

                           SET objExcelApp = NOTHING 

                         

                        END SUB