Total Pages in Sheet

robertfah · May 2nd, 2002, 06:35 AM

Hi,
I'd like to know if there's a way (using VBA) to get the total number of pages of a sheet?
I've tried difference variations of this code:

HorizBreaks = Worksheets(1).HPageBreaks.Count
HPages = HorizBreaks + 1
VertBreaks = Worksheets(1).VPageBreaks.Count
VPages = VertBreaks + 1
NumPages = HPages * VPages

and it still doesn't work. Any help you can give me would be greatly appreciated.

Jay Petrulis · May 2nd, 2002, 06:41 AM

Hi Robert,

PageCount = ExecuteExcel4Macro("Get.Document(50)")

For more detail, follow the responses in this link.

http://mrexcel.com/board/viewtopic.p...c=5067&forum=2

Bye,
Jay

robertfah · May 2nd, 2002, 06:47 AM

WOW.....really fast service....like 5 minutes fast......Thanks a million Jay...the code works awesome!

robertfah · May 2nd, 2002, 07:02 AM

Jay,
I'm using this in Access and I am making a connection to excel like this:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'Setup Excel
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

but when I try your code (which I think is right):

intPageCount = xlApp.ExecuteExcel4Macro("Get.Document(50)")

it returns 1.....any clue?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 06:35 AM	#1
robertfah New Member Join Date: May 2002 Posts: 3	Hi, I'd like to know if there's a way (using VBA) to get the total number of pages of a sheet? I've tried difference variations of this code: HorizBreaks = Worksheets(1).HPageBreaks.Count HPages = HorizBreaks + 1 VertBreaks = Worksheets(1).VPageBreaks.Count VPages = VertBreaks + 1 NumPages = HPages * VPages and it still doesn't work. Any help you can give me would be greatly appreciated.

May 2nd, 2002, 06:41 AM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi Robert, PageCount = ExecuteExcel4Macro("Get.Document(50)") For more detail, follow the responses in this link. http://mrexcel.com/board/viewtopic.p...c=5067&forum=2 Bye, Jay

May 2nd, 2002, 06:47 AM	#3
robertfah New Member Join Date: May 2002 Posts: 3	WOW.....really fast service....like 5 minutes fast......Thanks a million Jay...the code works awesome!

May 2nd, 2002, 07:02 AM	#4
robertfah New Member Join Date: May 2002 Posts: 3	Jay, I'm using this in Access and I am making a connection to excel like this: Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet 'Setup Excel Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) but when I try your code (which I think is right): intPageCount = xlApp.ExecuteExcel4Macro("Get.Document(50)") it returns 1.....any clue?