Solved: Moving document to recycle bin

[Klartigue]

I am essentially trying to say that if A5 of a document is blank, close it without saving via the below code:



Sub CloseWorkbook()
If Range("A5").Value = "" Then
ActiveWorkbook.Close True
End If
End Sub


Is there a way to also say as part of this code that if cell A5 is blank in the activeworkbook, then send the Amegy Trades.xls file located at "G:\Katherine Lartigue\Allocations\Amegy Trades.xls" to the recycle bin or delete it?

thanks for the help!

[Aflatoon]

You are actually saying that the workbook should save.

You can use:

Sub CloseWorkbook()
If Range("A5").Value = "" Then
ActiveWorkbook.Close False
kill "G:\Katherine Lartigue\Allocations\Amegy Trades.xls"
End If
End Sub

[Klartigue]

Can that be incorporated in my code to save. Can i say if the cell A5 is blank than kill the workbook but if cell A5 is not blank than:


Public Sub Saveas()
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Fidelity Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

[Aflatoon]

Sure

Sub CloseWorkbook()
If Range("A5").Value = "" Then
ActiveWorkbook.Close False
kill "G:\Katherine Lartigue\Allocations\Amegy Trades.xls"
Else
SaveAs
End If
End Sub

BTW, I personally would avoid using excel method names for your own routines. (eg the Workbook already has a SaveAs method)

[Klartigue]

I did this but the "kill G:\Katherine Lartigue\Allocations\Amegy Trades.xls"
does not seem to be working

Public Sub SaveAmegy()
If Range("A5").Value = "" Then
ActiveWorkbook.Close False
Kill "G:\Katherine Lartigue\Allocations\Amegy Trades.xls"
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close

Application.DisplayAlerts = True

End If

End Sub

[Aflatoon]

Are you deleting the workbook you just closed? Or the one with the code?

[Klartigue]

I am trying to say in the active workbook, if cell A5 is blank, then close the workbook without saving it. There is a folder that already contains Amegy Trades.xls and usually if cell A5 is not blank, then I just save the new workbook over the existig Amegy Trades.xls. But now I would like do it so if cell A5 is blank, close the current workbook and then go and delete Amegy Trades.xls that is already located in a folder..this is the one that I normally save over.

[Bob Phillips]

Is this what you mean


Public Sub SaveAmegy()
If Range("A5").Value = "" Then
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close

Application.DisplayAlerts = True
End If
End Sub

[Klartigue]

When i try the above code i get an error saying invalid or unqualified reference and it highlights .Saved

[Klartigue]

Maybe I can explain better:

I run this everyday for trades. So the Amegy Trades.xls already exists from when I ran this macro yesterday. So yesterdays trades are located at G:\Katherine Lartigue\Allocations\Amegy Trades.xls.

But today when I run the macro, if A5 of todays file is blank, I really dont want it to save in the Allocations folder because then I am just saving a blank file. So when I run the macro today, I would really like to only save it if A5 is not blank. But if A5 is blank, i would like to go ahead and close the file without saving it, as well as delete the Amegy Trades.xls file located in the Allocations folder from yesterday becasue I do not want old allocation files sitting in this folder.

So today, if A5 is not blank, then i will just save over the Amegy Trades.xls file from yesterday. But if A5 is blank, then I will close the file generated by the macro today without saving, as well as deleting yesterdays file that is located in the allocations file.

But if I go this route, there may not always be a Amegy Trades.xls file located in the Allocations folder because I would be deleting the old ones. So i could say IF A5 of the current file is blank, then dont save and IF there is Amegy Trades.xls located in the Allocations folder then delete that.

Does that help?

[Bob Phillips]

When i try the above code i get an error saying invalid or unqualified reference and it highlights .Saved

That should be

Activeworkbook.Saved = True

[Klartigue]

Public Sub SaveAmegy()
If Range("A5").Value = "" Then
ActiveWorkbook.Saved = True
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close

Application.DisplayAlerts = True
End If
End SubThe way my code works, I open a master spreadsheet, Avalon Fixed Income.xls and paste data into it and then save that as something else, Amegy Trades.xls, so that way I always have a blank master spreadsheet to paste into. The above code doesnt delete my Amegy Trades.xls spreadsheet, but it does delete my master spreadsheet.

[Klartigue]

Would this work? I just saved the document first and then proceeded with your step.

Public Sub SaveAmegy()
If Range("A5").Value = "" Then
ActiveWorkbook.Saved = True
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close

Application.DisplayAlerts = True
End If
End Sub
Sub EmailAmegy()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("dianne.mitchell@amegybank.com")
oRecipient.Type = 1

.CC = "trust.ops.central@amegybanl.com"
.Subject = "Avalon Trade Allocations Attached"

.body = "Please see the attached trade allocations." & vbNewLine & vbNewLine & _
"Let me know if you need anything else." & vbNewLine & vbNewLine & _
"Thanks, " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com " & vbNewLine & _
"713-238-2088"

.Attachments.Add ("G:\Katherine Lartigue\Allocations\Amegy Trades.xls")
.Save

End With

End Sub

[Klartigue]

this works great! Here is my whole code:

Public Sub SaveAmegy1()

Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Application.DisplayAlerts = True
End Sub
Public Sub SaveAmegy()
If Range("A5").Value = "" Then
ActiveWorkbook.Saved = True
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.Close

Application.DisplayAlerts = True
End If
End Sub
Sub EmailAmegy()
Const FILE_ATTACH As String = _
"G:\Katherine Lartigue\Allocations\Amegy Trades.xls"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("dianne.mitchell@amegybank.com")
oRecipient.Type = 1

.CC = "trust.ops.central@amegybanl.com"
.Subject = "Avalon Trade Allocations Attached"

.body = "Please see the attached trade allocations." & vbNewLine & vbNewLine & _
"Let me know if you need anything else." & vbNewLine & vbNewLine & _
"Thanks, " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com " & vbNewLine & _
"713-238-2088"

If Dir(FILE_ATTACH, vbNormal) <> "" Then

.Attachments.Add ("G:\Katherine Lartigue\Allocations\Amegy Trades.xls")
.Save
End If

End With

End Sub

If A5 was blank, then the workbook didnt save and the old one was deleted. And as a result if there was no document in the allocations folder then an email was not drafed. This code works like a charm, thank you!!!