Find a specific row in excel

Question

0.00/5 (No votes)

See more:

Hello, guys, I m trying to find a row in excel that has "yes" written in a column A, but im getting an error in this line:

rng = xlsheet.Range("A").EntireColumn

, that says:

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'

What I have tried:

Dim xlApp As Excel.Application = Nothing
          Dim xlWorkbook As Excel.Workbook = Nothing

          Dim rng As Excel.Range
          Dim xlsheet As Excel.Worksheet
          Dim Path As String = "AA.xlsx"
          xlApp = New Excel.Application
          xlApp.Visible = True
          Path = System.IO.Path.GetFullPath(Path)
          xlWorkbook = xlApp.Workbooks.Open(Path)
          xlsheet = CType(xlWorkbook.Sheets(level), Excel.Worksheet)
          rng = xlsheet.Range("A").EntireColumn
          Dim xlCurRow as long
          xlCurRow = rng.Find("Yes").Row

Posted 8-May-20 7:31am

Marc Kane

Updated 10-May-20 23:39pm

Add a Solution

Comments

ZurdoDev 8-May-20 14:12pm

I would suggest doing it in VBA first, then convert to VB.NEt.

ZurdoDev 8-May-20 14:13pm

xlsheet is probably null.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-05-10T23:40:00

Debug your code - if you are not sure how then follow the instructions here Tutorial: Debug Visual Basic code - Visual Studio | Microsoft Docs[^]

Look at the line of code that is throwing the exception

VB

rng = xlsheet.Range("A").EntireColumn

There are only two things in that line that are not part of the language syntax and those are the variables rng and xlsheet. You are trying to assign a value to rng and it has been properly declared within the scope of this code so it's unlikely to be that. So have a look at xlsheet. It is assigned a value (we think) in the line above that

VB

xlsheet = CType(xlWorkbook.Sheets(level), Excel.Worksheet)

Excamine the variable in the debugger, you will probably find that it is "Nothing". Getting a value into xlsheet depends on a couple of other variables - xlWorkbook and level

If level has not been set correctly and is 0 I would expect that to have been the line that threw the exception (excel sheets start at 1). But if you examine xlsheet in your IDE / Debugger you will probably find that it is "Nothing". To be honest, I'm not sure why you are using CType there - Sheets(n) will return an Excel.Worksheet, the conversion seems unnecessary.

Assuming level <> 0 then the line above

VB

xlWorkbook = xlApp.Workbooks.Open(Path)

needs to be looked at. In your debugger does xlWorkbook have a value, and is the name of that workbook "AA.xlsx"? Because you have not specified a path in

VB

Dim Path As String = "AA.xlsx"

You can make life a lot easier for yourself when tracking down errors by using well-constructed and thought out error handling techniques. This article is a good starting point Exception Handling Best Practices in .NET[^] - most of the examples are in C# but the words apply to any language. VB.NET has a section to itself at the end of the article, but don't just jump straight to it - read the principles