| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| workbook before close | Excel Discussion (Misc queries) | |||
| Creating an Excel Workbook in Access/VBA - CSV to XLS conversion | Excel Programming | |||
| Help on Workbook close and workbook save events | Excel Programming | |||
| Close a the current workbook and load another specified workbook | Excel Programming | |||
| Unwanted date conversion when opening a workbook | Excel Programming | |||
Linear Mode
