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 !
Hi !
Too many things to say but first we wanna know where this code is located, which kind of module ?
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
Last edited by chasoe; 06-30-2019 at 08:48 AM.
Try:
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Thanks for your prompt reply.
1. Your codes somehow was stuck in :
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.Please Login or Register to view this content.
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.
Last edited by alansidman; 06-30-2019 at 12:15 PM.
@chasoe
Please wrap code with tags, even if there is only one line
Thanks
Another way without an object variable …
Originally Posted by chasoe
PHP Code: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 » !
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.
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 …
@chasoe
Please read post #6 and add tags as requested ( final warning)
Yes, I got the message already.
Removed
Pending Op complying with moderator instruction
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks