I write Visual Basic (not VBA) programs that access Microsoft Excel spreadsheets. Unfortunately, they sometimes open a spreadsheet without closing them. (E. G. when debugging a program.)
At that point the spreadsheet is locked and I have to copy it to a new name. This is even if I not only stop the program but even shut down Visual Studio applications.
Example, here is a simple program that opens and closes a spreadsheet. I can run it as many times as I wish.
Imports System.IO
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim A As Excel.Application
A = New Excel.Application
Dim eW As Excel.Workbook : Dim C As Excel.Worksheet
Dim NAME As String
NAME = "c:\a\TEE.xlsx"
eW = A.Workbooks.Open(NAME)
Console.WriteLine()
C = eW.Sheets.Item(1) ' student individual grade sheet
Console.WriteLine(C.Cells(1, 1).value)
eW.Close(SaveChanges:=True)
Console.ReadLine() ' so we can see thescreen
End Sub
End Module
Let's say I have injected a defect , and thus my program does not close the
Excel spreadsheet as it should, e. g.
Imports System.IO
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim A As Excel.Application
A = New Excel.Application
Dim eW As Excel.Workbook : Dim C As Excel.Worksheet
Dim NAME As String
NAME = "c:\a\TEE.xlsx"
Dim Q As Integer : Dim R As Integer : Dim Z As Integer
Q = 5 : R = 0
eW = A.Workbooks.Open(NAME)
Console.WriteLine()
C = eW.Sheets.Item(1) ' student individual grade sheet
Console.WriteLine(C.Cells(1, 1).value)
Z = Q Mod R
eW.Close(SaveChanges:=True)
Console.ReadLine() ' so we can see the screen
End Sub
Now, when I run the original applicaton (presumably, I have removed the bug): I can run the original application as nothing was changed.
If my application changes something, I get a file save dialogue from Excel with a file Name of `"Copy of TEE.xlsx"
Here is the example program:
Imports System.IO
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim A As Excel.Application
A = New Excel.Application
Dim eW As Excel.Workbook : Dim C As Excel.Worksheet
Dim NAME As String
NAME = "c:\a\TEE.xlsx"
Dim Q As Integer : Dim R As Integer : Dim Z As Integer
Q = 5 : R = 0
eW = A.Workbooks.Open(NAME)
Console.WriteLine()
C = eW.Sheets.Item(1) ' student individual grade sheet
Console.WriteLine(C.Cells(1, 1).value)
Z = Q Mod R
eW.Close(SaveChanges:=True)
Console.ReadLine() ' so we can see thescreen
End Sub
(I face this challenge this in two different circumstances.
When I teach Visual Basic Programming, I have labs and procedures where the students write Visual BAsic Programs that do things with spreadsheets. (These parallel the laboratories I have that do things with one- and two-dimensional arrays.)
Needless to say, the students have bugs, their files get locked, and this distracts the students from the array processing logic concepts I am trying to teach.
The second, is I am making programs that create and maintain individual spreadsheets for each students grades (not that one). They load each spreadsheet, perform the processing and then close the file. Needless to say, I have bugs in them, and it is very distracting from the debugging process to keep having to deal with these stupid dialogues.
So how can I best deal with this .
(I would like to tell Visual Basicc to tell Excel : "I don't care if the Expletive-deleted file is locked, save it anyway." But beggars can't be choosers Perhaps, I can get it to automatically save to the suggested name instead of bothering me.
