Good day guys, im just wondering if there is a notes, functions or code related to print, i just want to come up with a good hardcopy of the information every time the employer request it. my source is adodc1 or datagrid

yes sir, i have actually gathered code

Private Sub cmdPrint_Click()

'' declaring the borders to be used in excel
Dim myBorders() As Variant, item As Variant
myBorders = Array(xlEdgeLeft, _
xlEdgeTop, _
xlEdgeBottom, _
xlEdgeRight, _
xlInsideVertical)

Set AppXls = CreateObject("Excel.Application")
Set objwb = AppXls.Workbooks.Add

objwb.Worksheets(1).Delete
objwb.Worksheets(2).Delete
Set objws = objwb.Worksheets.Add
objwb.Worksheets(2).Delete

'' Formatting the title in excel
Range("A1:J1").Merge
Range("A1:J1").Value = "COMPANY SERVICES, INC."
Range("A2:J2").Merge
Range("A2:J2").Value = "Data Employee Log"
Range("A1:J2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Size = 15

Adodc1.RecordSource = "Select ID, permission, empName, empNumber, empPosition, cTime, cDate, cStatus, cDay, dDestination, dKilometers from DTRpersonnel order by ID desc"
Adodc1.Refresh

currenttime = Format(Now, "Hh:Nn:ss AM/PM")
currentdate = Format$(Now, "mmmm dd yyyy")

If Adodc1.Recordset.RecordCount <> 0 Then
Cells(4, 1).Value = "Time printed: " & currenttime
Cells(5, 1).Value = "Date printed: " & currentdate

Range("A4:J7").Select
Selection.HorizontalAlignment = xlLeft
Selection.Font.Bold = True
Selection.Font.Size = 18

Range("B7").ColumnWidth = 21
Range("B7").Value = "Employee Number"

Range("A7").ColumnWidth = 19
Range("A7").Value = "Employee Name"

Range("C7").ColumnWidth = 19
Range("C7").Value = "Position"

Range("D7").ColumnWidth = 15
Range("D7").Value = "Scan Time"

Range("E7").ColumnWidth = 19
Range("E7").Value = "Scan Date"

Range("F7").ColumnWidth = 14
Range("F7").Value = "Scan Status"

Range("G7").ColumnWidth = 13
Range("G7").Value = "Scan Day"

Range("H7").ColumnWidth = 22
Range("H7").Value = "Permission"

Range("I7").ColumnWidth = 18
Range("I7").Value = "Destination"

Range("J7").ColumnWidth = 15
Range("J7").Value = "Kilometers"

Adodc1.RecordSource = "Select ID, permission, empName, empNumber, empPosition, cTime, cDate, cStatus, cDay, dDestination, dKilometers from DTRpersonnel order by ID desc"
Adodc1.Refresh
Dim ctr As String
ctr = Adodc1.Recordset.RecordCount

Adodc1.Recordset.MoveFirst
For i = 1 To Adodc1.Recordset.RecordCount
'' 8 = number of row, 1 = number of column
    Cells(i + 8, 1) = Adodc1.Recordset.Fields("empName").Value
    Cells(i + 8, 2) = Adodc1.Recordset.Fields("empNumber").Value
    Cells(i + 8, 3) = Adodc1.Recordset.Fields("empPosition").Value
    Cells(i + 8, 4) = Adodc1.Recordset.Fields("cTime").Value
    Cells(i + 8, 5) = Adodc1.Recordset.Fields("cDate").Value
    Cells(i + 8, 6) = Adodc1.Recordset.Fields("cStatus").Value
    Cells(i + 8, 7) = Adodc1.Recordset.Fields("cDay").Value
    Cells(i + 8, 8) = Adodc1.Recordset.Fields("permission").Value
    Cells(i + 8, 9) = Adodc1.Recordset.Fields("dDestination").Value
    Cells(i + 8, 10) = Adodc1.Recordset.Fields("dKilometers").Value
    Adodc1.Recordset.MoveNext
Next i

'' this is the range of printing from employeename down to kilometers
'' 8 = number of border line
Range("A7:J" & Val(ctr) + 8).Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Size = 12
Selection.Borders.LineStyle = 1

For Each item In myBorders
With Selection.Borders(item)
.LineStyle = xlContinuous
.Weight = 2
.ColorIndex = xlAutomatic
End With
Next item

With ActiveSheet.PageSetup
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = 1
End With

ctr = Adodc1.Recordset.RecordCount + 10

Cells(3 + Val(ctr), 1) = "Prepared by: ____________________________"
Cells(6 + Val(ctr), 1) = "Checked by: ____________________________"
Cells(9 + Val(ctr), 1) = "Approved by: ____________________________"

X = "A" & 1 + Val(ctr) & ":J" & Val(ctr) + 16
Range(X).Select
Selection.HorizontalAlignment = xlLeft
Selection.Font.Bold = True
Selection.Font.Size = 15

X = "A1" & ":J" & Val(ctr) + 15
Range(X).Select
Selection.PrintOut
Selection.Clear

Else
    MsgBox "Nothing to Print"

End If
end sub

but the problem with the code is it leaves the excel.exe open and unsave,
how can i save the excel and also close it after printing?

Edited 5 Years Ago by xtianenikkian: n/a

actually the above code is from my friends system

i've only tired to edit it one by one

i've used trial and error method in discovering what is the codes for

This article has been dead for over six months. Start a new discussion instead.