Old workbook (once opened by VBA) keeps opening when I open another excel file

1
votes

I am building a database in Access, for which I import data from an Excel workbook questionnaire. I have coded an Import-sub that selects, opens, retrieves the data from and finally closes the workbook.

The problem is this: for some reason, when I now open any excel workbook on my computer (at a time when neither Access or Excel is in use) some old version of the questionnaire keeps opening as well. This problem doesn't end by restarting the computer, but only by deleting that specific questionnaire-workbook. But then it starts happening with another workbook.

I have a theory that this might be because I - in my import-sub - have opened the questionnaire, encountered a run-time error which has ended the sub before it closed the workbook, and that somehow the workbook is still "open". Or that there still is a link active.

So I have two questions:

1.) Does anyone know how I can fix this problem?

2.) Is there generally any consequences of not closing a workbook that you open through VBA?

My relevant code is:

Dim MyXL As Excel.Application
Dim MyWB As Excel.Workbook
...

in between lots of stuff happening, several times an error occurs which interrupts the program.

...
MyWB.Close False
MyXL.Quit

Appreciate any help on this!

3
excelvbams-access
Or maybe you have some code in the Personal.xlsb file? – Siddharth Rout
Open Excel, Go to VBA Editor and on the Left Hand Side, do you see the Personal file? If yes, then open the ThisWorkbook and see if you have any code there? – Siddharth Rout
No, there is no code under "This workbook" for the specific file.. – Marius Johansen

3 Answers

1
votes

I did Encounter the same Problem and found out that Excel stores the files that open whenever you start Excel in a Folder (XLSTART). The path to mine was: c:\USERS\MyUserName\AppData\Roaming\Microsoft\Excel\XLSTART

As suggested by Ross McConeghy error handling may prevent such an occurrence. But the error already happened and the questionnaire, as you suggested, has placed that workbook in the Folder XLSTART. You have to delete it from that folder to fix the unwanted occurrence.

0
votes

Your theory is likely. If you never display the Excel application, errors are encountered, and your code never closes the workbook it would be left open in the background and your code would most likely lose reference to it. The next time you open an Excel file the instance of Excel that is already open(but hidden from view) "picks up" the request and opens the file, also displaying the file that was still open from the macro.

1.) You should set up error handling so that the Workbook and Excel application are closed when there is a non-recoverable error.

Sub MySub()
    On Error GoTo handle_Err

    <... code ...>

    Exit Sub
handle_Err:
    MyWB.Close False
    MyXL.Quit
End Sub

If you have other Error handling statements in your code always reset to On Error GoTo handle_Err instead of GoTo 0 etc.

2.) The only consequences of not closing the Workbook are the obvious ones- the system resources it's using and, if it is open for editing, no one else can edit it.

0
votes

I had a similar problem and solved it a different way. I found the connection to an external workbook and fixed it by going to Data > Edit Links.