• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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