How to set the scope of an Excel name range

[dejhost]

6 minutes ago, water said:

Yes, I do
Did you have a look at my ExcelChart UDF? it should be possible to update the data source by using Excel COM.
For download please see my signature.

 

Thanks for the hint. I took a look at the Excelchart UDF before I started to work on this tool. I figured I would be fine copying the sheets (including a pre-designed chart with the dynamic name range), and that this would be the easier solution. Well, guess I was wrong :-)  I will come back to Excelchart UDF.

However: I already wrote the Function for changing the data source "manually". Maybe it's saving somebody else some time:

Func _ChangeChartData($Sheetname)
    Opt("MouseCoordMode", 2) ;1=absolute, 0=relative, 2=client
    WinActivate("Excel")
    MouseClick($MOUSE_CLICK_LEFT, 728, 545, 1, 10)
    Sleep (50)
    MouseClick($MOUSE_CLICK_Right, 728, 545, 1, 10)
    Sleep (50)
    Send("{e}")
    Sleep (50)
    WinActivate("Select Data Source")
    Sleep (50)
    MouseClick($MOUSE_CLICK_LEFT, 122, 128, 1, 10)
    Sleep (50)
    WinActivate("Edit Series")
    Send("{Tab}")
    Sleep (50)
    Send("='" & $Sheetname & "'" & "{!}" & "pixel")
    Sleep (50)
    Send("{Tab}")
    Sleep (50)
    Send("='" & $Sheetname & "'" & "{!}" & "grayvalues")
    Sleep (50)
    Send("{Enter}")
    Sleep (50)
    WinActivate("Select Data Source")
    MouseClick($MOUSE_CLICK_LEFT, 448, 270, 1, 10)
    Opt("MouseCoordMode", 1)    
EndFunc   ;==>_ChangeChartData