3 Replies Latest reply: May 16, 2013 9:28 AM by Yannick Durand RSS

    Piece of VBS script to define name in Excel

    Yannick Durand

      Hi all,

      I have this piece of code which doen't work in QV (but it's fine when i do the equivalent in Excel).

      Can anybody explain me why please ??

       

      This is the piece which doesn't work :

      the idea is to define a name range in excel (the number of line depends on the number of cells with data in them).

       

       

      ActiveDocument.GetSheetObject("TAB_SAISIE").ExportBiff "C:\test_" & nom_comite.String & ".xls"

      Set XLApp = CreateObject("Excel.Application")
      XLApp.Workbooks.Open("C:\test_" & nom_comite.String & ".xls")
      XLApp.Visible = True

       

      (...)

       

      XLApp.Sheets.Add
      XLApp.Sheets(1).Name ="bdd_noms"
      set XLSheet = XLApp.Sheets("bdd_noms")

      for i = 1 to 8
      ActiveDocument.GetSheetObject("bdd_nom_" & i).CopyTextToClipboard
      XLSheet.Cells(1 , i).Select
      XLSheet.Paste
      Next

       

      XLApp.Names.Add "statut_phase", "=OFFSET(bdd_noms!$A$2,0,0,COUNTA(bdd_noms!$A$2:$A$100),1)" 

       

       

      This last line is the line where is the error ("the formula you typed contains an error ........."

       

      Thanks !