• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Read in data from an excel spreadsheet?


Hi,

I am using a vbScript and I need to be able to read in 4 columns of data from an excel file, and place these values into a dynamic array of some sort.

There are 4 columns of data in the excel sheet.  The columns will be a different length everytime the code is ran, but the columns will be the same length as each other.

So after the code has executed I need to have the data from the 4 columns in an array (or 4 different arrays??), in the same order as it was in the excel spreadsheet.

Cany anyone provide this code?
Many Thanks
Steven
0
sterankin
Asked:
sterankin
  • 4
  • 3
1 Solution
 
R_RajeshCommented:

FirstColumn = 1 '1=A,2=B...
Set xlWkb = CreateObject("Excel.Application").workbooks.open("C:\SampleBook.xls")
With xlWkb.sheets(1)
myArray = .Range(.cells(FirstColumn, 1), _
.cells(.Range("D65536").end(&HFFFFEFBE).row, FirstColumn + 3))
End With
xlWkb.Close False

For i = LBound(myArray) To UBound(myArray)
For j = 1 To 4
str1 = str1 & myArray(i, j) & vbTab
Next j
str1 = str1 & vbCrLf
Next i
MsgBox str1
0
 
R_RajeshCommented:
Slight modification. D65536 was hardcoded...

FirstColumn = 1 '1=A,2=B...
Set xlWkb = CreateObject("Excel.Application").workbooks.open("C:\SampleBook.xls")
With xlWkb.sheets(1)
myArray = .Range(.cells(FirstColumn, 1), _
.cells(.cells(65536, FirstColumn).end(&HFFFFEFBE).row, FirstColumn + 3))
End With
xlWkb.Close False

For i = LBound(myArray) To UBound(myArray)
For j = 1 To 4
str1 = str1 & myArray(i, j) & vbTab
Next j
str1 = str1 & vbCrLf
Next i
MsgBox str1
0
 
sterankinAuthor Commented:

Thanks - quick query before I resolve this - is there anyway I can ignore the first cells, that is ignore A1, B1, C1 and D1?
As these are the column headers.

Thanks for the help.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
R_RajeshCommented:
replace
myArray = .Range(.cells(FirstColumn, 1), _
.cells(.cells(65536, FirstColumn).end(&HFFFFEFBE).row, FirstColumn + 3))
with
myArray = .Range(.cells(FirstColumn, 2), _
.cells(.cells(65536, FirstColumn).end(&HFFFFEFBE).row, FirstColumn + 3))

second argument in cells(FirstColumn, 2) determines which row the range starts from...
0
 
sterankinAuthor Commented:
Thanks - I did try that but I got a subscirpt out of range error at thsi line:

str1 = str1 & myArray(i, j) & vbTab

any ideas?
0
 
R_RajeshCommented:
Sorry, had row and column mixed up...

myArray = .Range(.cells(2, FirstColumn), _
.cells(.cells(65536, FirstColumn).end(&HFFFFEFBE).row, FirstColumn + 3))
0
 
sterankinAuthor Commented:
Thanks for the help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now