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
   Else
      On Error GoTo GoofedUp
      Rem *** Do da Excel magic! ***
      Dim appExcel As Variant, txt
      Set appExcel = CreateObject("Excel.application")
      appExcel.Visible = False
      appExcel.Workbooks.Add
      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
      Wend
      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 ***
      appExcel.ActiveSheet.Columns.AutoFit
      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.ActiveWorkbook.ActiveSheet.Range("C7").Select
   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

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

End Sub

Select allOpen in new window