Get live data from close workbook every 1 minute

[singhverm]

i was using this vba to copy data from sheet to another every 1min. but now the problems arises that i need to fetch data from external workbook which is linked to a python and python updates the workbook every 1 minute by fetching the live data from a website. So, instead of copy and pasting the data from one sheet to another i need help getting data from that workbook which stays closed or has to be close around 60sec mark so that python can import that on it.

here is the code i was using-

Sub LetsGetsStarted()
rowNum = 8
Call OneToRuleThemAll
End Sub
Sub OneToRuleThemAll()
Application.ScreenUpdating = False

Range("data").Copy
Sheets("Record").Cells(rowNum, "A").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

'Increment for next run
rowNum = rowNum + 6

'Quit condition
If rowNum > 5618 Then Exit Sub

'Schedule next call
Application.OnTime earliesttime:=Now + TimeSerial(0, 1, 00), procedure:="OneToRuleThemAll"

End Sub

 

[BSALV]

I didn't test this, i'll do it when i'll be back, but i'm afraid a Mac ...
Is there need to do this more precisely, because i think there 'll be a small shift in the time (now and then +1s) so it ends up just as Phyton is busy ??? Or check the attribute "last time opened" of that file ???

in thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Einde
End Sub

and in a module

Global MyScheduledTime

Sub Start()
     Dim WB    As Workbook

     Einde     'first run that macro
     On Error Resume Next
     Set WB = Workbooks.Open("MyPython.xlsx")     'try to open that workbook
     ptr = 0     'reset pointer
     Do While WB Is Nothing And ptr < 8     'if WB isn't opened, try 8 times (*5 sec = 40 sec) to open
          Application.Wait Now + TimeSerial(0, 0, 5)     'wait 5 sec
          Set WB = Workbooks.Open("MyPython.xlsx")     'try to open
          ptr = ptr + 1     'increment pointer
     Loop
     If WB Is Nothing Then MsgBox "error opening wb", vbCritical: Exit Sub     'after 8 times, still not open = end

     a = WB.Sheets("MySheet").Range("data").Value2     'read range to array
     WB.Close False     'close immediately without saving
     ThisWorkbook.Sheets("Record").Cells(rowNum, "A").Resize(UBound(a), UBound(a, 2)).Value = a     'write array to thisworkbook

     MyScheduledTime = Now + TimeSerial(0, 0, 60)     'was able to open now, so within 60 seconds try again and almost sure, you can do it without error
     Application.OnTime MyScheduledTime, "start", , 1     'schedule next re-opening

End Sub

Sub Einde()
     On Error Resume Next
     Application.OnTime MyScheduledTime, "start", , False
End Sub