Using ilogic to search a row in an excel file

[bob_clark5J5M8]

Hi all, I used to use a piece of code to search an excel file; 

The code when ran would look up an excel sheet, if it found an existing doc number in column A that matched the inventor filename, AND then also found any text in column B then it proceeded to create a new row.

If (GoExcel.CellValue("A" & oRow)) = oDocNumber & (GoExcel.CellValue("B" & oRow)) = "1" Then......

I want now to have something similar, in that I'd like it to still check column A for the doc number but now I would like to check the cell value in column B and see it it matches an inventor variable, if it does not match the variable; then it creates a new row

If (GoExcel.CellValue("A" & oRow)) = oDocNumber & (GoExcel.CellValue("B" & oRow)) = an inventor variable Then......

many thanks for any assistance

[Ralf_Krieg]

Hello

If "Inventor variable" means a value of an fx parameter then try write just the parameter name

If (GoExcel.CellValue("A" & oRow)) = oDocNumber & (GoExcel.CellValue("B" & oRow)) = %Parametername% Then

Or if it means a value of an custom iProperty then

If (GoExcel.CellValue("A" & oRow)) = oDocNumber & (GoExcel.CellValue("B" & oRow)) = iProperties.Value("Custom","%PropertyName%") Then

[bob_clark5J5M8]

Hi, thx for posting....

The inventor variable means just a regular variable.

If I use what you suggest , iprop or param or even a variable, I get an error about it not being valid for being the type Boolean, which is why it worked before when I queried the second cell "B" to have any text, ie ..... & (GoExcel.CellValue("B" & oRow)) = "1"

Maybe using GoExcel isnt the solution?

My ilogic is a little rusty having not been using it for a few years

[WCrihfield]

Hi @bob_clark5J5M8.  Sometimes you just need to break things down into more lines of code to simplify and confirm that each step is working as planned, before attempting to combine a bunch of complex stuff into one line.  For instance try to retrieve the value from cell A in one line of code.  And if you know the 'Data Type' of what you are attempting to extract, it is often advantageous to pre-define the variable that is to hold the retrieved value as that specific Type.  Then check to see if it retrieved anything, before moving on to checking the value of call B, and so on.  Once you have extracted the values, and confirmed that values were retrieved, then you can compare the variables to each other as needed.  Data cells in an Excel spreadsheet can be formatted in many ways, so a data type mismatch can often trip folks up too.  The GoExcel iLogic snippets are nice for fairly simple 'quickie' rules, and keeping things brief, but you can also use the AddReference and Imports lines within the rule's Header, which will allow you to work with the Excel Application and its specific API objects directly if you want.  Of course that route generally requires a lot more code and some knowledge of the Excel API Object Model for vb.net.  It's doable though. (Link1, Link2)

[Ralf_Krieg]

Hello

What is the value of the cells in column "B" and what type is the variable and what's the value of it?

I agree to @WCrihfield to split the line, if the error persists to check the results in small single steps.

[bob_clark5J5M8]

Hi, I should maybe have posted more code, but its quite large; its for a drawing tracking register. When ran from the dwg, it creates and stores a pdf, adds a row to a ss with hyperlink to pdf etc and then sends an email with the attachment to some people. I shall break it down as I was working in the context of the full code. It should "simply" check each each row in the first column of the ss for an existing dwg number, if it doesn't find one then it writes the new entry in the first blank row it finds.

If it finds the same dwg number in a row then it should check column B for the revision letter, If it is the same as the dwgs revision letter then it overwrites the row.

I will write a mini programme just for this part and see how it goes, thanks for the input guys, its appreciated

[bob_clark5J5M8 · Accepted solution]

it was quite simple in the end

changed this;

If (GoExcel.CellValue("A" & oRow)) = oDocNumber & (GoExcel.CellValue("B" & oRow)) = an inventor variable Then......

to this;

If (GoExcel.CellValue("A" & oRow)) = oDocNumber And (GoExcel.CellValue("B" & oRow)) = an inventor variable Then......

thx all