Hello experts,
I have the code below which updates some columns of SharePoint list, based on excel values, through its ID in a loop, however if I have hundreds of entries it take some time intil updates, so I would like to know with you can help with improvements in the code. It uses SQL statements in VBA to update the SharePoint List. The Activex must be enabled.
Many thanks !
Please Login or Register to view this content.
Last edited by teixeire; 06-28-2022 at 09:41 AM.
I'm not familiar with the SQL part of it, but I see two things that might help.
Right after your DIM statements add "Application.Screenupdating = FALSE" to turn screen updates off so Excel doesn't waste it's time repainting the screen.
Right after "tbl.refresh" add "Application.Screenupdating = TRUE" to turn screen updates back on.
If you're doing your whole table, do you have to do it a row at a time? It would be much faster to do that whole table at once, but since I don't know the SQL side of it I can't help much with that.
I know I'm not stupid, but I suspect I'm a lot stupider than I think I am
I found a solution, many thanks !
Could you please state how you solved the problem? Others would also love to use it now and in the future.
Artik
Agree, interested in your fix.
And if your issue is solved you should update the thread by using the Thread Tools at the top of the page to mark it "Solved".
Okay, I did a sample table as below..
I look for SQL statements and applied as below, doing a join between same table in different places, and update it in batch instead of row`s looping
table example.png
Please Login or Register to view this content.
Cool. How much difference in speed did you notice by making that change?
I asked end user to test in real scenario, but it seems to work much better than before
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks