• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 6
  • 4
1 Solution
 
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

0
 
Martin LissOlder than dirtCommented:
And

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

.PrintTitleRows = "$1:$6"
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Wilder1626Author Commented:
Hello

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


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

0
 
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
0
 
Wilder1626Author Commented:
perfect. Thanks


it all work now.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now