Print to File: In Excel?

[pk]

Hello, I hope someone can help me with this tough one...

Am using VBA in Excel XP with Windows 2000.

I have several Excel files each consisting of several
pages. I need code to extract each individual page from
each file and save them into separate Excel files.

I was wondering if it is possible to do this, by using VBA
to print each page to a file (as in the [File][Print]
dialog box) to an Excel file rather than a text file. I
need to retain all formats.

Obviously, some pages have partial last pages that extend
beyond the last page break. Some pages include hard page
breaks, some don't. I think for the most part I only have
to worry about horizontal page breaks.

Whether this is or isn't the way to go, could you please
supply me with your example code and best ideas on the way
to proceed? Thanks mountains in advance...

 

[Jan Karel Pieterse]

Hi,

This code would save each worksheet of the active workbook:

Sub SaveSheets()
Dim sFilename As String
Dim sPath As String
Dim oWorkbook As Workbook
Dim oSheet As Worksheet
Dim oTempbook As Workbook
sFilename = "Copy of sheet "
sPath = "c:\windows\temp\"
Set oWorkbook = ActiveWorkbook
For Each oSheet In oWorkbook.Worksheets
oSheet.Copy
Set oTempbook = ActiveWorkbook
oTempbook.SaveAs sPath & sFilename &
oTempbook.Worksheets(1).Name & ".xls"
oTempbook.Close False
Next
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP