Converting Image to Text

[BeaglesBuddy]

I need to build excel sheets and be able to convert images to text. I copy the sheet from a web site and all fields in the table come in as text except for one. That field can be 1 of two images. Right now i have to save the xl sheet as html and then Find/Replace the html image code with a + or - sign which is time consuming and difficult to outsource. Is there a way I can convert the image through excel. I need to be able to sort the sheet as well as pull the data into a separate tab.

 

[AlphaFrog]

You might be able to do it with a macro. The trick is to find something accessible from VBA that can distinguish one image from the other (plus vs. minus images)

Often (not always) the web site will have Alternative Text as part of the image. Or if each image has a unique click-able link, that could be used as well.

What is the site you copy from so I can take a look at the images?

 

[BeaglesBuddy]

You might be able to do it with a macro. The trick is to find something accessible from VBA that can distinguish one image from the other (plus vs. minus images)

Often (not always) the web site will have Alternative Text as part of the image. Or if each image has a unique click-able link, that could be used as well.

What is the site you copy from so I can take a look at the images?

http://caps.fool.com/player/tmfbabo.aspx

Each image is unique in the name. thumbUpMed.gif and thumbDownMed.gif

They all have alt tags that read Outperform and Underperform.

Appreciate any help you can give me.

 

[AlphaFrog]

Sub Replace_Plus_Minus_Images()
    
    Dim Pic As Shape

    Application.ScreenUpdating = False
    
    For Each Pic In ActiveSheet.Shapes
    
        If Pic.Type = msoPicture Then
        
            Select Case Pic.AlternativeText
                Case "Underperform"
                    Pic.TopLeftCell.Value = "-"
                    Pic.Delete
                Case "Outperform"
                    Pic.TopLeftCell.Value = "+"
                    Pic.Delete
            End Select
            
        End If
    
    Next Pic
    
    Application.ScreenUpdating = True
    
End Sub

 

[BeaglesBuddy]

Perfect! Thanks

 

[BeaglesBuddy]

Sub Replace_Plus_Minus_Images()
    
    Dim Pic As Shape

    Application.ScreenUpdating = False
    
    For Each Pic In ActiveSheet.Shapes
    
        If Pic.Type = msoPicture Then
        
            Select Case Pic.AlternativeText
                Case "Underperform"
                    Pic.TopLeftCell.Value = "-"
                    Pic.Delete
                Case "Outperform"
                    Pic.TopLeftCell.Value = "+"
                    Pic.Delete
            End Select
            
        End If
    
    Next Pic
    
    Application.ScreenUpdating = True
    
End Sub

Hey Could I do this for the caps rating column as well. There is no alt text for those images. Could I change Pic.AlternativeText to pull from the html src. Those are consistent based on 1 of 5 ratings.

 

[AlphaFrog]

I don't know how to do that. Doesn't mean it can't be done though.