PDA

View Full Version : Solved: create workbook, delete workbook



ironj32
03-12-2007, 08:42 AM
I have the code to create a copy of a worksheet into a new workbook. For some reason I cannot figure out the code to delete the workbook...you think it would be simple, lol.

Sub CopyWorksheet()
Sheets("Saved").Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "\" & Me.txtVendorName & ".xls"
wb.Close
End Sub


i have a button in userform (which many of you have helped me with already) that sends the new workbook as an email attachment, and i would like it to delete the new workbook too.

Thanks!

vonpookie
03-12-2007, 09:02 AM
You can use the Kill statement, but you have to feed it the full path to the filename. I would just set the path as a variable:
Sub CopyWorksheet()
Dim wb As Workbook, wbName As String

Sheets("Saved").Copy
Set wb = ActiveWorkbook

'save full path as string variable
wbName = ThisWorkbook.Path & "\" & Me.txtVendorName & ".xls"

'save workbook with that path/name
wb.SaveAs wbName

wb.Close

'delete the workbook
Kill wbName

End Sub

ironj32
03-12-2007, 09:19 AM
Thanks, that's what i needed!

ended up using it like this:

Sub CopyWorksheet()
Dim wb As Workbook, wbName As String
Sheets("Saved").Copy
Set wb = ActiveWorkbook
'save full path as string variable
wbName = ThisWorkbook.Path & "\" & Me.txtVendorName & ".xls"
'save workbook with that path/name
wb.SaveAs wbName
wb.Close
End Sub
Sub DeleteWorkbook()
Dim wbName As String
wbName = ThisWorkbook.Path & "\" & Me.txtVendorName & ".xls"
Kill wbName
End Sub