Solved

reading from a column in excel workbook

Posted on 2000-05-11
6
166 Views
Last Modified: 2010-05-02
how to read in values from a specific column in excel workbook 1 by 1 to a variable
0
Comment
Question by:bazukei
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:ture
ID: 2799265
bazukei,

This procedure loops through A1 - A20 and reads each value into the variable x, one at a time.

(For this to work in VB, you must set a reference to the MS Excel library.)

Sub ReadExcelValues()

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim r As Long
Dim x As Variant

Set wb = xl.Workbooks.Open("c:\test\test.xls")
Set ws = wb.Worksheets("Sheet1")
For r = 1 To 20
  x = ws.Cells(r, 1)
  MsgBox x
Next r
wb.Close SaveChanges:=False
xl.Quit
Set wb = Nothing
Set xl = Nothing

End Sub

Ture Magnusson
Karlstad, Sweden
0
 
LVL 1

Accepted Solution

by:
manchula earned 20 total points
ID: 2799267
Try this code. This will use the opened Excel File to read the value. This example gives the first cell (row 1, column 1) value.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet


Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Sheets(1)

'Use xlSheets.Cells(RowIndex, ColIndex)
MsgBox xlSheet.Cells(1, 1)


0
 

Author Comment

by:bazukei
ID: 2799408
Adjusted points from 10 to 20
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:bazukei
ID: 2799409
thanks
both answers are good but

ture .. your code won't get me to the right sheet
my desired sheet is 3 and column is X
from row 2 to 921

manchula.. your code won't get me to X column and after 389 rows it won't display anything from the cell eventhough i have the data

please try again

thank you all very much

0
 
LVL 1

Expert Comment

by:manchula
ID: 2799438
bazukei, i did not find anything wrong in that code. it works even for more than 5000 rows.

can you please tell me in detail about your code, or how you want to pick up the data.
0
 
LVL 22

Expert Comment

by:ture
ID: 2799449
bazukei,

I hope that this suits you better:

This procedure opens the workbook c:\test\test.xls and reads each value in Sheet3, range X2:X921 into the variable x

Sub ReadExcelValues()

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim r As Long
Dim x As Variant

Set wb = xl.Workbooks.Open("c:\test\test.xls")
Set ws = wb.Worksheets("Sheet3")
For r = 2 To 921
  x = ws.Cells(r, 24)
  MsgBox x
Next r
wb.Close SaveChanges:=False
xl.Quit
Set wb = Nothing
Set xl = Nothing

End Sub

/Ture
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now