Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How to open & Read Excel sheet Using VB

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
pnc
Asked:
pnc
  • 4
  • 3
  • 3
1 Solution
 
MarineCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
pncAuthor Commented:
Edited text of question.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MarineCommented:
Try this
xlSheet is WorkSheet
xlSheet.Rows.Find "ASSEST"

This will find your string ASSEST
0
 
MarineCommented:
OfCourse you can then loop and store it however you want.
0
 
pncAuthor Commented:
This question has a deletion request Pending
0
 
pncAuthor Commented:
I got the solution
0
 
TimCotteeHead of Software ServicesCommented:
This question no longer is pending deletion
0
 
TimCotteeHead of Software ServicesCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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