close workbook/pdf conversion
 

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

close workbook/pdf conversion
 

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

close workbook/pdf conversion
 

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

close workbook/pdf conversion
 

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

close workbook/pdf conversion
 

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