open excel refresh and close issue

[mikematthys]

hey all,

the code below is working but i got an issue with it, maybe someone can help me out.

so it opens an excel file, refreshes the data in it and closes it again.
i had to put a timer because i kept getting a notification saying 'By completing this action the refresh will be cancelled and its not yet finished, want to continue?'. but even with the timer it still sometimes (90% of the cases)gives me that notification even when te refresh only takes a second, i put in 3 just to be sure

anyone a solution ?



Private Function RefreshExcel()
DoCmd.SetWarnings False
Dim appexcel As Object
Dim start As Date
start = Now
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "******"
While DateDiff("s", start, Now) < 3
appexcel.activeworkbook.refreshall
Wend

appexcel.activeworkbook.Save
appexcel.activeworkbook.Close (True)
Set appexcel = Nothing
DoCmd.SetWarnings True
End Function

 

[mikematthys]

found it, putting out the autorefresh in the excel sheet did the trick

Data > Connections > Properties > (uncheck) enable background refresh

 

[theDBguy]

Hi. Thanks for sharing!