Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet after printed
I would like to save the worksheet after it is printed. I have the
"saving part" of the code below. I can't figure out how to get it to execute after the worksheet is printed. I've been researching different types of Methods (Events), but can't seem to identify any that will run my Sub Procedure AFTER the worksheet is printed out. How can I get my code to REACT to a PrintOut Event (Method)? I've tried conditionals like IF Worksheet.PrintOut THEN . . . but it doesn't seem to work. Maybe I should use some type of Do While, or Do Until Loop, or something? What is wrong with my approach?Does anyone have any suggestions? Here's the code I have written which saves the worksheets: Sub Save_Printout() Dim s As String, Sh As Worksheet Set Sh = ActiveSheet Sh.Copy s = "c:\Printed Worksheets\" & Range ("M5").Value 'location and names of workbooks ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy 'selects all cells Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values & number formats only Application.CutCopyMode = False Range("A1").Select 'this is just to take the cursor back to the top to make that sheet a litte "cleaner" ActiveWorkbook.Close SaveChanges:=True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet after printed
Mason,
I'm not sure if this will work, but you could use the BeforePrint event and inside it cancel the print job that triggered the event, then do the printing from inside the event and then call your saving sub. I think getting it to print correctly may be difficult, but I don't know. Here's a stab at it: Private Sub Workbook_BeforePrint(Cancel As Boolean) 'turn off events so the "ThisWorkbook.Printout" command 'below won't start an endless loop of BeforePrint events Application.EnableEvents = False 'cancel the printing that triggered this event Cancel = True 'start our own printing ThisWorkbook.PrintOut Call Save_Printout() 'turn events back on 'Application.EnableEvents = True End Sub hth, Doug "Mason" wrote in message ... I would like to save the worksheet after it is printed. I have the "saving part" of the code below. I can't figure out how to get it to execute after the worksheet is printed. I've been researching different types of Methods (Events), but can't seem to identify any that will run my Sub Procedure AFTER the worksheet is printed out. How can I get my code to REACT to a PrintOut Event (Method)? I've tried conditionals like IF Worksheet.PrintOut THEN . . . but it doesn't seem to work. Maybe I should use some type of Do While, or Do Until Loop, or something? What is wrong with my approach?Does anyone have any suggestions? Here's the code I have written which saves the worksheets: Sub Save_Printout() Dim s As String, Sh As Worksheet Set Sh = ActiveSheet Sh.Copy s = "c:\Printed Worksheets\" & Range ("M5").Value 'location and names of workbooks ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy 'selects all cells Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values & number formats only Application.CutCopyMode = False Range("A1").Select 'this is just to take the cursor back to the top to make that sheet a litte "cleaner" ActiveWorkbook.Close SaveChanges:=True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet after printed
On Sep 12, 10:16*pm, "Doug Glancy"
wrote: Mason, I'm not sure if this will work, but you could use the BeforePrint event *and inside it cancel the print job that triggered the event, then do the printing from inside the event and then call your saving sub. *I think getting it to print correctly may be difficult, but I don't know. *Here's a stab at it: Private Sub Workbook_BeforePrint(Cancel As Boolean) 'turn off events so the "ThisWorkbook.Printout" command 'below won't start an endless loop of BeforePrint events Application.EnableEvents = False 'cancel the printing that triggered this event Cancel = True 'start our own printing ThisWorkbook.PrintOut Call Save_Printout() 'turn events back on 'Application.EnableEvents = True End Sub hth, Doug "Mason" wrote in message ... I would like to save the worksheet after it is printed. I have the "saving part" of the code below. I can't figure out how to get it to execute after the worksheet is printed. I've been researching different types of Methods (Events), but can't seem to identify any that will run my Sub Procedure AFTER the worksheet is printed out. How can I get my code to REACT to a PrintOut Event (Method)? I've tried conditionals like IF Worksheet.PrintOut THEN *. . . but it doesn't seem to work. Maybe I should use some type of Do While, or Do Until Loop, or something? What is wrong with my approach?Does anyone have any suggestions? Here's the code I have written which saves the worksheets: Sub Save_Printout() Dim s As String, Sh As Worksheet Set Sh = ActiveSheet Sh.Copy s = "c:\Printed Worksheets\" & Range ("M5").Value 'location and names of workbooks ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy 'selects all cells Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values & number formats only Application.CutCopyMode = False Range("A1").Select 'this is just to take the cursor back to the top to make that sheet a litte "cleaner" ActiveWorkbook.Close SaveChanges:=True End Sub- Hide quoted text - - Show quoted text - Thanks for the suggestion Doug. This is a REALLY interesting and innovative approach. I can't seem to get the private procedure to only print the active worksheet. The private sub want's to print ALL SHEETS. Any suggestions on how to get it to just print the active (selected) worksheet? -mason |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet after printed
"Mason" wrote in message ... On Sep 12, 10:16 pm, "Doug Glancy" wrote: Mason, I'm not sure if this will work, but you could use the BeforePrint event and inside it cancel the print job that triggered the event, then do the printing from inside the event and then call your saving sub. I think getting it to print correctly may be difficult, but I don't know. Here's a stab at it: Private Sub Workbook_BeforePrint(Cancel As Boolean) 'turn off events so the "ThisWorkbook.Printout" command 'below won't start an endless loop of BeforePrint events Application.EnableEvents = False 'cancel the printing that triggered this event Cancel = True 'start our own printing ThisWorkbook.PrintOut Call Save_Printout() 'turn events back on 'Application.EnableEvents = True End Sub hth, Doug "Mason" wrote in message ... I would like to save the worksheet after it is printed. I have the "saving part" of the code below. I can't figure out how to get it to execute after the worksheet is printed. I've been researching different types of Methods (Events), but can't seem to identify any that will run my Sub Procedure AFTER the worksheet is printed out. How can I get my code to REACT to a PrintOut Event (Method)? I've tried conditionals like IF Worksheet.PrintOut THEN . . . but it doesn't seem to work. Maybe I should use some type of Do While, or Do Until Loop, or something? What is wrong with my approach?Does anyone have any suggestions? Here's the code I have written which saves the worksheets: Sub Save_Printout() Dim s As String, Sh As Worksheet Set Sh = ActiveSheet Sh.Copy s = "c:\Printed Worksheets\" & Range ("M5").Value 'location and names of workbooks ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy 'selects all cells Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values & number formats only Application.CutCopyMode = False Range("A1").Select 'this is just to take the cursor back to the top to make that sheet a litte "cleaner" ActiveWorkbook.Close SaveChanges:=True End Sub- Hide quoted text - - Show quoted text - Thanks for the suggestion Doug. This is a REALLY interesting and innovative approach. I can't seem to get the private procedure to only print the active worksheet. The private sub want's to print ALL SHEETS. Any suggestions on how to get it to just print the active (selected) worksheet? -mason Mason, Glad you like that idea. You should start by just recording a macro that prints the way you want. I'm not much of an expert on printing via VBA, so if that doesn't help you might want to start a new post. Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show date printed, updated only when worksheet printed? | Excel Discussion (Misc queries) | |||
Printing on a pre-printed worksheet in excel 2003 | Excel Discussion (Misc queries) | |||
Save worksheet to a unique filename after worksheet is printed | Excel Programming | |||
Combobox moves when worksheet is printed??? | Excel Programming | |||
How to prevent a specific worksheet from being printed ! | Excel Programming |