How to set the scope of an Excel name range

[dejhost]

Hi!
I am using the function 

$tWorkbook.Names.Add

to create a name range in Excel. With the following line, I set a name ("pixel") , as well as a dynamic reference:

$tWorkbook.Names.Add("pixel", "=OFFSET("& $Filenames[$i] & "!$A$2;0;0;COUNTA(" & $Filenames[$i] &"!$A$2:$A$2000);1)")

I  would like to set the scope of the range name as well. It seems, that "Workbook" is the default, but I'd rather choose a sheet.

Thank you for helping.

 

Edited January 25, 2019 by dejhost

[Nine]

range.activate (for single cell) and/or range.select (for whole range) ?

[water]

This page describes how to set a name range on a worksheet: https://docs.microsoft.com/en-us/office/vba/api/excel.name

[dejhost]

I'm not very familiar with the terminology, and I don't understand your question, Nine... so I'd rather draw you a picture:

1) This is what my code (see thirst thread) does:

2) But: What I want is this: 

I looked at your link, water, but couldn't make out a property called "source". I only find "name", "Comment" and "RefersTo".

 

[water]

My Excel version does not support SourceData as Scope
But you could record an Excel macro, create a new name with Scope of "SourceData", end the macro recording and then post the resulting VBA code.

[Subz]

@Water - I think the sheet is called "SourceData", please correct me if I'm wrong @dejhost

So you want to use something like:

