0

I am using sendkeys to manipulate the options in Find and Replace. However, when it comes to taking screenshots, it captures the screenshot without the results. Most of my searches (99%) return no result. I just have to take a screenshot of the entire active screen (I use a dual monitor) and save the file as a jpg or pdf.

The code I have so far is

Sub Seachpdfer()
 Dim searchquery As Range
 Dim mystring As String
 Dim mypic As Shape

 Set searchquery = Worksheets("Query").Range("AA2:AA10001")

 For Each cell In searchquery

    mystring = cell.Value

    If cell.Value = "" Then
        Exit For
    End If

  Worksheets("Restricted List").Select
  Worksheets("Restricted List").Range("A1").Activate
  Worksheets("Restricted List").Range("I1").Activate

  Application.CommandBars("Edit").Controls("Find...").Execute



  Application.SendKeys ("%n" & mystring)
  Application.SendKeys ("{TAB}{TAB}")
  Application.SendKeys ("Sheet")
  Application.SendKeys ("{ENTER}")
  'Application.SendKeys ("{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}")
  Application.SendKeys ("{TAB}{TAB}")
  Application.SendKeys "%i", True  ' This clicks the Find button on the Find and Replace dialog

' This is where it all breaks. I tried using doevents and tried using application wait. But the screenshot I get does not show
'The search result We could not find what....."

  Application.SendKeys "({1068})", True
  DoEvents
  ThisWorkbook.Worksheets("Staging").Activate
  ActiveSheet.Paste
  sFileName = ThisWorkbook.Path & "\" & mystring & ".jpg"

  For Each mypic In ActiveSheet.Shapes

        mypic.Select

       Call PictureExport ' This function converts it to a chart and prints out the pdf


   Next

 Next
End Sub

Any help will be much appreciated. Thank you.

Htnasarp
  • 23
  • 5
  • 5
    Make use of the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Find) method instead of using `.SendKeys` (which is a bad approach). And you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 27 '19 at 15:56
  • 5
    Wow. Why are you using `SendKeys` for this at all? Is [`Range.Find`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) not working for some reason? – Comintern Feb 27 '19 at 15:56
  • Thank you guys. I know send keys are tricky. Just that the internal auditors want a screenshot with the find and replace dialog clearly showing the search string and the resultant "we could not find...". This will be saved to evidence that the item was not in the excel sheet as of that date. – Htnasarp Feb 28 '19 at 03:44
  • My specific problem is that when the Find and replace returns no result, I am not able to take a screenshot with the We couldn't find what you were looking for. Click Options for more ways to search." dialog box. When I run the code above, I get a screenshot without the dialog box. – Htnasarp Feb 28 '19 at 18:55

0 Answers0