How can I run gnfinder from within Excel?

seacliffs

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone -

First - I basically know nothing about programming. I use this site to find code other people have posted and stick it into Excel and am always in glee when it works. I've tried that for this problem, too, but with no luck.

Here's the situation. I have an Excel spreadsheet with 1400 research abstracts. I'm trying to find any species' names listed in each individual abstract. I tried vlookup and various codes - but the simple database size crashes my program every time. However, I then found this program called gnfinder, which basically allows me to search text for a species' name. I really, really don't want to manually copy and paste 1400 paragraphs though - so if there is a way to do this within Excel - I would be incredibly grateful!

Here's how it works in the command area of my computer:

C:\bin>echo "The bulk ore carrier Apollo Sea sank near Dassen Island, South Africa, on 20 June 1994 during a period of winter storms. Approximately 10 000 African (Jackass) Penguins Spheniscus demersus were oiled, collected and transported to the SANCCOB rescue centre; 5213 were released after cleaning, 4076 with flipper bands. We believe that most of the penguins oiled during this incident reached an island or the mainland alive, and that there was no mass mortality in the wild at the time of the oil spill. Birds from all parts of the breeding range were oiled, but most were from Robben and Dassen Islands. The overwhelming majority of released birds made the transition from the rescue centre to the wild successfully; 2652 had been resighted at breeding colonies within two years of their release; the cumulative number of birds was increasing steadily and an asymptote had not been reached by August 1996. There was a wide dispersal of released penguins, with recoveries and resightings over 1800 km of coastline between Algoa Bay and Walvis Bay." | gnfinder find | find /i "name"
"totalNames": 2
"names": [
"name": "Spheniscus demersus",
"name": "Algoa",


The purple text are the commands I type in.
The blue text is from one cell in Excel that I have copied and pasted.
The green text is what the computer spits back out and then I have to copy and paste back into Excel.

1400 times. (sob)


Here's what I am hoping someone here can help me do:

1. Copy and paste your amazing code into a macro.
2. Run it.
3. Column 2 mysteriously and magically fills up with the species name specific to each cell in Column 1.

I don't even know if this makes sense. I hope so!

Help, please!!! Thank you!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
Welcome to MrExcel forums.

Put this macro in a standard module. Test it with just 3 or 4 rows in the active sheet to see if the result is what you want.

VBA Code:
Public Sub Run_gnfinder()

    Dim lastRow As Long, r As Long, i As Long
    Dim command As String
    Dim result As Variant
   
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For r = 2 To lastRow
            command = "echo " & .Cells(r, "A").Value & " | gnfinder find | find /i ""name"""
            result = Split(CreateObject("WScript.Shell").Exec("cmd /c " & command).StdOut.ReadAll, vbCrLf)
            .Cells(r, "B").Value = Join(result, "")
        Next
    End With
       
End Sub
 

seacliffs

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi John - thanks for that code! I plugged it in - but nothing happened. The command screen would pop open and then close again quickly - but no text was added to the Excel spreadsheet. I'm wondering if it's because of how the results are created in the command? Any other suggestions? THANK YOU!

1606099695836.png
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
I suspect you haven't added C:\bin to the PATH user or system environment variable. Either do that and close and reopen the workbook and my original macro should work, or use this macro which runs the command in C:\bin.

VBA Code:
Public Sub Run_gnfinder()

    Dim lastRow As Long, r As Long, i As Long
    Dim command As String
    Dim result As Variant
  
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For r = 2 To lastRow
            .Cells(r, "B").ClearContents
            command = "echo " & .Cells(r, "A").Value & " | gnfinder find | find /i ""name"""
            result = Split(CreateObject("WScript.Shell").Exec("cmd /c CD C:\bin & " & command).StdOut.ReadAll, vbCrLf)
            .Cells(r, "B").Value = Join(result, "")
        Next
    End With
      
End Sub
Here's my test results:
1606142444598.png


Note: as shown above, it doesn't matter if the phrase is enclosed in double quotes or not.
 
Last edited:
Solution

seacliffs

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
You were absolutely right - I hadn't added in the bin!

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top