Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Programming > Visual Basic Programming
Reload this Page

Accessing Excel cells from ASP/VBScript

Discuss Accessing Excel cells from ASP/VBScript in the Visual Basic Programming forum on ASP Free.
Accessing Excel cells from ASP/VBScript Visual Basic Programming forum discussing coding strategies, development and techniques. Go from prototype to product quickly using this robust platform.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old April 21st, 2005, 12:06 AM
cjreynolds cjreynolds is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Mar 2005
Posts: 361 cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 3 Days 11 h 47 m 7 sec
Reputation Power: 5
Accessing Excel cells from ASP/VBScript
I posted this in the ASP Development forum to no avail - I'm thinking this may be more of a VBA thing...

I'm trying to grab cell values from an excel file using ASP with VBScript. This is the code I'm using:

Code:
<%
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "c:\inetpub\wwwroot\asp\calendar\test.xls"
set ExcelRS = ExcelConn.Execute ("SELECT * FROM [Sheet1$]")
 
Response.Write(ExcelRS(1,1))
 
%>


I've tried Response.write(ExcelRS($A$1)) and every other variation I can think of. What is the syntax for refering to a cell location from VBScript/VBA/etc... ?
Reply With Quote
  #2  
Old April 22nd, 2005, 03:15 AM
hithere's Avatar
hithere hithere is offline
Learner
ASP Free Newbie (0 - 499 posts)
  
Join Date: Nov 2004
Location: India
Posts: 450 hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 12 h 13 m 21 sec
Reputation Power: 18
Try with
Response.write(ExcelRs(0)) --- for referring to first column of excel sheet and similarly u can fetch values for different columns

Quote:
Originally Posted by cjreynolds
I posted this in the ASP Development forum to no avail - I'm thinking this may be more of a VBA thing...

I'm trying to grab cell values from an excel file using ASP with VBScript. This is the code I'm using:

Code:
<%
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "c:\inetpub\wwwroot\asp\calendar\test.xls"
set ExcelRS = ExcelConn.Execute ("SELECT * FROM [Sheet1$]")
 
Response.Write(ExcelRS(1,1))
 
%>


I've tried Response.write(ExcelRS($A$1)) and every other variation I can think of. What is the syntax for refering to a cell location from VBScript/VBA/etc... ?
Reply With Quote
  #3  
Old April 25th, 2005, 12:08 AM
cjreynolds cjreynolds is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Mar 2005
Posts: 361 cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 3 Days 11 h 47 m 7 sec
Reputation Power: 5
Thanks, HiThere... but that gets me row 2 (A2, B2, etc.). How do I get to the other rows? I've tried about every number from 0 to 260, and once I get past 3 (D2, the last cell in row 2), I get:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Am I brain dead, and overlooking something obvious?

joe
Reply With Quote
  #4  
Old April 25th, 2005, 05:59 AM
hithere's Avatar
hithere hithere is offline
Learner
ASP Free Newbie (0 - 499 posts)
  
Join Date: Nov 2004
Location: India
Posts: 450 hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level)hithere User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 12 h 13 m 21 sec
Reputation Power: 18
Use this for looping thru the rows. Here i have used to retreive data from three columns A, B and C
Do until Excelrs.eof
msgbox rs(0)
msgbox rs(1)
msgbox rs(2)
rs.movenext
Loop
I hope this helps u.
Hey according to me why it begins from row 2 is because it assumes that row 1 is having column names.
If ur excel file doesnot contains columns then use connection string as
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0; HDR=No;"


Quote:
Originally Posted by cjreynolds
Thanks, HiThere... but that gets me row 2 (A2, B2, etc.). How do I get to the other rows? I've tried about every number from 0 to 260, and once I get past 3 (D2, the last cell in row 2), I get:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Am I brain dead, and overlooking something obvious?

joe
Comments on this post
cjreynolds agrees: Immense help! Thanks!
Reply With Quote
  #5  
Old April 25th, 2005, 11:08 AM
cjreynolds cjreynolds is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Mar 2005
Posts: 361 cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level)cjreynolds User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 3 Days 11 h 47 m 7 sec
Reputation Power: 5
Awesome! Just what the doctor ordered...

Thanks, HiThere!
Reply With Quote
Reply

Viewing: ASP Free Forums > Programming > Visual Basic Programming > Accessing Excel cells from ASP/VBScript

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway. 
Stay green...Green IT