Re: Kill File

[tbamrah]

Re: Kill File

Hi All,

I have a workbook which is sent to the end user via email.

On openning the attachment (.xls), the user has the option of filing a form and either:

1. Saving in desired location to come back to later and resume filling in, or
2. a "Completed & Send Back Button"

My issue is with point 2.

(Note: The Filled in data is placed onto the spreadsheet)

I would like the file to be "Temporarily Saved" then attached to send back.

I have been unlucky in attempting to save the File in the users own "Temp" Folder, so decided that the macro would save the spreadsheet to a common local area (i.. desktop), attach and send back, open a new instance of a workbook, paste macro and call this macro which woulf delete (Kill) the temporarily desktop saved file.

Below is a test macros of this process.

The macro works but doesnt kill the file.

However, if i call Sub Killme manually, the file DOES delete.

Any ideas??

Sub AddNewMacro()
ThisWorkbook.SaveAs "Deskptop Directory" & "\Test 1.xlsm"
Y = ThisWorkbook.Name
Set Newbook = Workbooks.Add
X = Newbook.Name
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBComp2 As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = Workbooks(X).VBProject
Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "Module1"
Dim LineNum As Long
Const DQUOTE = """"
Set VBProj = Workbooks(X).VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub Test()"
LineNum = LineNum + 1
.InsertLines LineNum, "Workbooks(" & DQUOTE & Y & DQUOTE & ").close SaveChanges:=False"
LineNum = LineNum + 1
.InsertLines LineNum, "Application.wait(Now + TimeValue(" & DQUOTE & "00:00:05" & DQUOTE & "))"
LineNum = LineNum + 1
.InsertLines LineNum, "Call Killme"
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With

Set VBComp2 = VBProj.VBComponents("Module2")
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub Killme()"
LineNum = LineNum + 1
.InsertLines LineNum, " Kill " & DQUOTE & "Deskptop Directory" & DQUOTE & "\Test 1.xlsm"
LineNum = LineNum + 1
.InsertLines LineNum, "Msgbox" & DQUOTE & "Done!" & DQUOTE
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With

Workbooks(X).Activate

Application.Run (X & "!Test")

End Sub

 

[RoryA]

Re: Kill File

If the user doesn't have Trusted access to the VBProject set, your code won't work anyway.
Can't you simply use SaveCopyAs to save a temp copy of the workbook, then add it to an email and delete it? Or just use the Workbook's Sendmail method? What you are trying seems unnecessarily complicated unless there are details you haven't mentioned.

 

[tbamrah]

Re: Kill File

Rory,

Thank you for your reply - Perhaps I am being a bit cowboy-ish about the task.

You mention the SaveCopyAs Procedure. Where would you suggest that I could save this? i.e. retrieving the users temp folder directory, and also, will the system allow this without requiring adminstrative rights?

 

[RoryA]

Re: Kill File

You can save it wherever you desire - I assumed you already had code to determine a save location?

 

[tbamrah]

Re: Kill File

Which is All good, but I Intend on deleting the temporarily saved file/copy of file - which takes me back to the kill not working.

Any ideas how to then delete this file using the macro?

 

[RoryA]

Re: Kill File

Essentially you need:

activeworkbook.savecopyas "some path"
' do whatever you plan to do with the copy
kill "some path"

If you get an error with that then it would indicate that either the file is still in use (presumably by the email program) or you do not have delete permissions (which would be unusual if you have write permissions).

 

[tbamrah]

Re: Kill File

Top Man! Worked + V.Simple,

But atleast i managed to learn a bit about adding new macros to new books!

Nice 1!