$oWorkbook.WorkSheets("SourceData").Names.Add(...

 

[water]

I'm running Excel 2016 and it allows to use Workbook and Worksheet as "Scope". So I thought DataSource is something different.

[Nine]

 @dejhost Maybe you could give us access to your Excel file and post a self-contain snippet of code that doesn't work.  It would make it easier instead of us trying to guess...

Edited January 26, 2019 by Nine

[dejhost]

Subz, you're correct. "Sourcedata" is the name of the sheet.

While recording the macro, I have chosen the excelsheet "Summary" instead of "Sourcedata". Makes it easier to distinguish from the cell range used.

ActiveWorkbook.Worksheets("Summary").Names.Add Name:="grayvalues", _
        RefersToR1C1:= _
        "=OFFSET(SourceData!R2C1,0,0,COUNTA(SourceData!R2C1:R2000C1),1)"
    ActiveWorkbook.Worksheets("Summary").Names("grayvalues").Comment = ""
End Sub

So I guess my autoit code should be:
$tWorkbook.Worksheets("SourceData").Names.Add("pixel", "=OFFSET("& $Filenames[$i] & "!$A$2;0;0;COUNTA(" & $Filenames[$i] &"!$A$2:$A$2000);1)")

[dejhost]

7 minutes ago, Nine said:

 @dejhost Maybe you could give us access to your Excel file and post a self-contain snippet of code that doesn't work.  It would make it easier instead of us trying to guess...

Sure...

Exceltemplate.xlsx

 

 

[dejhost]

15 minutes ago, dejhost said:

 

So I guess my autoit code should be:
$tWorkbook.Worksheets("SourceData").Names.Add("pixel", "=OFFSET("& $Filenames[$i] & "!$A$2;0;0;COUNTA(" & $Filenames[$i] &"!$A$2:$A$2000);1)")

 

Nope. Doesn't work. But then again: VBA is not Autoit, right :-)

Edited January 26, 2019 by dejhost
shortened

[Subz]

Worked for me Office 2016 32bit:
Note: My region uses "," rather than ";", also I didn't see any .jpg file sheet names so not sure what you're referencing.

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\ExcelTemplate.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $Filenames[1] = ["DSC08291.JPG"]
$oWorkbook.WorkSheets("SourceData").Name = $Filenames[0]
$oWorkbook.WorkSheets($Filenames[0]).Names.Add("Pixel", "=OFFSET(" & $Filenames[0] & "!$A$2,0,0,COUNTA(" & $Filenames[0] & "!$A$2:$A$2000),1)")

 

Edited January 26, 2019 by Subz
Updated example

[dejhost]

Should I explain what I am trying to achieve?

1. The user of my autoit script may open one or several image files.
2. For each image file, an Excelsheet shall be provided. So Autoit opens the provided Exceletemplate.xlsx, and copies and renames the Sheet "SourceData" accordingly. 
3. Autoit shall also create this "dynamic Name range" as a basis for the shown chart. So the chart get is data via "Sheetname!Dynamic-Range-name".

At a later stage, Autoit fills the table (columns A-D) with data. The chart "Contrast" adapts automatically.

 

Edited January 26, 2019 by dejhost

[Subz]

"Nope. Doesn't work." really doesn't help, as I mentioned in the example I posted above it works fine for me, you may want to add some error checking to figure out why its not working for you.

[dejhost]

1 minute ago, Subz said:

"Nope. Doesn't work." really doesn't help, as I mentioned in the example I posted above it works fine for me, you may want to add some error checking to figure out why its not working for you.

Sorry, I should have explained. I'm working on the error tracking...

[dejhost]

Thank you guys, I got the command working. Reason for my trouble: I was messing up the workflow.

My script was first renaming the Excelsheet, and then I tried to define the name (with the dynamic range) - based on the former sheetname. This resulted in an error (*surprise"). 

So now, the code works.

However, it doesn't solve the problem I was hoping to solve (beyond my initial question): As I mentioned, each file opened by the user receives its own Excelsheet.

Through the process of duplicating the excelsheet, the chart looses its data reference (exchanges the name-range through columns and lines). See the attached file (Sheet 2-5), and my code below.

;Opening the Excelworkbook template

    Local $oExcel = _Excel_Open(True, False, True, True)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Global $tWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True, "","","0")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    If WinSetState ("Exceltemplate.xlsx - Excel" ,"",  @SW_MAXIMIZE) = 0 Then
        msgbox($MB_SYSTEMMODAL,"Error maximizing Excel" , "Exceltemplate.xlsx - Excel")
    EndIf

$tWorkbook.Sheets(2).Activate

;Copy, and rename sheets in case of several images
If $counter = 1 Then
    For $i = 2 to Ubound($Filenames)-1
        $tWorkbook.Sheets($i).Activate
;rename:
        $tWorkbook.Sheets($i).Name = $Filenames[$i]
        Sleep(300)
;insert range name
        $tWorkbook.WorkSheets($Filenames[$i]).Names.Add("Pixel", "=OFFSET(" & $Filenames[$i] & "!$A$2;0;0;COUNTA(" & $Filenames[$i] & "!$A$2:$A$2000);1)")
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "$tWorkbook.Names.Add", "Error creating name for range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        $tWorkbook.WorkSheets($Filenames[$i]).Names.Add("grayvalues", "=OFFSET("& $Filenames[$i] & "!$B$2;0;0;COUNTA(" & $Filenames[$i] &"!$B$2:$B$2000);1)")
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "$tWorkbook.Names.Add", "Error creating name for range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;copy, except for last run
        If $i < Ubound($Filenames)-1 Then
            Local $oCopiedSheet = _Excel_SheetCopyMove($tWorkbook, 2, $tWorkbook, $i, False, True)
                Sleep(100)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove", "Error copying sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                Sleep (200)
        EndIf
    Next
EndIf

Do you have a suggestion how I could get the correct charts on every Excelsheet?

GDI_Results.xlsx

[water]

Why not create all needed copies and then process each copy individually?

[dejhost]

3 hours ago, water said:

Why not create all needed copies and then process each copy individually?

I'm not sure whether I understand what you mean. Testing shows, that during the process of copying the sheet, the chart looses its reference to the named range. It gets replaced with a static reference to row and column.

Or are you suggesting to copy the entire workbook n-times for n images? And once all data and charts are in place, I could copy the sheets into one Workbook? In this case the loss of the dynamic range during the copy process wouldn't matter anymore... should work, but for a large amount of imagefiles, I would have to create  large amount of excelfiles...

I tried using VBA macros again, this time to select the source of the chart:

Sub selectsource_1()
'
' selectsource_1 Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).XValues = "==SourceData!pixel"
    ActiveChart.FullSeriesCollection(1).Values = "==SourceData!grayvalues"
End Sub

The debugger stops at the line "ActiveChart.FullSeriesCollection(1).XValues = "==SourceData!pixel"" with "Run-time error 1004". 

Further, I have no clue how to translate it into autoit script.

Maybe the easiest way is to have Autoit perform some mouseclicks, and set the source of the chart using the "send"-command?

 

 

[dejhost]

I realize that this has by far more to do with Excel than Autoit, so I should try another forum.

Anyway: If somebody knows a better workaround than mouseclicks on the chart and insert the correct data source... please share!

[water]

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.

 

Edited January 28, 2019 by water