Hi all,
My goal is to use "XPathOnUrl" thousands of times, from a relatively few websites. So far my excel sheet becomes unusable even when the data has loaded.
I am able to use the "Dump" formula for the home page of the website. But each subsequent website, the data I want is specific. Where the "dumb " formula can no longer be used.
Does anyone know a way to use the "XPathOnUrl" thousands of times without slowing down excel?
Thanks in advance for any help
Jackson
Hi Jackson,
What are you trying to extract from the websites? Dump is only needed when extracting more than one value at a time. This may cause issues when the array consists of more than one row and you use formulas for each row.
After the formulas have completed, I recommend copy pasting as values to avoid recalculation when the sheet changes.
I am trying to create a price comparison excel sheet. I extract the links to the products individual pages, from the original home page. And I can do this rather quickly and easily using the "Dump" formula.
The excel sheet slows down when I am on each individual product page, and I am extracting all of the data from all the various product pages.... (thousands of times processing the "XPathOnUrl" formula)
I will create a macro to copy and paste, to automate the process. Thank you.
I have created a macro to copy and paste the values for the data. However, even if I create a "Application.Wait" line in the macro, the cell calculating the"XPathOnURL" is copied and pasted as "#GETTINGDATA"
No matter how long I increase the time for the "Application.Wait" code, "#GETTINGDATA" is still what is copied and pasted.
I can only put this down to, excel does not process the "XPathOnURL" formula while a macro is running. As soon as the macro is finished, all of the data appears. The problem with this, is that with thousands of lines of data, excel becomes almost too slow to run a second separate macro to copy and paste the values for the data.
My solution here, is to create two separate macros, one for creating, say 50 lines of the "XPathOnURL" formula, and a second macro to copy and paste the values.
However this would still be manually operated, and quite tedious for thousands of lines of data.
Any assistance to this problem, would be greatly appreciated.
Hi, you need to disable async in order to make VBA handle the formulas. See this page:
https://seotoolsforexcel.com/seotools-config-xml/
The trade-off is that you can't run formulas in parallel but you get full control and can also capture the results using Evaluate without interacting with the sheet.