Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Working with Excel ranges 1

Status
Not open for further replies.
stevesmith69

stevesmith69

Technical User
Mar 13, 2009
14
GB
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.
 
Sort by date Sort by votes
Glad to give you the challenge. :)

Many thanks for all your help Dan.
 
Upvote 0 Downvote
I'm really sorry Dan, but I can't get that to work. jsRangeArray is still null or not an object. Perhaps I'm not using it correctly - could you throw up your code please?
 
Upvote 0 Downvote
[0] I reference to your post of 13 Mar 09 8:34 only (the rest I ignore).

[1] It is not clear what you meant by prop, blah[prop]. If you mean the cell's address and its value, it is this.
[tt]
var s = '';
[red]//[/red]for (props in blah) {
[red]//[/red]s += props + ': ' + blah[props] + '\n';
[red]//[/red]}
[blue]for (var i=0;i<blah.cells.count;i++) {
s+=blah.cells(i+1).address(true) + " : " + blah.cells(i+1).value + "\n";
}[/blue]
alert(s);
[/tt]
 
Upvote 0 Downvote
Odd that it's not working for you. Here's my cut-down code, which shows '4':

Code: 
<html>
<head>
	<script type="text/javascript">
		var Worksheet = 'C:\\test.xls';
		var Excel = new ActiveXObject('Excel.Application');

		Excel.Visible = false;
		var Excel_file = Excel.Workbooks.Open(Worksheet);

		var blah = Excel_file.ActiveSheet.Range('MyRange');
		var jsRangeArray = new VBArray(blah.Value).toArray();
		alert(jsRangeArray.length); 	// Should show 4

		Excel.ActiveWorkbook.Close(true);
		Excel.Application.Quit();
		Excel = null;
	</script>
</head>

<body>
</body>
</html>

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:
Code Couch Tech Snippets & Info:
 
Upvote 0 Downvote
Yes, I too got a length of 4 for the array but still get null or not an object error when referencing the contents of the array.
 
Upvote 0 Downvote
Then I'd say you're doing something strange to read the data from the array. With the following, I see 10, 20, 30, 40:

Code: 
<html>
<head>
	<script type="text/javascript">
		var Worksheet = 'C:\\test.xls';
		var Excel = new ActiveXObject('Excel.Application');

		Excel.Visible = false;
		var Excel_file = Excel.Workbooks.Open(Worksheet);

		var blah = Excel_file.ActiveSheet.Range('MyRange');
		var jsRangeArray = new VBArray(blah.Value).toArray();
		alert(jsRangeArray.length); 	// Shows 4
		alert(jsRangeArray[0]);			// Shows 10
		alert(jsRangeArray[1]);			// Shows 20
		alert(jsRangeArray[2]);			// Shows 30
		alert(jsRangeArray[3]);			// Shows 40

		Excel.ActiveWorkbook.Close(true);
		Excel.Application.Quit();
		Excel = null;
	</script>
</head>

<body>
</body>
</html>

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:
Code Couch Tech Snippets & Info:
 
Upvote 0 Downvote
Me too now!

Really pleased to get this working. Many thanks for all your help Dan. I would never in a million years of cracked this. Much appreciated.

Steve.

:)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

edgarv
  • Locked
  • Question
Field with numbers and characters get nan
Replies
1
Views
107
ggriffit
ggriffit
tonynsx
  • Locked
  • Question
document.getElementById(&quot;main&quot;).innerHTML not working
Replies
1
Views
112
ggriffit
ggriffit
HansMaulwurf
  • Locked
  • Question
AJAX not working after database migration from MySQL to SQL Server ( Undefined index)
Replies
0
Views
88
HansMaulwurf
HansMaulwurf
rogerte
  • Locked
  • Question
Javascript updating form control not working
Replies
3
Views
147
rogerte
rogerte
JPSeo
  • Locked
  • Question
Another cjhild_process.exec issue with complex shell command
Replies
0
Views
76
JPSeo
JPSeo

Part and Inventory Search

Sponsor

Back
Top