Thread: Excel VBA - Printing Front And Back - VBForums

excel has no provision in vba to print in duplex, even if it is in the printer dialog, and works from the dialog Register Help RememberMe? Forum NewPosts FAQ Calendar ForumActions MarkForumsRead QuickLinks Today'sPosts ViewSiteLeaders What'sNew? AdvertiserDisclosure AdvancedSearch VBForums VisualBasic OfficeDevelopment ExcelVBA-PrintingFrontAndBack Ifthisisyourfirstvisit,besureto checkouttheFAQbyclickingthe linkabove.Youmayhavetoregister beforeyoucanpost:clicktheregisterlinkabovetoproceed.Tostartviewingmessages, selecttheforumthatyouwanttovisitfromtheselectionbelow. Results1to13of13 Thread:ExcelVBA-PrintingFrontAndBack ThreadTools ShowPrintableVersion Display LinearMode SwitchtoHybridMode SwitchtoThreadedMode Mar9th,2009, 12:05PM #1 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 ExcelVBA-PrintingFrontAndBack Iwantaparticularreporttoprintfront/backoneachpage.So,togetthecodeforthisIdo"recordamacro",thenPageSetup/Options/PrintingShortcuts Fromthe'PrintOnBothSidesDropdown'Iselect"Yes,flipover' I"OK"mywaybacktothespreadsheet,andIgetthefollowingmacro. Code: SubMacro1() ' 'Macro1Macro 'Macrorecorded3/9/2009byHack ' ' WithActiveSheet.PageSetup .PrintTitleRows="" .PrintTitleColumns="" EndWith ActiveSheet.PageSetup.PrintArea="" WithActiveSheet.PageSetup .LeftHeader="" .CenterHeader="" .RightHeader="" .LeftFooter="" .CenterFooter="" .RightFooter="" .LeftMargin=Application.InchesToPoints(0.75) .RightMargin=Application.InchesToPoints(0.75) .TopMargin=Application.InchesToPoints(1) .BottomMargin=Application.InchesToPoints(1) .HeaderMargin=Application.InchesToPoints(0.5) .FooterMargin=Application.InchesToPoints(0.5) .PrintHeadings=False .PrintGridlines=False .PrintComments=xlPrintNoComments .PrintQuality=600 .CenterHorizontally=False .CenterVertically=False .Orientation=xlPortrait .Draft=False .PaperSize=xlPaperLetter .FirstPageNumber=xlAutomatic .Order=xlDownThenOver .BlackAndWhite=False .Zoom=100 .PrintErrors=xlPrintErrorsDisplayed EndWith EndSub Sofar,sogood....Iincorporatethisintomycodeandithasnoaffectwhatsoeverintermsofprintingmyreportfrontandbackoneachpage.WhatamImacro-missing? ReplyWithQuote Mar9th,2009, 03:37PM #2 westconn1 ViewProfile ViewForumPosts PowerPoster JoinDateDec2004 Posts25,587 Re:ExcelVBA-PrintingFrontAndBack excelhasnoprovisioninvbatoprintinduplex,evenifitisintheprinterdialog,andworksfromthedialog youneedtosettheprinterpropertiesbeforeprinting,remembertosetbackafter ihavedonethisbeforehttp://www.vbforums.com/showpost.php...8&postcount=11 thereismoreinformationinotherpartsofthatthread idomybesttotestcodeworksbeforeipostit,butsometimesamunabletodosoforsomereason,andusuallysaysoifthisisthecase. Notecodesnippetspostedarejustthatanddonotincludeerrorhandlingthatisrequiredinrealworldapplications,butavoidOnErrorResumeNext dimallvariablesasrequiredasoftenihavedonesoelsewhereinmycodebutonlypostedtherelevantpart comebackandmarkyouroriginalpostasresolvedifyourproblemisfixed pete ReplyWithQuote Mar10th,2009, 01:14AM #3 SiddharthRout ViewProfile ViewForumPosts VisitHomepage SuperModerator JoinDateFeb2005 LocationMumbai,India Posts11,998 Re:ExcelVBA-PrintingFrontAndBack Hereisasimplealternative... vbCode: PrivateSubCommandButton1_Click()   '~~>Printson BothSidesofapage   DimFromPGAsLong,TotalPGAsLong    '~~>PageSetup   WithActiveSheet.PageSetup     .PrintArea=False     .Order=xlOverThenDown'Blah     '~~>Blah   EndWith      '~~>ExecuteExcel4MacroMethod.Readmoreon   '~~>[url]http://msdn.microsoft.com/en-us/library/aa195716(office.11).aspx[/url]   TotalPG=ExecuteExcel4Macro("Get.Document(50)")    '~~>PrintOddpages   ForFromPG=1ToTotalPGStep2     ActiveSheet.PrintOutFrom:=FromPG,To:=FromPG   Next      '~~>Informuser   Beep   MsgBox"PleaseTurnPagesover..."    '~~>PrintEvenPages   ForFromPG=2ToTotalPGStep2     ActiveSheet.PrintOutFrom:=FromPG,To:=FromPG   Next EndSub TheotherwaytodoistousesendkeysifyouarenotusingVista.somethinglikethis.Youmighthavetoamenditaspertheprinteroption... vbCode: SubPrintBothSides()   SendKeys"%fp%r{TAB}{TAB}{+}~~",True EndSub LasteditedbySiddharthRout;Mar10th,2009at01:22AM. AgoodexercisefortheHeartistobenddownandhelpanotherup... PleaseMarkyourThread"Resolved",ifthequeryissolved MyGear: ★CPU★Ryzen55800X ★GPU★NVIDIAGeForceRTX3080TIFounderEdition ★RAM★G.SkillTridentZRGB32GB3600MHz ★MB★ASUSTUFGAMINGX570(WI-FI)ATXGaming ★Storage★SSDSB-ROCKET-1TB+SEAGATE2TBBarracudaIHD ★Cooling★NOCTUANH-D15CHROMAXBLACK140mm+10ofNoctuaNF-F12PWM ★PSU★ANTECHCG-1000-EXTREME1000Watt80PlusGoldFullyModularPSU ★Case★LIANLIPC-O11DYNAMICXLROG(BLACK)(G99.O11DXL-X) ★Monitor★LGUltragear27"240HzGamingMonitor ★Keyboard★TVSElectronicsGoldKeyboard ★Mouse★LogitechG502Hero ReplyWithQuote Mar10th,2009, 06:16AM #4 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 Re:ExcelVBA-PrintingFrontAndBack OriginallyPostedbywestconn1 excelhasnoprovisioninvbatoprintinduplex,evenifitisintheprinterdialog,andworksfromthedialog youneedtosettheprinterpropertiesbeforeprinting,remembertosetbackafter ihavedonethisbeforehttp://www.vbforums.com/showpost.php...8&postcount=11 thereismoreinformationinotherpartsofthatthread Iactuallyfoundthatthreadonasearch.Ifduplexwasastatedrequirementforthisproject,Iwouldhaveemployedthepostedsolution.But,thisprojectisgoingtogetturnedovertoanotherpersonwhenI'mdone,andthatcodewouldjustblowthemaway. @koolsid:xlOverThenDowndoesn'tworkforme.Itstillprintsonesidedonly. ReplyWithQuote Mar10th,2009, 07:42AM #5 SiddharthRout ViewProfile ViewForumPosts VisitHomepage SuperModerator JoinDateFeb2005 LocationMumbai,India Posts11,998 Re:ExcelVBA-PrintingFrontAndBack @koolsid:xlOverThenDowndoesn'tworkforme.Itstillprintsonesidedonly. Hackyoumissedit...Thecodethatigavewillprintitinonedirectionandthenwillbeepsothatyoucanfliptheentirebundleover... AgoodexercisefortheHeartistobenddownandhelpanotherup... PleaseMarkyourThread"Resolved",ifthequeryissolved MyGear: ★CPU★Ryzen55800X ★GPU★NVIDIAGeForceRTX3080TIFounderEdition ★RAM★G.SkillTridentZRGB32GB3600MHz ★MB★ASUSTUFGAMINGX570(WI-FI)ATXGaming ★Storage★SSDSB-ROCKET-1TB+SEAGATE2TBBarracudaIHD ★Cooling★NOCTUANH-D15CHROMAXBLACK140mm+10ofNoctuaNF-F12PWM ★PSU★ANTECHCG-1000-EXTREME1000Watt80PlusGoldFullyModularPSU ★Case★LIANLIPC-O11DYNAMICXLROG(BLACK)(G99.O11DXL-X) ★Monitor★LGUltragear27"240HzGamingMonitor ★Keyboard★TVSElectronicsGoldKeyboard ★Mouse★LogitechG502Hero ReplyWithQuote Mar10th,2009, 07:46AM #6 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 Re:ExcelVBA-PrintingFrontAndBack OriginallyPostedbykoolsid Hackyoumissedit...Thecodethatigavewillprintitinonedirectionandthenwillbeepsothatyoucanfliptheentirebundleover... Thatiswaytoomuchwork. ReplyWithQuote Mar10th,2009, 09:27AM #7 SiddharthRout ViewProfile ViewForumPosts VisitHomepage SuperModerator JoinDateFeb2005 LocationMumbai,India Posts11,998 Re:ExcelVBA-PrintingFrontAndBack Howabouttheoptionofusingsendkeys?Thatwillactuallyprintonbothsides... AgoodexercisefortheHeartistobenddownandhelpanotherup... PleaseMarkyourThread"Resolved",ifthequeryissolved MyGear: ★CPU★Ryzen55800X ★GPU★NVIDIAGeForceRTX3080TIFounderEdition ★RAM★G.SkillTridentZRGB32GB3600MHz ★MB★ASUSTUFGAMINGX570(WI-FI)ATXGaming ★Storage★SSDSB-ROCKET-1TB+SEAGATE2TBBarracudaIHD ★Cooling★NOCTUANH-D15CHROMAXBLACK140mm+10ofNoctuaNF-F12PWM ★PSU★ANTECHCG-1000-EXTREME1000Watt80PlusGoldFullyModularPSU ★Case★LIANLIPC-O11DYNAMICXLROG(BLACK)(G99.O11DXL-X) ★Monitor★LGUltragear27"240HzGamingMonitor ★Keyboard★TVSElectronicsGoldKeyboard ★Mouse★LogitechG502Hero ReplyWithQuote Mar10th,2009, 01:03PM #8 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 Re:ExcelVBA-PrintingFrontAndBack Iwon'tuseSendKeysforanything. Ireallydidn'tlikethatfunctionallthatmuchtobeginwith.ThefactthatitwilljusthavetobereplacedwhenmovingtoVistaisjustanothernailinitscoffinasfarasI'mconcerned. ReplyWithQuote Mar10th,2009, 03:22PM #9 westconn1 ViewProfile ViewForumPosts PowerPoster JoinDateDec2004 Posts25,587 Re:ExcelVBA-PrintingFrontAndBack Ifduplexwasastatedrequirementforthisproject, sorryithoughtthatwaswhatyouwantedtodo,printbothsides,thecodecouldbeconvertedintoanactivex,thenusedinasinglelinecreateobject iamhavealwaysbeengoingtomakeitastandalone(oneday)asourvistamachineherehasnoduplexprovisionintheprinterdriverforsomereason,thoughthedriverinstallsfromthesamediskastheXPmachines idomybesttotestcodeworksbeforeipostit,butsometimesamunabletodosoforsomereason,andusuallysaysoifthisisthecase. Notecodesnippetspostedarejustthatanddonotincludeerrorhandlingthatisrequiredinrealworldapplications,butavoidOnErrorResumeNext dimallvariablesasrequiredasoftenihavedonesoelsewhereinmycodebutonlypostedtherelevantpart comebackandmarkyouroriginalpostasresolvedifyourproblemisfixed pete ReplyWithQuote Mar11th,2009, 01:30PM #10 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 Re:ExcelVBA-PrintingFrontAndBack OriginallyPostedbywestconn1 iamhavealwaysbeengoingtomakeitastandalone(oneday) Thatwouldbeverycool. ReplyWithQuote Mar11th,2009, 03:28PM #11 westconn1 ViewProfile ViewForumPosts PowerPoster JoinDateDec2004 Posts25,587 Re:ExcelVBA-PrintingFrontAndBack ½donenow............. willpostitlater idomybesttotestcodeworksbeforeipostit,butsometimesamunabletodosoforsomereason,andusuallysaysoifthisisthecase. Notecodesnippetspostedarejustthatanddonotincludeerrorhandlingthatisrequiredinrealworldapplications,butavoidOnErrorResumeNext dimallvariablesasrequiredasoftenihavedonesoelsewhereinmycodebutonlypostedtherelevantpart comebackandmarkyouroriginalpostasresolvedifyourproblemisfixed pete ReplyWithQuote Mar11th,2009, 03:33PM #12 Hack ViewProfile ViewForumPosts ThreadStarter I'mabouttobeaPowerPoster! JoinDateAug2001 LocationSearchingformendhak Posts58,333 Re:ExcelVBA-PrintingFrontAndBack PutitinCodeBank-OtherwithExcelVBAinthethreadtitle ReplyWithQuote Apr6th,2009, 07:17AM #13 westconn1 ViewProfile ViewForumPosts PowerPoster JoinDateDec2004 Posts25,587 Re:ExcelVBA-PrintingFrontAndBack PutitinCodeBank-OtherwithExcelVBAinthethreadtitle uploadedto http://www.vbforums.com/showthread.p...33#post3488633 didn'tspecifyexcelasitcanworkinanything idomybesttotestcodeworksbeforeipostit,butsometimesamunabletodosoforsomereason,andusuallysaysoifthisisthecase. Notecodesnippetspostedarejustthatanddonotincludeerrorhandlingthatisrequiredinrealworldapplications,butavoidOnErrorResumeNext dimallvariablesasrequiredasoftenihavedonesoelsewhereinmycodebutonlypostedtherelevantpart comebackandmarkyouroriginalpostasresolvedifyourproblemisfixed pete ReplyWithQuote QuickNavigation OfficeDevelopment Top SiteAreas Settings PrivateMessages Subscriptions Who'sOnline SearchForums ForumsHome Forums VisualBasic VisualBasic.NET VB.netCodeBank VisualBasic6andEarlier CodeBank-VisualBasic6andearlier UniversalWindowsPlatformandModernWindowsExperience Xamarin MobileDevelopment ASP,VBScript OfficeDevelopment DatabaseDevelopment Reporting API GamesandGraphicsProgramming GameDemos COMandActiveX NetworkProgramming VisualBasicFAQs SlowChatwiththeMicrosoftVisualBasicteam .NETandMore ASP.NETAndASP.NETCore VisualBasic.NET MVC.Net C# MicrosoftAzureandCloudDev WPF,WCF,WF .NETArchitectureandDesign Silverlight General GeneralDeveloperForum IoT,IoE,andMakerForum TestersandTesting ApplicationTesting ApplicationDeployment LinuxDevelopment GeneralPC VBForumsCodingContests ContestEntries CodeItBetter MathsForum OtherLanguages OtherBASIC CandC++ Java PHP XML,HTML,Javascript,WebandCSS jQuery Assembly OtherProgrammingLanguages VBForumsCodeBank CodeBank-VisualBasic.NET CodeBank-VisualBasic6andearlier CodeBank-ASP/ASP.NET/MVC/WebAPI CodeBank-C# CodeBank-C++ CodeBank-Java/J# CodeBank-PHP Codebank-GameProgramming Codebank-MobileDevelopment CodeBank-JavaScript Codebank-CascadingStyleSheets(CSS) CodeBank-Other VBForumsUtilityBank UtilityBank-Utilities UtilityBank-IDEAdd-Ins UtilityBank-Components UtilityBank-Tutorials UtilityBank-Other Projects ProjectRequests ProjectCommunicationArea Jobs JustVBJobs OpenPositions(Jobs) LookingforWork Community ForumFeedback GeneralDiscussion/ChitChat WorldEvents ForumTestArea « PreviousThread | NextThread » VBForums VisualBasic OfficeDevelopment ExcelVBA-PrintingFrontAndBack PostingPermissions Youmaynotpostnewthreads Youmaynotpostreplies Youmaynotpostattachments Youmaynotedityourposts   BBcodeisOn SmiliesareOn [IMG]codeisOn [VIDEO]codeisOn HTMLcodeisOff ForumRules ClickHeretoExpandForumtoFullWidth TermsofService| AboutUs| PrivacyNotice| ContactUs| Advertise| Sitemap| California-DoNotSellMyInfo AdvertiserDisclosure: SomeoftheproductsthatappearonthissitearefromcompaniesfromwhichTechnologyAdvicereceivescompensation.Thiscompensationmayimpacthowandwhereproductsappearonthissiteincluding,forexample,theorderinwhichtheyappear.TechnologyAdvicedoesnotincludeallcompaniesoralltypesofproductsavailableinthemarketplace. AlltimesareGMT-5.Thetimenowis03:52AM.