How to Put an Excel Column into an Array

Keywords: 	  excel column array

Question:

Hi, I need some help using OLE and Excel.
I have a .xls and I want to find a row in the file, searching a value in a column in the file.
P.e:
CODE NAME 1 NAME1 2 NAME2
I want to store in a variable the row that contains NAME2 in the colum NAME.

After, how can i store in a variable each colum of the row selected?

Answer:

If I am reading this correctly, you want to store column values to variables. I have attached code to illustrate a simple ( and unsophisticated ) way to quickly put an Excel Column into an array.

The code relies on a File I uploaded for kali earlier today, named RAO.xls, and it is easy to conform the script to your spreadsheet.

Note that if you get an error: 1261 OLE:Exception, On Line, DB.OPEN(cConn), and you are using Excel 9.0: since you are using the OLEDB Provider in the code below, Excel 9.0 is part of MDAC 2.7 - however, 2.7 does not include the Jet Engine Upgrade, that is a separate download.

file  = "E:\WBDEMO\RAO.XLS"  ; change to suit your environment
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%file%;Extended Properties=Excel 8.0"
DB    = ObjectOpen("ADODB.Connection")

DB.Open(cConn)

cSQL  = "SELECT COMPANY FROM [RAO$]"
RS    = DB.Execute(cSQL)
cVar  = RS.GetString()
cVar  = StrClean(cVar,@CR,"|",@FALSE,1)
cVar  = StrClean( cVar,@TAB,"|",@FALSE,1)
cVar  = StrSub( cVar, 1, StrLen(cVar)-1 )
aVar  = Arrayize(cVar,"|")
n     = ArrInfo(aVar,1)

DB.Close()
ObjectClose(DB)

For i = 0 To ( n-1 )
   j  = i+1
   Message("Company %j% of %n%",aVar[i])
Next

Exit

Article ID:   W15257

File Created: 2002:11:14:15:08:06

Last Updated: 2002:11:14:15:08:06

Database Search

TechHome

How to Put an Excel Column into an Array

Question:

Answer: