Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to save, print and close wb
i'm new in VBE and need some advice on my work. I would like to sav invoice to another name and print that new workbook and then close i by using macro. On my code, run the Save As command to create a new copy of the Workbook(pentagon0002.xls), then don't open up that workbook just close it. that will give me the error "Object Required". What else I need t fix? Sub SaveandPrintout() ActiveWindows.SelectSheets.PrintOut Copies:=1, Collate:=True FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value FileNumStr = Format(FileNum, "0000") ActiveWorkbook.SaveAs Filename:="c:\temp\test\pentagon" & FileNumStr ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindows.Close SaveChanges:=False End Su -- sakun ----------------------------------------------------------------------- sakung's Profile: http://www.excelforum.com/member.php...fo&userid=3723 View this thread: http://www.excelforum.com/showthread.php?threadid=57062 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to save, print and close wb
Hi Sakung,
Try ' define constants for clarity Const filepath As String = "c:\temp\test\" Const filename As String = "pentagon" Const fileext As String = ".xls" Sub SaveandPrintout() FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value FileNumStr = Format(FileNum, "0000") ' save it ActiveWorkbook.SaveAs _ filename:=filepath & filename & FileNumStr & fileext, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False ' print it out ActiveWorkbook.SelectedSheets.PrintOut _ Copies:=1, _ Collate:=True ' close it ActiveWorkbook.Close SaveChanges:=False End Sub I changed the order as you said that you wanted it saved first, added some constants and corrected your reference to the active workbook. -- HTHs Martin "sakung" wrote: i'm new in VBE and need some advice on my work. I would like to save invoice to another name and print that new workbook and then close it by using macro. On my code, run the Save As command to create a new copy of the Workbook(pentagon0002.xls), then don't open up that workbook, just close it. that will give me the error "Object Required". What else I need to fix? Sub SaveandPrintout() ActiveWindows.SelectSheets.PrintOut Copies:=1, Collate:=True FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value FileNumStr = Format(FileNum, "0000") ActiveWorkbook.SaveAs Filename:="c:\temp\test\pentagon" & FileNumStr & ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindows.Close SaveChanges:=False End Sub -- sakung ------------------------------------------------------------------------ sakung's Profile: http://www.excelforum.com/member.php...o&userid=37230 View this thread: http://www.excelforum.com/showthread...hreadid=570623 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to save, print and close wb
The best recommendation for you is to use the macro recorder by clickin Tools, Macros, New Macro... You mimic what you want done, then close it when finished and examine the code -- raypayett ----------------------------------------------------------------------- raypayette's Profile: http://www.excelforum.com/member.php...fo&userid=2956 View this thread: http://www.excelforum.com/showthread.php?threadid=57062 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to save, print and close wb
Thank you to Martin Fishlock and raypayette Your code is working good. Thank you very much. However, I need the old workbook (pentagon0001.xls) still open, only close the new workbook(pentagon0002.xls) that just created. Where can I type "Workbooks.open"? I try to put it before end sub, It doesn't working. -- sakung ------------------------------------------------------------------------ sakung's Profile: http://www.excelforum.com/member.php...o&userid=37230 View this thread: http://www.excelforum.com/showthread...hreadid=570623 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vba - Save but don't close | Excel Worksheet Functions | |||
Save/Close All | Excel Discussion (Misc queries) | |||
To save and close | Excel Worksheet Functions | |||
I like to open a folder,auto print,save then close | Excel Programming | |||
Close without save | Excel Programming |