How can I run gnfinder from within Excel?

[seacliffs]

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!

 

[John_w]

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.

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]

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!

 

[John_w]

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.

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:

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

 

[seacliffs]

You were absolutely right - I hadn't added in the bin!

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