'SaveAs function extremely Slow
I have a fairly simple loop that copies data from a few locations, creates a new worksheet with it, creates a workbook from that sheet, saves it, and deletes it.
Sometimes this loop runs nearly instantly and I can save 20ish files in about 5 seconds, however sometimes it will take 15 to 20 seconds to finish each iteration of this loop, which causes it to take several minutes unexpectedly. I haven't been able to find any outside source (tried with all other programs closed) that seems to slow it down. I've also done the typical methods of speeding up the loop like disabling screen updating and calculations.
I don't know if there is a more efficient way to loop this process, but I've verified this section in particular is the part that causes the delays as placing breaks while debugging has shown the differences in time and the other sections are all nearly instant. The section below is slightly modified to remove code that is known not to be problematic.
Edit - Additionally, I should mention each of these sheets are extremely small, holding only around 10 rows and 2 columns of data.
for i = 1 to 100
featureSheet.Copy
ActiveWorkbook.SaveAs "C:\DIR\" & HeaderFile.Sheets("SheetName").Range("C" & i).Value & ".json", FileFormat:=xlTextPrinter
ActiveWorkbook.Close
featureSheet.Delete
Next i
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
