[SOLVED] Infinite loop while closing another workbook

Infinite loop while closing another workbook

I'm working on an Excel project where I use VBA and two workbooks : One to update some tables (analysis requests updated with a macro while opening) and another one where I copy part of those tables adding informations after tables were updated.

The first file where I have my requests launch an update when it opens :

ThisWorkbook.Activate
Set myCurrentWorkbook = ThisWorkbook

do some stuffs...

call my_copy

Here is the code in my_copy :

Set myWorkbook = Workbooks.Open(myPath)
myWorkbook.Worksheets(mySheet).Select

do some stuff

myWorkbook.Close SaveChanges:=True

The problem is : seems like Excel crashes when I try to close the second workbook, and relaunched itself. So it relaunchs the first macro, which call my_copy, recrashes, and goes in an infinite loop.

When in my_copy I just let myWorkbook.Save without closing it doesn't crash..

I tried to fix it using two lines like this :

myWorkbook.Save
myWorkbook.Close (True)

But nothing changed..

How could I fix it please ? What's going wrong ?

Best regards

Solution

Find a walkaround to avoid those problems : In the firstlines of my code, I write on a dedicate cells the current date. Then I add a condition in the initial code :

If myCurrentWorkbook.Worksheets(mySheet).cells(1,1) = Date then Exit Sub

Works because the second time it opens, date were updated and so it stops the loop !!

Thanks everyone for the help.

Jerry