There are more than 100 lines of data on the sheet.The content of the ABC column is keyed to go to SQLServer to retrieve data one line at a time and store the results in the column H.
Looped a collection of rows with ForEach statements.The connection to SQLServer is before the loop starts, and it's like repeating the record set retrieval and closing in the loop.
Regardless of whether the Application.ScreeUpdating is TRUE, I would like to do something about the situation where I can hardly get a screen response until the processing is complete.
"Nearly available" means that the ProgressBar on UserForm indicates progress, but the screen hardens in the middle and does not indicate progress.
At the end of the process, the programming message box appears, so you know the completion just in case.
It can't be helped because of the heavy handling, and I've neglected this issue until now
Is there a solution?
I don't even know if it's happening because the memory is eating (I don't know how to check it), but if that's the case, if you can open the memory in a timely manner,
Will the screen reflect smoothly?
I was able to solve various problems on this site, so I was wondering if I could solve this problem.Thank you for your cooperation.
UserForm1.ProgressBar1.Value=0 UserForm1.ProgressBar1.Min=0 UserForm1.ProgressBar1.Max=100 Range ("A9999").Select Selection.End(xlUp).Select MaxLine= ActiveCell.Row Set DataSheet= Range ("A2:A" & MaxLine) For Each x In DataSheet W_LCnt = W_LCnt+1 strSQL="SELECT XXXXX" RS. OpenstrSQL, Conn, adOpenStatic, adLockReadOnly, -1 If RS.EOF Then x.Offset(0,5).Value="None" Else RS.MoveFirst x.Offset(0,5).CopyFromRecordset RS End If RS.Close '# DoEvents UserForm1.ProgressBar1.Value=W_LCnt/100 Next x
This is what bothered me when I saw the current code.
1) DoEvents has been commented out, but is it actually uncommented?
2) Does the variable W_LCnt mean the number of processes?
Isn't that right?
Will the progress bar still stop in the middle?
I replaced SQL with a time-consuming one and ran it in my environment. 1)and 2) and the progress bar was displayed properly.
There is a function called DoEvents.
If you call this function during a loop, the OS will be able to control other events, so you may be able to resolve it.
This article will be helpful.
340 Memory layouts learned in theory don't work out as expected when actually printed as addresses.
339 Understanding the Meaning of mpm prefork Settings
367 To Limit Column Values to Strings in a String List Using sqlalchemy
345 Who developed the "avformat-59.dll" that comes with FFmpeg?
© 2023 OneMinuteCode. All rights reserved.