Do While Loop never stops

[chasoe]

Dear Sirs,

I just come up with a problem that the vba runs the loop but will never end. The codes previously work correctly but seems fails after I made some slight changes to the codes and folder file names or whatever which I cannot remember :

Files in folder :

11038-CAL-201906.xls
11041-CAL-201906.xls

Please Login or Register  to view this content.

Would like if someone could help spotting out where the error is ?

Many thanks !

[Marc L]

Hi !

Too many things to say but first we wanna know where this code is located, which kind of module ?

[chasoe]

The codes are stored in a file called Macro-CostAccrualList.xlsm,
and this file is stored in the same folder as 11038-CAL-201906.xls and 11041-CAL-201906.xls.

Of course there are may other ????-CAL-201906.xls files in this folder, and I just list out 2 only, and ???? are actual numeric code representing each construction project.

The Macro file is intended to look for all these ????-CAL-1906.xls files, open them one by one, and make some adjustments, then save it, and go for another file until all these files have been adjusted.
And BTW, it is just a normal simple module under VBA Project Explorer.

Hope you understand.

Thx

[Mumps1]

Try:

Please Login or Register  to view this content.

[chasoe]

Thanks for your prompt reply.

1. Your codes somehow was stuck in :

Please Login or Register  to view this content.

2. In any case, I find that your codes included :

Please Login or Register  to view this content.

so I just added this codes into my original macro, and the whole thing works again and the loop can stop after finishing opening each CAL files.

but I don't know why this EnableEvents is so important without which the loop will never end, but repeat again and again after after going through all the CAL files

Thanks anyway as at least I can sort out where the problem is.

[Pepe Le Mokko]

@chasoe
Please wrap code with tags, even if there is only one line
Thanks

[Marc L]

it is just a normal simple module under VBA Project Explorer.

Sub CAL_ALLOpen()
      Dim P$, F$, L&
     With Application
         .AskToUpdateLinks = False
         .EnableEvents = False
         .ScreenUpdating = False
          P = ThisWorkbook.Path & "\"
          F = Dir(P & "*CAL*.xls")
    While F > ""
          L = L + 1
        With Workbooks.Open(P & F)
            .Sheets("CAL-GTT").[F9].Value2 = 500
            .Close True
        End With
          F = Dir
    Wend
         .AskToUpdateLinks = True
         .EnableEvents = True
         .ScreenUpdating = True
     End With
          MsgBox L & " files modified", vbInformation, "Updating CAL"
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[chasoe]

Looks too advanced for me to understand, as I'm only a beginner in VBA and all the codes comes from the google search and adapted to fit my need.

And it's bad news as the codes fail again even after adding "Enable Event" stuff.

[Marc L]

As it's the casual case without any attachment, so attach the main workbook (where code is)
saved as binary .xlsb or macro .xlsm and at least a couple of source .xls files …

[Pepe Le Mokko]

@chasoe
Please read post #6 and add tags as requested ( final warning)

[chasoe]

Yes, I got the message already.

[Fluff13]

Removed
Pending Op complying with moderator instruction

[chasoe]

Macro file as attached, as I need to strip off any Asian characters within the codes.
And I think this final version can now work after several trial run.