Working with Excel ranges 1

[stevesmith69]

Hello, I'm fairly new to Javascript and excel and have struggled with this for days now. No amount of Googling has revealed the answer to me.

How do I access the values in a named range in Excel using Javascript?

I can see how to access a single value in a known location e.g.

var1 = Excel.Workbooks.Open("C:\\TEST.xls").ActiveSheet.Cells(1,1).Value;

how do I retrieve the values in a named range e.g. "MyRange" and assign them to variables?

I can retrieve the address of the range but cant see how that helps me to loop through the values in the range and assign them. I have played with for in statements to try a for each approach to the range but can't get that to work.

Any help appreciated.

Thank you.

 

[BillyRayPreachersSon]

From reading the docs, I think you can use any of these:

.ActiveSheet.Range('A1')
.ActiveSheet.Range('A1:D1')
.ActiveSheet.Range('MyRangeName')

http://msdn.microsoft.com/en-us/library/aa176146(office.11).aspx

Hope this helps,
Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

Thank you BillyRayPreachersSon.

I appreciate that I can use .ActiveSheet.Range('MyRangeName') but not how I can get at the values contained in those cells?

How would .ActiveSheet.Range('MyRangeName').Value know which value to return from the many values contained in a range?

 

[BillyRayPreachersSon]

Oh yes - to iterate over them, you might have to use the "for .. in" construct. Unfortunately, I cannot test this as none of the machines here at work have Excel on them.

Something like:

var blah = foo.ActiveSheet.Range('MyRangeName');
for (cells in blah) {
   // do something with cells or blah[cells] here perhaps
}

Either that, or a bog-standard for loop.

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[BillyRayPreachersSon]

So perhaps using my above example, one of these might work:

var blah = foo.ActiveSheet.Range('MyRangeName');
for (cells in blah) {
   alert(blah[cells].Value);
}

var blah = foo.ActiveSheet.Range('MyRangeName');
for (var loop=0; loop<blah.length; loop++) {
   alert(blah[loop].Value);
}

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

Thanks again Dan.

That was exactly the approach I tried before posting in this forum - it failed for me with the debugger telling me on the alert line that

blah[...].Value is null or not an object

Now, I know that my test Excel sheet has a value in each of my tiny 2x2 testing range and can access them individually using the .Cells(1,1).Value approach so I can't think why I should get this error message.

 

[stevesmith69]

Oh and I discovered that blah.length is undefined in

var blah = foo.ActiveSheet.Range('MyRangeName');
for (var loop=0; loop<blah.length; loop++) {
   alert(blah[loop].Value);
}

when it comes to iterating through the loop. The code just falls through the loop without doing the alert.

If I replace blah.length with a value e.g. 4 then the loop is entered but fails on the alert line with the error message described above.

foo.ActiveSheet.Range('MyRangeName').Address; returns the actual range of the cells e.g. $A$1:$B$2 so I know I'm looking in the right place and that there are values in there. I'm stumped.

 

[BillyRayPreachersSon]

If you have a debugger, how about telling us what the Range command returns for you?

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

The debugger (Microsoft Script Debugger) returns a blank line for blah after executing

var blah = foo.ActiveSheet.Range('MyRangeName');

Trying

alert(blah);

returns

[object]

in the alert box so something is being assigned I assume.

 

[BillyRayPreachersSon]

OK - so try running this JS over the return value:

var s = '';
for (props in blah) {
	s += props + ': ' + blah[props] + '\n';
}
alert(s);

That should give us some idea of what is in [object]

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

Thanks Dan.

That code throws up a blank alert box.

 

[BillyRayPreachersSon]

Unfortunately, I'm out of ideas at the moment... although if you want to attach an example spreadsheet and code to a post in this thread, I can take a look at it tonight when I'm at home with Excel, if no-one else can suggest anything.

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

Ok Thanks.

<script type="text/javascript">

	var Worksheet;

	var Excel;

	Worksheet = "C:\\test.xls";
		
	Excel = new ActiveXObject("Excel.Application");	
	Excel.Visible = false;
	
	var Excel_file = Excel.Workbooks.Open(Worksheet);
	
	id = 1;

	var blah =  Excel_file.ActiveSheet.Range('MyRange');
		
	//alert(blah);
	//alert(blah.length);

	//for (var loop=0; loop<blah.length; loop++) 
	//{
   		//alert(blah[loop].Value);
	//}

	var s = '';
	for (props in blah) {
    	s += props + ': ' + blah[props] + '\n';
	}
	alert(s);

	//Clean up

	Excel.ActiveWorkbook.Close(true);
	Excel.Application.Quit();
	Excel = null;
  
        setTimeout("CollectGarbage()",1);

</script>

not sure if I ahve got the ahng of the attachment facility though.

 

[stevesmith69]

Be nice to be able to edit my posts too.

If I could i would have taken out the id = 1; line in the code.



The excel sheet is simple - just four arbitrary values (1,2,3,4) in A1:B2 and a defined name (MyRange) for that range. Nothing else.

 

[BillyRayPreachersSon]

Also you need to attach the file to an online URL. Unless you're sharing your C: drive to the whole of the internet, that is ;-)



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[Diancecht]

Where did you define your Range? You're accessing an empty Range ...

Cheers,
Dian

 

[stevesmith69]

The range is defined in Excel.

Excel_file.ActiveSheet.Range('MyRange').Address; returns $A$1:$B$2 which is the range defined as 'MyRange' in Excel.

 

[BillyRayPreachersSon]

Can you upload the Excel file online somewhere?



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/

 

[stevesmith69]

I'll try.

 

[BillyRayPreachersSon]

Wow. That was fun... I like brain challenges like this

The answer is:

var jsRangeArray = new VBArray(blah.Value).toArray();

This will give you a 1D array with x items, where x is the total number of cells in the range.

I found it in the end as my debugger was showing up blah.Value as an "array of variants", yet it had no properties I could inspect which set alarm bells ringing that it was a VB-only array. Converting to a JS array did the trick.

Hope this helps,
Dan





Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info:

http://www.codecouch.com/