close workbook/pdf conversion

G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top