I have a macro that creates copys data from a sheet called "template", pastes
it into a sheet called "tempsheet", then converts "tempsheet" to a pdf, and
attaches it to an email. However, during the PDF conversion process, my
original workbook is closed, and I am left with the temporary workbook
created to form the PDF. I would like to kill the temporary file, and leave
open the template. Here is the context surrounding the PDF conversion. Any
help would be great. Thanks!

Sheets("TempSheet").Activate
Dim lastPrinter As String, myPath As String
Dim myFileName As String, Wb As Workbook
myPath = "U:\Temp\"
myFileName = Sheets("Template").Range("B1")
myfullpath = myPath & myFileName & ".xls"
Worksheets("TempSheet").Range("a1:f100").Copy
Set Wb = ActiveWorkbook
Wb.SaveAs myfullpath
lastPrinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Wb.Close (False)
Kill myfullpath
Application.ActivePrinter = lastPrinter

What about doing this:

kill Wb

Instead of

kill myfullpath


--
Adios,
Clay Harryman


"ToddEZ" wrote:

> I have a macro that creates copys data from a sheet called "template", pastes
> it into a sheet called "tempsheet", then converts "tempsheet" to a pdf, and
> attaches it to an email. However, during the PDF conversion process, my
> original workbook is closed, and I am left with the temporary workbook
> created to form the PDF. I would like to kill the temporary file, and leave
> open the template. Here is the context surrounding the PDF conversion. Any
> help would be great. Thanks!
>
> Sheets("TempSheet").Activate
> Dim lastPrinter As String, myPath As String
> Dim myFileName As String, Wb As Workbook
> myPath = "U:\Temp\"
> myFileName = Sheets("Template").Range("B1")
> myfullpath = myPath & myFileName & ".xls"
> Worksheets("TempSheet").Range("a1:f100").Copy
> Set Wb = ActiveWorkbook
> Wb.SaveAs myfullpath
> lastPrinter = Application.ActivePrinter
> Application.ActivePrinter = "Adobe PDF on Ne05:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> Wb.Close (False)
> Kill myfullpath
> Application.ActivePrinter = lastPrinter

Unfortunately that yielded the same results. Thanks for the suggestion though.

"Clayman" wrote:

> What about doing this:
>
> kill Wb
>
> Instead of
>
> kill myfullpath
>
>
> --
> Adios,
> Clay Harryman
>
>
> "ToddEZ" wrote:
>
> > I have a macro that creates copys data from a sheet called "template", pastes
> > it into a sheet called "tempsheet", then converts "tempsheet" to a pdf, and
> > attaches it to an email. However, during the PDF conversion process, my
> > original workbook is closed, and I am left with the temporary workbook
> > created to form the PDF. I would like to kill the temporary file, and leave
> > open the template. Here is the context surrounding the PDF conversion. Any
> > help would be great. Thanks!
> >
> > Sheets("TempSheet").Activate
> > Dim lastPrinter As String, myPath As String
> > Dim myFileName As String, Wb As Workbook
> > myPath = "U:\Temp\"
> > myFileName = Sheets("Template").Range("B1")
> > myfullpath = myPath & myFileName & ".xls"
> > Worksheets("TempSheet").Range("a1:f100").Copy
> > Set Wb = ActiveWorkbook
> > Wb.SaveAs myfullpath
> > lastPrinter = Application.ActivePrinter
> > Application.ActivePrinter = "Adobe PDF on Ne05:"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> > Wb.Close (False)
> > Kill myfullpath
> > Application.ActivePrinter = lastPrinter

Dim lastPrinter As String, myPath As String
Dim WB as Workbook, WB1 as Workbook
Dim rng as Range
Dim myFileName As String, Wb As Workbook
Sheets("TempSheet").Activate
myPath = "U:\Temp\"
myFileName = Sheets("Template").Range("B1")
myfullpath = myPath & myFileName & ".xls"
Set Wb1 = ActiveWorkbook
set rng = WB1.Worksheets("TempSheet").Range("a1:f100")
set WB = Workbooks.Add(Template:=xlWBATWorksheet)
rng.copy WB.Worksheets(1).Range("A1")
Wb.SaveAs myfullpath
lastPrinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Wb.Close (False)
Kill myfullpath
Application.ActivePrinter = lastPrinter

--
Regards,
Tom Ogilvy

"ToddEZ" wrote:

> I have a macro that creates copys data from a sheet called "template", pastes
> it into a sheet called "tempsheet", then converts "tempsheet" to a pdf, and
> attaches it to an email. However, during the PDF conversion process, my
> original workbook is closed, and I am left with the temporary workbook
> created to form the PDF. I would like to kill the temporary file, and leave
> open the template. Here is the context surrounding the PDF conversion. Any
> help would be great. Thanks!
>
> Sheets("TempSheet").Activate
> Dim lastPrinter As String, myPath As String
> Dim myFileName As String, Wb As Workbook
> myPath = "U:\Temp\"
> myFileName = Sheets("Template").Range("B1")
> myfullpath = myPath & myFileName & ".xls"
> Worksheets("TempSheet").Range("a1:f100").Copy
> Set Wb = ActiveWorkbook
> Wb.SaveAs myfullpath
> lastPrinter = Application.ActivePrinter
> Application.ActivePrinter = "Adobe PDF on Ne05:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> Wb.Close (False)
> Kill myfullpath
> Application.ActivePrinter = lastPrinter

this worked great! thanks for taking the time to help.

"Tom Ogilvy" wrote:

>
> Dim lastPrinter As String, myPath As String
> Dim WB as Workbook, WB1 as Workbook
> Dim rng as Range
> Dim myFileName As String, Wb As Workbook
> Sheets("TempSheet").Activate
> myPath = "U:\Temp\"
> myFileName = Sheets("Template").Range("B1")
> myfullpath = myPath & myFileName & ".xls"
> Set Wb1 = ActiveWorkbook
> set rng = WB1.Worksheets("TempSheet").Range("a1:f100")
> set WB = Workbooks.Add(Template:=xlWBATWorksheet)
> rng.copy WB.Worksheets(1).Range("A1")
> Wb.SaveAs myfullpath
> lastPrinter = Application.ActivePrinter
> Application.ActivePrinter = "Adobe PDF on Ne05:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> Wb.Close (False)
> Kill myfullpath
> Application.ActivePrinter = lastPrinter
>
> --
> Regards,
> Tom Ogilvy
>
> "ToddEZ" wrote:
>
> > I have a macro that creates copys data from a sheet called "template", pastes
> > it into a sheet called "tempsheet", then converts "tempsheet" to a pdf, and
> > attaches it to an email. However, during the PDF conversion process, my
> > original workbook is closed, and I am left with the temporary workbook
> > created to form the PDF. I would like to kill the temporary file, and leave
> > open the template. Here is the context surrounding the PDF conversion. Any
> > help would be great. Thanks!
> >
> > Sheets("TempSheet").Activate
> > Dim lastPrinter As String, myPath As String
> > Dim myFileName As String, Wb As Workbook
> > myPath = "U:\Temp\"
> > myFileName = Sheets("Template").Range("B1")
> > myfullpath = myPath & myFileName & ".xls"
> > Worksheets("TempSheet").Range("a1:f100").Copy
> > Set Wb = ActiveWorkbook
> > Wb.SaveAs myfullpath
> > lastPrinter = Application.ActivePrinter
> > Application.ActivePrinter = "Adobe PDF on Ne05:"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> > Wb.Close (False)
> > Kill myfullpath
> > Application.ActivePrinter = lastPrinter