Solved

reading from a column in excel workbook

Posted on 2000-05-11
6
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

729 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