Catastrophic Error in Excel VBA Script every second time

Caylu

New Member
Joined
Feb 24, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Catastrophic Error.jpg


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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
When you run it the second time, is it after you have closed Excel, or leaving the code worksheet open and running again?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is it better if you use the ACE OLEDB driver instead?
 

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,332
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top