print page numbers

[dmc688]

dear excel gurus,

i’m not familiar with vba project but this piece of code was left by my former colleague. it is supposed to put page numbers on multiple files during printing.
Such that file 1 is page 1, file 2 is page 2, file 3 with 2 pages are pages 3 & 4, file 4 is page 5 and so on...

my question is why is it printing page numbers on other users but not mine. it's selective.
and though print page numbers for that user, he has reported that it skips pages too.
hoping for your feedback. thank you very much!


Sub Button1_Click()
On Error GoTo ErrHandler:
Dim range1 As Long
Dim range2 As Long
Dim xlApp As Object ‘Excel.Application
Dim xlWrk As Object ‘Excel.Workbook
Dim xlSheet As Object ‘Excel.Worksheet
Dim xlID As Integer
Dim path As String
Dim fileFormat As String
path = ActiveSheet.Range(“D6″).Value
fileFormat = ActiveSheet.Range(“D12″).Value
range1 = ActiveSheet.Range(“E9″).Value
range2 = ActiveSheet.Range(“E10″).Value
If range1 <= 0 Or range2 0 Or range2 > 0 Then
For i = range1 To range2
Dim footer As String
Dim fullpath As String
fullpath = path & i & fileFormat
Set xlApp = VBA.CreateObject(“Excel.Application”)
Set xlWrk = xlApp.Workbooks.Open(fullpath)
Set xlSheet = xlWrk.Sheets(1)
Dim cnt1 As Long
cnt1 = 1 + cnt2
xlSheet.PageSetup.FirstPageNumber = cnt1
xlSheet.PageSetup.CenterFooter = “Page &P”
xlSheet.PrintOut
HorizBreaks = xlSheet.HPageBreaks.Count
HPages = HorizBreaks + 1
VertBreaks = xlSheet.VPageBreaks.Count
VPages = VertBreaks + 1
numpages = HPages * VPages
cnt2 = cnt2 + numpages
xlWrk.Close False
Next i
MsgBox (“Done printing.”)
End If
Exit Sub
ErrHandler:
”error handling code
Resume Next
End Sub



Sub Button4_Click()
Dim strButtonCaption As String
Dim strDialogTitle As String
Dim strAttachment As String
Dim varItem As Variant
strButtonCaption = “BrowseBTN”
strDialogTitle = “Upload”
With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add “All Files”, “*.*” ‘Allow ALL File types
End With
‘The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False
.ButtonName = strButtonCaption
.InitialFileName = vbNullString
.InitialView = msoFileDialogViewDetails ‘Detailed View
.Title = strDialogTitle
If .Show Then
For Each varItem In .SelectedItems
AllowMultiSelect = False
strAttachment = varItem
ActiveSheet.Range(“D6″) = ExtractPathName(strAttachment)
Next varItem
End If
End With
End Sub



Function ExtractPathName(filespec) As String
‘ Returns the path from a filespec
Dim x As Variant
x = Split(filespec, Application.PathSeparator)
ReDim Preserve x(0 To UBound(x) – 1)
ExtractPathName = Join(x, Application.PathSeparator) & _
Application.PathSeparator
End Function