Sign in to follow this  
Followers 0
apoliemans

Create a named range in Excel

8 posts in this topic

Hi,

Having problems creating a named range in Excel.

What is the autoit equivalent of this VBS code:

ActiveWorkbook.Names.Add Name:="tempRange", RefersTo:="=Sheet1!$A$1:$D$3"

Thanks, Andre

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Welcome to AutoIt and the forum!

How do you open the workbook? Do you use the Excel UDF?

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

These are (some of) the statements I have at the start of my script:

$Filename = "somefile.xls"
Local $oExcelDoc = _ExcelBookOpen($Filename, 1, True)
$oExcelDoc.workbooks.add

In the active workbook I want to define a named range.


 

Share this post


Link to post
Share on other sites

Try:

#include <Excel.au3>
Global $Filename = "somefile.xls"
Global $oExcelDoc = _ExcelBookOpen($Filename, 1, True)
Global $oWorkBook = $oExcelDoc.workbooks.add
$oWorkBook.Names.Add("tempRange", "=Sheet1!$A$1:$D$3")

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Works, thank you!

Andre

Share this post


Link to post
Share on other sites

I'm about to rewrite the Excel UDF. Do you think there should be a function to handle names of ranges (add name, list names ...)?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Right now I am quite happy with the example you gave above.

Note that I am new to Autoit (since 1 week).

My next challenge will be to create a Pivot table using this named range, I may start a new topic if I need help with that.

Andre

Share this post


Link to post
Share on other sites

If you like you could have a look at the rewrite of the Excel UDF I'm working on right now.

It supports the curretn version of Excel and some functions are much faster.

Unfortunately it's still in an early Alpha state.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0