Solved

How to open & Read Excel sheet Using VB

Posted on 2000-04-24
10
188 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
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: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

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

920 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

14 Experts available now in Live!

Get 1:1 Help Now