Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • 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
 
TimCotteeCommented:
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
Independent Software Vendors: 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!

 
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
 
TimCotteeCommented:
This question no longer is pending deletion
0
 
TimCotteeCommented:
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
 
TimCotteeCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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