엑셀VBA_스닙펫:엑셀 시트를 나누어 출력함과 동시에 출력 헤더,푸터를 설정

activeSheetPageSetup.bas

'작성일 : 2012.09.11
'작성자 : 서재원
'출처 : http://support.microsoft.com/kb/2669736/ko
'설명 : 엑셀용 VBA모듈
' 엑셀 시트를 나누어 출력하도록 양식을 맞추어 준다.
Sub activeSheetPageSetup(ByVal printOrientation As XlPageOrientation, ByVal leftHeaderImgSrc As String)
	Dim targetOrientation As XlPageOrientation
	targetOrientation = printOrientation
	'페이지를레이아웃뷰로변경
	ActiveWindow.View = xlPageLayoutView
	ActiveWindow.Zoom = 55
	'2.페이지넓이:1페이지, 높이:자동
	Application.PrintCommunication = False
	'좌상단 헤더이미지삽입
	Application.PrintCommunication = False
	With ActiveSheet.pageSetup
		.PrintTitleRows = ""
		.PrintTitleColumns = ""
	
		'커스터마이즈 포인트
		.CenterHeader = "&""돋움,보통""비밀(Confidential) " & Chr(10) & ""
		.CenterFooter = "ME, LTD. ALL RIGHTS RESERVED."
		'끝에 공백문자를 넣는 것에 주목. 엑셀 자체 에러로 글씨가 잘리는 경우가 있다.
		.RightFooter = "- &P -" & Chr(10) & ""
		.leftHeader = "&G" & Chr(10) & ""
		'.LeftHeaderPicture.Filename = "C:\Users\cinos\Desktop\Logo.png"
		.LeftHeaderPicture.Filename = leftHeaderImgSrc
		.LeftMargin = Application.InchesToPoints(0.25)
		.RightMargin = Application.InchesToPoints(0.25)
		.TopMargin = Application.InchesToPoints(0.75)
		.BottomMargin = Application.InchesToPoints(0.75)
		.HeaderMargin = Application.InchesToPoints(0.3)
		.FooterMargin = Application.InchesToPoints(0.3)
	
		'70%줌
		.Zoom = 70
		.PrintErrors = xlPrintErrorsDisplayed
		.OddAndEvenPagesHeaderFooter = False
		.DifferentFirstPageHeaderFooter = False
		.ScaleWithDocHeaderFooter = True
		.AlignMarginsHeaderFooter = True

		.EvenPage.leftHeader.Text = ""
		.EvenPage.CenterHeader.Text = ""
		.EvenPage.RightHeader.Text = ""
		.EvenPage.LeftFooter.Text = ""
		.EvenPage.CenterFooter.Text = ""
		.EvenPage.RightFooter.Text = ""
		.FirstPage.leftHeader.Text = ""
		.FirstPage.CenterHeader.Text = ""
		.FirstPage.RightHeader.Text = ""
		.FirstPage.LeftFooter.Text = ""
		.FirstPage.CenterFooter.Text = ""
		.FirstPage.RightFooter.Text = ""
		.FitToPagesWide = 1
		.FitToPagesTall = 0
		
		'방향지정
		.orientation = targetOrientation
		.CenterHorizontally = True
		.CenterVertically = True
		.PaperSize = xlPaperA4
	End With

	Application.PrintCommunication = True
End Sub

'테스트 코드

Sub activeSheetPageSetupTest()
	Call activeSheetPageSetup(xlPortrait, "C:\Users\cinos\Desktop\Logo.png")
End Sub