Piece of VBS script to define name in Excel

[#1]

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 !

[marcus_sommer]

Sheet and Range in xls isn't right adressed - try this:

XLApp.Names.Add "statut_phase",

"=OFFSET(XLSheet.Cells(2, 1),0,0,COUNTA(XLSheet.Range(Cells(2, 1),cells(100, 1))),1)"

- Marcus

[#3]

Hi Marcus

Thank you for your interest in my problem.

I still have as error when I try your suggestion.

Isn't it strange that have QV macro expression (XLSheet) between the " ", which is supposed to be Excel Langage ?

[#4 · Accepted solution]

OK I just found by accident,

I have to replace the  , by ;