0

I am trying to automate the generation of some spreadsheets using an external software, but when a new sheet is generated, the macro fails to find it.

Because the software generating the sheets is not a microsoft object, I have been using mouse events and SendKeys to interact with it, as the software loads in a fixed position on my monitor every time.

So far I have tried For Each x in Workbooks and If x.name = defaultName (it is guaranteed to always be the same name on generation), but both have returned errors. 

Call LeftClick
Sleep (20000)
MsgBox (Workbooks.Count)
For Each book In Workbooks
     If (book.name = "gwpr.xls") Then
        Set extract = book
        Exit For
     End If
Next book
Extract.Worksheets("Sheet1").UsedRange.Copy

The leftclick in the code clicks on the "export to excel" button in the software, and the 20 second sleep is to allow adequate time for the workbook to open (this part does not fail).

There should be 3 workbooks open at the Workbooks.Count line as a result, but it always outputs 2, and the For Each block fails to find the new workbook as well.

Does anyone know of any reason VBA might struggle to recognize workbooks generated during execution, and what possible workarounds might exist?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
J.S
  • 1
  • 5
  • 4
    Maybe the new workbook you are creating is in a new instance of the Excel application and that is why the macro doesn't recognize it or count it. Check on the Task Manager if after generating the new workbook the are two instances of Excel running at the same time – Pablo G Oct 22 '19 at 13:46
  • Agreed with @PabloG, after testing this a couple of times with my export to excel programs. [this might help](https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel) to resolve your issue. – Plutian Oct 22 '19 at 13:50
  • Yup! Didn't even realize that was a possibility, thank you so much @PabloG! Going to look into that thread Plutian linked now and see if that doesn't fix my issue. – J.S Oct 22 '19 at 14:00
  • Ok, just confirming, but the thread did indeed solve the issue. I cannot thank you both enough for the help. – J.S Oct 22 '19 at 14:11

0 Answers0