Category Archives: Excel

Get a Total Number of Pages for Excel Workbook

This Code Calculates the Total Number of pages to be printed on an excel workbook as well as each sheet num of pages:


Sub NumberOfPrintedPages()
    numpages = 0
    sht1 = 0
    sht2 = 0
    sht3 = 0
   
For i = 1 To Worksheets.Count
    Worksheets(i).DisplayAutomaticPageBreaks = True
    HorizBreaks = Worksheets(i).HPageBreaks.Count
    hpages = HorizBreaks + 1
    ‘VertBreaks = Worksheets(i).VPageBreaks.Count
    ‘VPages = VertBreaks + 1
    numpages = numpages + hpages
    Worksheets(i).DisplayAutomaticPageBreaks = False
   
    Select Case i
        Case 1
            sht1 = hpages
        Case 2
            sht2 = hpages
        Case 3
            sht3 = hpages
    End Select
   
Next
    ‘Cells(1, 1) = numpages
    MsgBox numpages & ” Pages in This Work Book” & vbCr & sht1 & ” Pages in Sheet1″ & vbCr & sht2 & ” Pages in Sheet2″ & vbCr & sht3 & ” Pages in Sheet3″
   
End Sub
Advertisements

VBA : Read From XLS File and Return Recordset based on a given Select Statement

Usage Sample:

    Dim oRs As New ADODB.Recordset
    If cmbGov.Text = “” Then
        Set oRs = getXLSSet(“Select * from [Area$]”)
    Else
        Set oRs = getXLSSet(“Select * from [Area$] where Gov = ‘” & cmbGov.Text & “‘”)
    End If
  
    Dim y As Integer
    For y = 1 To oRs.RecordCount
        frmhome.cmbArea.AddItem (oRs.Fields(0).Value)
        oRs.MoveNext
    Next


Function:
‘Function to read XLS Files based on a given select statement
Public Function getXLSSet(sourceStr As String) As Recordset
On Error GoTo Catch
    Dim oRs As ADODB.Recordset, oConn As ADODB.Connection, sConString As String, vValue As Variant
    
    sConString = “Provider= Microsoft.Jet.OLEDB.4.0;” & ” Data Source=” & sXLFile & “;Extended Properties=Excel 8.0;”
    Set oConn = New ADODB.Connection
    
    With oConn
        .CursorLocation = adUseClient
        .Open sConString
    End With
    
    Set oRs = New ADODB.Recordset
    
    With oRs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Source = sourceStr
        .ActiveConnection = oConn
        .Open
        .MoveFirst
    End With
    
    ”Work with the RS
        Set getXLSSet = oRs
    
    Exit Function
Catch:
    ‘MsgBox “Can’t Process Your Request” & vbCr _
    ‘& “Please Check you Levels or Excel File”
End Function