Is it possible to delete the workbook you have opened?

[rogerte]

Hi

I have created a Workbook_Open macro that reads in the contents of a text file, makes some modifications, saves the modified spreadsheet, without the VBA code, to another workbook without the VBA code in it, and then closes the workbook. This all works ok!

Private Sub Workbook_Open()

Dim sFileName As String
Dim sPrefix As String
Dim sOutput As String
Dim sInput As String

Dim i As Integer

'Get This Spreadsheet's prefix - assumes loaded as nnnnBSEXPORT.XLS, and then get input and export filenames.
sFileName = UCase(ThisWorkbook.FullName)
sInput = Left(sFileName, Len(sFileName) - 3) & "TXT"
'Check file exists
If Len(Dir(sInput)) = 0 Then
   MsgBox sInput & " NOT found"
   Exit Sub
End If

'Get the prefix and save it in export xls filename
sPrefix = Right(sFileName, Len(sFileName) - InStrRev(sFileName, "\"))
i = InStr(1, sPrefix, "BSEXPORT.XLS")
sOutput = ThisWorkbook.Path & "\" & Left(sPrefix, i - 1) & "BS.XLS"

'Now check it doesn't exist - if so quit
If Len(Dir(sOutput)) > 0 Then
   MsgBox sOutput & " Already exists - cannot continue"
   Exit Sub
End If

'All OK so read in the text file
    Workbooks.OpenText Filename:=sInput, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array( _
        3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10 _
        , 2), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 2), Array(15, 2), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 2), Array(21, 1), Array(22, 2), _
        Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array( _
        29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), _
        Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), Array(41, 2), Array( _
        42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2), Array(47, 2), Array(48, 2), _
        Array(49, 2), Array(50, 2))
    Range("A1:IV1").Select
    Selection.Font.Bold = True
    Columns("A:IV").EntireColumn.AutoFit
    Range("A1").Select
    
'Save work sheet as new name
    
    ActiveWorkbook.SaveAs Filename:=sOutput, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

'Quit
Application.Quit

End Sub

Is it possible, within the macro, to delete the originally opened workbook (nnnnBSEXPORT.XLS) after having saved as nnnnBS.XLS? I don't think so because WIndows will still have the file open, but I have been asked if it can be done.

Many thanks

Roger

 

[DrSimon]

Can't see the problem - you've dropped the association to the original by the SaveAs. You should just need to 'Kill <path>/nnnnBSEXPORT.XLS'

 

[rogerte]

DrSimon,

Thanks for reply,

Back at work so gave it a try.

Added a Kill sFileName after the Saveas line - get an "error 70 permission denied" error message.

The file title on the spreadsheet shows it now as nnnnBS.XLS, so save has worked ok.

OS is XP (no control over that - its corporate!

Any ideas?

Thanks

Roger

 

[strongm]

>Added a Kill sFileName after the Saveas line - get an "error 70 permission denied" error message.
>The file title on the spreadsheet shows it now as nnnnBS.XLS, so save has worked ok.

The problem is that you arwe trying to kill the file you have just saved (and which is now the open file). You need to be killing the old file.

So where you do:

sFileName = UCase(ThisWorkbook.FullName)

add

sOriginalFileName = sFileName

and then you should be able to safely Kill sOriginalFileName after the SaveAs

 

[DrSimon]

I didn't have a suitable txt file to play with, so I just did 'Workbooks.Open Filename:=sInput' and then Kill sInput worked without any problem. Must admit that I'm using Windows 7 and Excel 2010 though. Not sure what strongm is getting at: the code is Save As sOutput, then Kill sInput.

It may be related to where you ran it from - try putting the code in PersonaL.xls and runnign from there and see if that makes a difference.

 

[rogerte]

Thanks for the replies.

It looks like, after the SaveAs, both worksheets remain open (you can display both sheets within the Excel 2007 application), and the focus remains on the original file, so that explains what is happening.

I don't understand why, and how to get over it though!

 

[DrSimon]

Something strange is going on and can't really comemnt on what you are finding, because. You may need to write the code using a proper variable handle such as below. As I have already suggested, don't run from an Open method, use something independant so you can close the workbook and then delete it.

 Dim Wb As Workbook
Set Wb = Workbooks.Open(Filename)
......
Wb.Close
Kill Filename

 

[PHV]

What about this ?

...
Dim Wb As Workbook
Workbooks.OpenText ...
Set wb = ActiveWorkbook
...
ActiveWorkbook.SaveAs ...
wb.Close
Kill sInput
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, I guess you want to delete sInput instead of sFileName ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

>Not sure what strongm is getting at

Nor me. Misread something.


DrSimon is giving the solution for killing the input file, which should indeed work OK. But you are asking how to delete the sheet hosting the currently running macro (which is NOT the input file, as far as I can tell), i.e nnnnBSEXPORT.XLS rather than nnnnBSEXPORT.TXT

The problem here is that you are trying to delete a file that is indeed still open (as you surmised earlier) - and it has to be open to be running the VBA ...

 

[PHV]

Seems like I misread too ...
You really want to delete the workbook containing the currently running VBA (ie ThisWorkbook) ?

 

[rogerte]

Sorry for delay in getting back, but was working off-site most of yesterday.

Yes the original question I was posed was was it possible to delete the originally opened workbook, after it had been saved under a new name.

I think the user has now accepted that you can't delete an open file, so has accepted the solution I first offered - to use a batch file to open the original workbook, and then delete the original workbook after the spreadsheet operation finishes.

Thanks for your feedback folks.

Roger

 

[strongm]

>to delete the originally opened workbook, after it had been saved under a new name

To clarify. You actually have (derived from your code)

nnnnBSEXPORT.XLS - macro sheet
nnnnBSEXPORT.TXT - input sheet
nnnnBS.XLS - output sheet

Your code runs in nnnnBSEXPORT.XLS, loads nnnnBSEXPORT.TXT and then saves it as nnnnBS.XLS

Therefore the sheet you modify and save under a new name is NOT the originally opened workbook. And this is the source of the confusion: what you try to describe and what you code actually does are somewhat different things.

 

[DrSimon]

Glad you guys have got involved. The point I was trying to make in my last update was that it may be better to run a macro from outside the workbook and also by using variables, which I understand is better notation, there would be less confusion about which file was in use!

Simon