excel Page setup with VB6

Hello all

I have this code where i transfer my MSHFlexgrid to excel in a specific format.

But now, i would like to also set the page setup automatically so that if i print it, the setup will be good.

For example,

1- The first 6 rows will always be in all pages.
2- On the bottom of each sheet, i will have the page number out of!!!!!! (Ex 1/2, 2/2)
3- The Righ tMargin would be 0.75
4- The Left tMargin would be 0.75
5- The Bottom Margin would be 0.5
6- The Top Margin would be 1

How can i do that?

Thanks again for your help

Sub FlexExcel(fg As MSHFlexGrid)
   Dim c As Integer, a As Integer
   Dim TotalRecs As Long, r As Long, cnt As Long
   Rem *** Get total number of records ***
   TotalRecs = fg.Rows - 1
   If TotalRecs = 0 Then
      MsgBox "Zero records to export", vbCritical
      On Error GoTo GoofedUp
      Rem *** Do da Excel magic! ***
      Dim appExcel As Variant, txt
      Set appExcel = CreateObject("Excel.application")
      appExcel.Visible = False
      Rem *** Add column headers ***
      For c = 0 To fg.Cols - 1
         appExcel.Cells(5, c + 1).Font.Bold = True
         appExcel.Cells(5, c + 1).Formula = fg.TextMatrix(0, c)
         appExcel.Cells(5, c + 1).Borders.Weight = 2
         appExcel.Cells(5, c + 1).Interior.Color = RGB(205, 197, 191)
         appExcel.Cells(6, c + 1).RowHeight = 2
      Next c
      r = 0
      Rem *** Add data ***
      While cnt < TotalRecs
         r = r + 1
         txt = FlexGet(fg, r)
         For c = 0 To fg.Cols - 1
            appExcel.Range(Chr$(65 + c) & CStr(r + 6)) = ParseLine(txt, vbTab, c + 1)
         Next c
         cnt = cnt + 1
      Rem *** Add cell borders ***
      appExcel.Range("A5:" & Chr$(64 + fg.Cols) & CStr(TotalRecs + 6)).Borders.Weight = 1
      Rem *** Resize all columns to width of their content ***
      Rem *** Set proper column alignment
      For c = 0 To fg.Cols - 1
         Select Case fg.ColAlignment(c)
            Case 0 To 2 'Left
               a = 2
            Case 3 To 5 'Center
               a = 3
            Case 6 To 8 'Right
               a = 4
            Case Else   'Contents
               a = 1
         End Select
         appExcel.ActiveSheet.Columns(Chr$(65 + c)).HorizontalAlignment = a
      Next c
      appExcel.Visible = True
   End If
   appExcel.ActiveWindow.FreezePanes = True
   With appExcel.ActiveWorkbook.ActiveSheet
   appExcel.ActiveWorkbook.ActiveSheet.Range("a1") = "Periode d'acftivité: " & Form3.DTPicker1 & " - " & Form3.DTPicker2
    appExcel.ActiveWorkbook.ActiveSheet.Range("a1").Font.Bold = True
    appExcel.ActiveWorkbook.ActiveSheet.Range("a1").Font.Size = 17
    End With
    appExcel.ActiveWorkbook.ActiveSheet.Range("a3") = "Date du rapport:"
    appExcel.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
    appExcel.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "dddd dd mmmm yyyy")
    appExcel.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
    appExcel.ActiveWorkbook.ActiveSheet.Range("D5").NumberFormat = "0"

   Exit Sub

   If Err.Number >= 1 Then
      MsgBox Err.Description, vbCritical, Err.Number
   End If

End Sub

Open in new window

LVL 11
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Here is part if the answer

With Worksheets(1).PageSetup
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1.0)
    .BottomMargin = Application.InchesToPoints(0.5)
    '.HeaderMargin = Application.InchesToPoints(0.5)
   ' .FooterMargin = Application.InchesToPoints(0.5)
End With

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:

 .RightFooter = "Page &P of &N" ' or .LeftFooter or .CenterFooter
Martin LissOlder than dirtCommented:
And maybe

.PrintTitleRows = "$1:$6"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Wilder1626Author Commented:

I have this compile error Method or data member not found on:.InchesToPoints

do you know why?
Martin LissOlder than dirtCommented:
What version of Excel are you using?
Wilder1626Author Commented:
I'm using Microsoft Office 2007.
Martin LissOlder than dirtCommented:
Are you using it with the Worksheets object because this shows that it should work?

Wilder1626Author Commented:
Oh, i think that like this is working:

 'page setup
    With Worksheets(1).PageSetup
    .LeftMargin = appExcel.InchesToPoints(0.75)
    .RightMargin = appExcel.InchesToPoints(0.75)
    .TopMargin = appExcel.InchesToPoints(1#)
    .BottomMargin = appExcel.InchesToPoints(0.5)
    .HeaderMargin = appExcel.InchesToPoints(0.5)
    .FooterMargin = appExcel.InchesToPoints(0.5)
      .RightFooter = "Page &P of &N"
      .PrintTitleRows = "$1:$6"
    End With

Open in new window

Let me do one more test
Wilder1626Author Commented:
perfect. Thanks

it all work now.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

My Articles:
Using the VB6 DebuggerAutomatic Insertion of Procedure Names
A Textbox ActiveX Control That Limits Input to NumbersSpell Check a Textbox
Improved Formatting TagsConditional CompilationDynamically Resize Form Controls

Marty - MVP 2009, 2010, 2011
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.