Solved

How to open & Read Excel sheet Using VB

Posted on 2000-04-24
10
184 Views
Last Modified: 2010-05-03
Hi TimCottee,
Using VB,Excel I want to find a Row which has value of "ASSETS"
I tried using below code.Mr.TimCottee could u please find the my solution.

Dim xlsApp As Excel.Application
    Dim xlsWB As Excel.Workbook
    Dim xlsWS As Excel.Worksheet
    On Error Resume Next
    Set xlsApp = GetObject(, "Excel.Application") 'look for a running copy of Excel
    If Err.Number <> 0 Then 'If Excel is not running then
        Set xlsApp = CreateObject("Excel.Application") 'run it
    End If
    Set xlsWB = xlsApp.Workbooks.Open(App.Path & "\NIAtemplate.xlt")
    xlsApp.Visible = True
    Set xlsWS = xlsWB.Sheets("Sheet1")
   
    'MsgBox "Record Count: " & CStr(xlsWS.Rows.CurrentRegion.Rows.Count)
    MsgBox "Record Count: " & CStr(xlsWS.Rows.CurrentRegion.Rows.Count)
   
    xlsApp.Quit
    Set xlsApp = Nothing
0
Comment
Question by:pnc
  • 4
  • 3
  • 3
10 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2746057
I don't know what you really looking for but if you only want to find in one particular Sheet you shouldn't even loop though sheet collection. it's just not necessary.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2746834
pnc, I think that what you are trying to do is to return the number of rows in each sheet, assuming that the first cell in the last row of each sheet contains "ASSETS". If this is the case then the following example would probably help.

Private Sub Command2_Click()
    Dim xlsApp As Excel.Application
    Dim xlsWB As Excel.Workbook
    Dim xlsWS As Excel.Worksheet
    On Error Resume Next
    Set xlsApp = GetObject(, "Excel.Application") 'look for a running copy of Excel
    If Err.Number <> 0 Then 'If Excel is not running then
        Set xlsApp = CreateObject("Excel.Application") 'run it
    End If
    Set xlsWB = xlsApp.Workbooks.Open(C:\powerSTEPS\NIAtemplate.xlt)
    xlsApp.Visible = True
    Set xlsWS = xlsWB.Sheets("Sheet1")
    MsgBox "Record Count: " & CStr(xlsWS.Rows.CurrentRegion.Rows.Count)
    xlsApp.Quit
    Set xlsApp = Nothing
End Sub


0
 

Author Comment

by:pnc
ID: 2748500
Edited text of question.
0
 
LVL 6

Expert Comment

by:Marine
ID: 2748546
Try this
xlSheet is WorkSheet
xlSheet.Rows.Find "ASSEST"

This will find your string ASSEST
0
 
LVL 6

Expert Comment

by:Marine
ID: 2748547
OfCourse you can then loop and store it however you want.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:pnc
ID: 2749253
This question has a deletion request Pending
0
 

Author Comment

by:pnc
ID: 2749293
I got the solution
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2750582
This question no longer is pending deletion
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2750583
pnc, you got the solution? where from might I ask, My original code + Marine's certainly provided one. If not the one you used, then could you post you solution. This might prove useful to someone else accessing this site.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
ID: 2844021
pnc, as you haven't responded, I am assuming that the answer you got was from either myself or Marine so I am proposing this as an answer.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 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

19 Experts available now in Live!

Get 1:1 Help Now