Catastrophic Error in Excel VBA Script every second time

[Caylu]

I have an excel file with number of macros, which I run for different purposes. Previously it was running fine but recently (a week or so) I am facing the issue that it crashes every second time I run a macro. I have over 10 functions in it. So whatever I execute the first time will work but whenever I run it second time it will crash and gives a catastrophic error. I can go with any combination that i run Function 8 first and function 6 second time or function 5 first and function 8 second time, it will crash on second function. Closing and reopening the excel file will solve the problem for one time.

All functions are reading data from another CSV file and do some calculations and this reading of file is done using ADODB connection so that CSV file can be used as a DB and if there is any issue in the data of CSV it is properly taken care of, using other methods for reading CSV sometimes does break with special characters or extra comma. The line giving error is shown below.

DbConnect.Open "Driver=Microsoft Access Text Driver (*.txt, *.csv);DefaultDir=" & myFilePath & "; " & "Extensions=csv,txt;"

Now the interesting thing is that I took the file on my laptop and ran it and it did not give this catastrophic error on running the second function. Both machines are running windows 7 with excel 2016.

The machine on which I am having the issue is on the domain and was recently patched and updated. Also i don't have administrative privileges on it.

All suggestions will be appreciated.

 

[CountTepes]

When you run it the second time, is it after you have closed Excel, or leaving the code worksheet open and running again?

 

[RoryA]

Is it better if you use the ACE OLEDB driver instead?

 

[CountTepes]

The reason I ask, is that on the fist run, you are starting with a fresh copy of excel. When you run the code, then close excel and reopen the worksheet to run the code again, you might have a second invisible instance of excel running.

When it crashes, press ctrl-shift-esc and look at processes. If there is more than one instance of excel running, that is most probably your issue. Alternatively, you may have more than one instance of the ADODB driver running. So Excel cannot bind to it properly, or it is still holding the file in a locked state.

Are you sure that all subroutines, unload what they need to unload that they exit properly?

 

[Caylu]

The reason I ask, is that on the fist run, you are starting with a fresh copy of excel. When you run the code, then close excel and reopen the worksheet to run the code again, you might have a second invisible instance of excel running.

When it crashes, press ctrl-shift-esc and look at processes. If there is more than one instance of excel running, that is most probably your issue. Alternatively, you may have more than one instance of the ADODB driver running. So Excel cannot bind to it properly, or it is still holding the file in a locked state.

Are you sure that all subroutines, unload what they need to unload that they exit properly?

I checked the processes after running the macro first time and saw there is only one instance of excel running.
How can i check that more than one instances of the ADODB driver is running?

I believe there is no issue in the subroutine as this macro without any modification run on my other laptop fine. No matter how many times I run it. Is there a possibility that ADODB driver is corrupted?

 

[CountTepes]

To quote Murphy's Law, "If it can happen it will."

With windows, no corrupt software is beyond the realms of impossibility.

After running the first time and closing excel, do you still have an instance of an excel process running, or does it disappear?

Also the fact that the machine having an issue, is the one on the domain, means that it is most probably administered by SCCM and AD policies. Also that the file you are opening is a network file and on your laptop, the file is local. If you do not have admin access to the PC, it is possible that the file you are accessing, may not be released properly.

When you run the second time and it crashes, how do you get it to run properly again?