0

I am working on excel VBA. In my code I am downloading Multiple files say 50 one by one. What I am doing, I'm downloading first file making changes doing some calculation and saving it as xlsx and after that downloading another file and doing calculation saving it as xlsx and so on. So After downloading 26-27 files I am getting "Out of Memory" Error.

Can anyone suggest me how to clear memory after downloading each file. Thank you so much in advance

Sanjeev Shinde
  • 63
  • 1
  • 3
  • 14
  • You need to change your approach so that it no longer requires doing things that apparently expose memory leaks in Excel. If you're holding spreadsheets in variables, the first thing I would do is dispose those variables the moment you no longer need them. – Robert Harvey Apr 12 '16 at 14:35
  • Could you post the code that's doing this? It would give us a better idea –  Apr 12 '16 at 14:50
  • @RichardU /// Here I am giving you the link to my file https://drive.google.com/open?id=0BwIYozURuftTdXR1U3laZTVsbkk – Sanjeev Shinde Apr 12 '16 at 15:11
  • you didn't say "...and after that closing it: are you keeping all files open after having saved them as "xlsx"? – user3598756 Apr 12 '16 at 15:44
  • No I also tried to do that but then its asking everytime conformation to overwrite. Should I close each file after doing calculations? – Sanjeev Shinde Apr 12 '16 at 16:12

1 Answers1

2

Perhaps consider setting your variables to Nothing? That way the objects will be destroyed and memory resource released.

There are a couple of posts that may give you some more insights. Worth checking them out.

When should an Excel VBA variable be killed or set to Nothing?

Excel VBA: Does destroying a collection of objects destroy every single object?

Community
  • 1
  • 1
woodykiddy
  • 6,074
  • 16
  • 59
  • 100