PDA

View Full Version : VBA: Workbook re-open after closing



Jomathr
06-30-2014, 11:20 AM
I have a pretty standard line of code to close the workbook if conditions are not met:



ThisWorkbook.Close


I have code in the thisWorkbook section that check if the file is saved on a hard drive by fetching the serial, if there is no serial I close the workbook but when I download and open the file directly from the net without saving it and close the workbook with it, it re-open a copy of it wich I don't want to happen.

From what I could find there seem to be a hidden application.ontime that cause the file to re-open but I have no clue how to go around it. If it's not that I have no idea why it is happening.

anyone have any idea about this?

westconn1
06-30-2014, 02:10 PM
I have a pretty standard line of code to close the workbook if conditions are not met:what code do you have after thisworkboook.close?


From what I could find there seem to be a hidden application.ontimeseems unlikely

hard to guess without seeing all the code

Jomathr
06-30-2014, 03:31 PM
Alright I'll post the code that come before that come before and up to the close:

ThisWorkbook:



Private Sub Workbook_Open()


Application.DisplayAlerts = False


On Error GoTo ErrHandler:
SNFind:
DriveL = Mid(ActiveWorkbook.Path, 1, 3)
Snum = CreateObject("Scripting.FileSystemObject").GetDrive(DriveL).SerialNumber


If Range("State") = "" Then
FrmActivateDemo.Show
End If


If Snum <> Range("State") _
And Range("State") <> "" Then
FrmVerif.Show
End If


ErrHandler:
If Err.Number = 5 Then
FrmErreurOpen.Show
QuaAutoSave
Resume SNFind
End If

End Sub

This piece of code mainly serve to force the user to save the file before using it. In the case of opening the file directly from IE (sadly I have to cope with it) there is no HD serial wich raise the error 5

the QuaAutoSave Function:



Public Function QuaAutoSave()


Dim Spath As String
Dim FileExtStr As String
Dim Fullpath As String
Dim fname As String
Dim Svalid As Boolean
Dim langue As String
Dim MsgName As String
Dim NameErr As String


Svalid = False
FileExtStr = ".xlsm"


On Error GoTo endf


With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Spath = .SelectedItems(1)
End If
End With


Do While Svalid = False


If Dir(Spath & "\Qua" & FileExtStr) <> "" Then
MsgBox (NameErr)
Else
Svalid = True
End If
Loop


'Save the file
Fullpath = Spath & "\Qua"
ActiveWorkbook.SaveAs Fullpath, FileFormat:=52


endf:


End Function

then focus switch to a form with text and a button saying the application will close:



Private Sub BtnCancel_Click()


ActiveWorkbook.Saved = True
Unload Me
ThisWorkbook.Close


End Sub


it does close the workbook but reopens it as a copy workbook[1].

If I start the workbook from a saved file it close without any issue

Jomathr
07-05-2014, 10:11 PM
Alright I made a file reflecting the problem and it does the same thing when I open it directly from IE withot saving it first

11900

remember to choose open from IE to be able to reproduce the reopening behavior.