Click here to Skip to main content
15,442,081 members
Home / Discussions / Visual Basic
   

Visual Basic

 
GeneralRe: Need help with this question Pin
Richard MacCutchan6-Sep-21 22:28
mveRichard MacCutchan6-Sep-21 22:28 
AnswerRe: Need help with this question Pin
OriginalGriff6-Sep-21 19:44
mveOriginalGriff6-Sep-21 19:44 
QuestionText box auto takes serial no when items select form combo box Pin
GOBIND KUMAR SHARMA31-Aug-21 23:48
MemberGOBIND KUMAR SHARMA31-Aug-21 23:48 
Rant[REPOST] Text box auto takes serial no when items select form combo box Pin
Richard Deeming1-Sep-21 0:06
mveRichard Deeming1-Sep-21 0:06 
QuestionWhat is the problem here? Pin
Member 1495490331-Aug-21 9:59
MemberMember 1495490331-Aug-21 9:59 
AnswerRe: What is the problem here? Pin
Richard MacCutchan31-Aug-21 21:57
mveRichard MacCutchan31-Aug-21 21:57 
GeneralRe: What is the problem here? Pin
Richard Deeming31-Aug-21 22:50
mveRichard Deeming31-Aug-21 22:50 
QuestionHow to faster process export to excel row by row with excel formating Pin
Anton Setiawan 202124-Aug-21 18:33
MemberAnton Setiawan 202124-Aug-21 18:33 
Hi there, please help me how to speed up, i'm new in VB.NET programming, we have code to calculate salary and generate excel file with custom format (field, fonts, etc), the process is takes long time (up to 3 hours for 2000 record).

The code asf :
Private Sub tsbExcel_Click(sender As Object, e As EventArgs) Handles tsbExcel.Click
       If MessageBox.Show("Continue Proses...", "Rekap Detail All", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
           GenPayrollAll()
       End If
   End Sub

   Private Sub GenPayrollAll()
       'Get variabel
       Dim vabsDateBgn As Date = dtabsDateBgn.Value
       Dim vabsDateEnd As Date = dtabsDateEnd.Value

       'Validation
       If chkPeriod(vabsDateBgn) = False Then
           MessageBox.Show("No record available", "Attention", MessageBoxButtons.OK, MessageBoxIcon.Information)
           Return
       End If
       DayCount()
       SendKeys.Send("{TAB}")
       lblProgress.Text = "Collecting data..."
       Application.DoEvents() ' Keep App Responsive

       'Get header data
       SQLstr = "SELECT t_Absent_h.absID,t_Absent_h.absDateBgn,t_Absent_h.absDateEnd,m_Customers.cstName,m_CustomersLocation.cloContLocation,t_Absent_h.IdEmp,m_Employee.FName+' '+m_Employee.LName AS NmEmp, m_othFunctAllowance.ofaName, " & _
                "t_Absent_h.absGajiPokok,t_Absent_h.absFunctionalAllow,t_Absent_h.absSecurityAllow,t_Absent_h.absTunjKehadiran,t_Absent_h.absMealAllow,t_Absent_h.absOthersAllow,t_Absent_h.absLmbBiasa,t_Absent_h.absLmbNasional,t_Absent_h.absLmbUser, " & _
                "t_Absent_h.absPotJamsostek,t_Absent_h.absPotJamsostekKes,t_Absent_h.absPotSeragam,t_Absent_h.absPotPelatihan,t_Absent_h.absPotInvDiklat,t_Absent_h.absPotAbsen,t_Absent_h.absOthersReduction, " & _
                "t_Absent_h.absPromosi,t_Absent_h.absDemosi,t_Absent_h.absTotal,t_Absent_h.abs21_PPh,t_Absent_h.absTotal+t_Absent_h.abs21_pph AS absGajiKotor,t_Absent_h.absKeterangan,m_Employee.empAccBank,m_Employee.empAccNo,m_Employee.empDPermanent,m_Employee.empDResign " & _
                "FROM t_Absent_h " & _
                "INNER JOIN m_Employee ON m_Employee.IdEmp=t_Absent_h.IdEmp " & _
                "INNER JOIN m_Customers ON m_Customers.cstID=t_Absent_h.cstID " & _
                "INNER JOIN m_CustomersLocation ON m_CustomersLocation.cloID=t_Absent_h.cloID " & _
                "LEFT JOIN m_othFunctAllowance ON m_othFunctAllowance.ofaID=t_Absent_h.ofaID " & _
                "WHERE CONVERT(VARCHAR(10),t_Absent_h.absDateBgn,120)=@absDateBgn " & _
                "ORDER BY cstName ASC,cloContLocation ASC,IdEmp ASC"
       'Set command
       DBcmd = New SqlCommand(SQLstr, DBcon)
       DBcmd.CommandType = CommandType.Text
       'Set parameter
       DBcmd.Parameters.Add("@absDateBgn", SqlDbType.Date).Value = Format(vabsDateBgn, "yyyy-MM-dd")
       'Set DataAdapter
       DAtbl = New SqlDataAdapter(DBcmd)
       DAtbl.SelectCommand = DBcmd
       'Execute DataAdapter
       If (DBcon.State <> ConnectionState.Closed) Then : DBcon.Close() : End If
       DBcon.Open()
       If DStbl.Tables.Contains("tmpPayrollSlip") Then : DStbl.Tables.Remove("tmpPayrollSlip") : End If
       DAtbl.Fill(DStbl, "tmpPayrollSlip")
       DBcmd.Dispose()
       DBcon.Close()

       'Add column to dataset
       Dim dcH As New DataColumn("sumH", System.Type.GetType("System.Int32"))
       Dim dcO As New DataColumn("sumO", System.Type.GetType("System.Int32"))
       Dim dcC As New DataColumn("sumC", System.Type.GetType("System.Int32"))
       Dim dcA As New DataColumn("sumA", System.Type.GetType("System.Int32"))
       Dim dcDS As New DataColumn("sumDS", System.Type.GetType("System.Int32"))
       Dim dcTS As New DataColumn("sumTS", System.Type.GetType("System.Int32"))
       Dim dcI0 As New DataColumn("sumI0", System.Type.GetType("System.Int32"))
       Dim dcI1 As New DataColumn("sumI1", System.Type.GetType("System.Int32"))
       Dim dcLB As New DataColumn("sumLB", System.Type.GetType("System.Int32"))
       Dim dcLN As New DataColumn("sumLN", System.Type.GetType("System.Int32"))
       Dim dcLU As New DataColumn("sumLU", System.Type.GetType("System.Int32"))
       Dim dcX As New DataColumn("sumX", System.Type.GetType("System.Int32"))
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcH)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcO)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcC)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcA)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcDS)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcTS)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcI0)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcI1)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLB)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLN)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLU)
       DStbl.Tables("tmpPayrollSlip").Columns.Add(dcX)
       DStbl.AcceptChanges()

       'Finding summary detail absent
       Dim countMax As Integer = DStbl.Tables("tmpPayrollSlip").Rows.Count - 1
       If countMax < 0 Then
           MessageBox.Show("No record available", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
           Return
       End If
       ProgressBar1.Maximum = countMax + 1
       Dim vabsID As Integer = 0
       If (DBcon.State <> ConnectionState.Closed) Then : DBcon.Close() : End If
       DBcon.Open()
       For i = 0 To countMax
           vabsID = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absID")
           SQLstr = "SELECT absAbsent,COUNT(absAbsent) AS absAbsentCount,SUM(absOvt) AS absOvtSum,SUM(absAdd) AS absAddSum FROM t_Absent_d WHERE absID=@absID GROUP BY absAbsent"
           'Set command
           DBcmd = New SqlCommand(SQLstr, DBcon)
           DBcmd.CommandType = CommandType.Text
           'Set parameter
           DBcmd.Parameters.Add("@absID", SqlDbType.Int).Value = vabsID
           'Execute reader
           DRtbl = DBcmd.ExecuteReader
           If DRtbl.HasRows = True Then
               Dim vabsAbsent As String = ""
               Dim vabsAbsentCount As Integer = 0
               Dim vabsOvtSum As Integer = 0
               Dim vabsAddSum As Integer = 0
               While DRtbl.Read()
                   vabsAbsent = VB.Trim(DRtbl("absAbsent"))
                   vabsAbsentCount = VB.Trim(DRtbl("absAbsentCount"))
                   vabsOvtSum = VB.Trim(DRtbl("absOvtSum"))
                   vabsAddSum = VB.Trim(DRtbl("absAddSum"))
                   Select Case vabsAbsent
                       Case Is = "H"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumH") = vabsAbsentCount
                       Case Is = "O"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumO") = vabsAbsentCount
                       Case Is = "C"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumC") = vabsAbsentCount
                       Case Is = "A"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumA") = vabsAbsentCount
                       Case Is = "DS"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumDS") = vabsAbsentCount
                       Case Is = "TS"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumTS") = vabsAbsentCount
                       Case Is = "I0"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI0") = vabsAbsentCount
                       Case Is = "I1"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI1") = vabsAbsentCount
                       Case Is = "LB"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLB") = vabsOvtSum + vabsAddSum
                       Case Is = "LN"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLN") = vabsOvtSum + vabsAddSum
                       Case Is = "LU"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLU") = vabsOvtSum + vabsAddSum
                       Case Is = "X"
                           DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumX") = vabsOvtSum + vabsAddSum
                   End Select
               End While
           End If
           DRtbl.Close()
           DBcmd.Dispose()
           vabsID = 0
           ProgressBar1.Value = (i + 1)
           lblProgress.Text = (i + 1).ToString("#,##0") + " of " + (countMax + 1).ToString("#,##0") + " Write summary to excel"
       Next
       DBcon.Close()
       Delay(0)
       lblProgress.Text = "..."
       ProgressBar1.Value = 0

       '=======================================================
       'Export to excel file
       '=======================================================
       'Create dimension for excel applications
       Dim oXL As New Excel.Application
       Dim oWB As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim oRange As Excel.Range

       'Start Excel and get Application object.
       oXL = CreateObject("Excel.Application")
       oXL.Visible = False
       oXL.SheetsInNewWorkbook = 1

       'Get a new workbook and create FirstSheet
       Dim SName As String = "PayrollRecap"
       oWB = oXL.Workbooks.Add
       oWB.Sheets("Sheet1").Name = SName

       'Select active sheet
       Dim iRowH As Integer = 4
       Dim iColH As Integer = 2
       oWB.Sheets(SName).Select()
       oWB.Sheets(SName).Cells(iRowH, iColH - 1).Value = "SITE dan LOKASI"
       oWB.Sheets(SName).Cells(iRowH, iColH - 0).Value = "No"
       oWB.Sheets(SName).Cells(iRowH, iColH + 1).Value = "NIP"
       oWB.Sheets(SName).Cells(iRowH, iColH + 2).Value = "NAMA"
       oWB.Sheets(SName).Cells(iRowH, iColH + 3).Value = "Jabatan"

       oWB.Sheets(SName).Cells(iRowH, iColH + 4).Value = "Gaji Pokok"
       oWB.Sheets(SName).Cells(iRowH, iColH + 5).Value = "Jabatan"
       oWB.Sheets(SName).Cells(iRowH, iColH + 6).Value = "Security"
       oWB.Sheets(SName).Cells(iRowH, iColH + 7).Value = "Kehadiran"
       oWB.Sheets(SName).Cells(iRowH, iColH + 8).Value = "U.Makan"
       oWB.Sheets(SName).Cells(iRowH, iColH + 9).Value = "Lain2"

       oWB.Sheets(SName).Cells(iRowH, iColH + 10).Value = "Biasa"
       oWB.Sheets(SName).Cells(iRowH, iColH + 11).Value = "Nasional"
       oWB.Sheets(SName).Cells(iRowH, iColH + 12).Value = "P/User"

       oWB.Sheets(SName).Cells(iRowH, iColH + 13).Value = "BPJS-TK"
       oWB.Sheets(SName).Cells(iRowH, iColH + 14).Value = "BPJS-KES"
       oWB.Sheets(SName).Cells(iRowH, iColH + 15).Value = "Seragam"
       oWB.Sheets(SName).Cells(iRowH, iColH + 16).Value = "Pelatihan"
       oWB.Sheets(SName).Cells(iRowH, iColH + 17).Value = "Investasi Diklat"
       oWB.Sheets(SName).Cells(iRowH, iColH + 18).Value = "Absen"
       oWB.Sheets(SName).Cells(iRowH, iColH + 19).Value = "Lain2"

       oWB.Sheets(SName).Cells(iRowH, iColH + 20).Value = "Penyesuaian (+)"
       oWB.Sheets(SName).Cells(iRowH, iColH + 21).Value = "Penyesuaian (-)"
       oWB.Sheets(SName).Cells(iRowH, iColH + 22).Value = "Gaji Sblm PPh"
       oWB.Sheets(SName).Cells(iRowH, iColH + 23).Value = "PPh 21"
       oWB.Sheets(SName).Cells(iRowH, iColH + 24).Value = "Gaji Stlh PPh"
       oWB.Sheets(SName).Cells(iRowH, iColH + 25).Value = "Total Gaji"

       oWB.Sheets(SName).Cells(iRowH, iColH + 26).Value = "H"
       oWB.Sheets(SName).Cells(iRowH, iColH + 27).Value = "O"
       oWB.Sheets(SName).Cells(iRowH, iColH + 28).Value = "C"
       oWB.Sheets(SName).Cells(iRowH, iColH + 29).Value = "A"
       oWB.Sheets(SName).Cells(iRowH, iColH + 30).Value = "DS"
       oWB.Sheets(SName).Cells(iRowH, iColH + 31).Value = "TS"
       oWB.Sheets(SName).Cells(iRowH, iColH + 32).Value = "I0"
       oWB.Sheets(SName).Cells(iRowH, iColH + 33).Value = "I1"
       oWB.Sheets(SName).Cells(iRowH, iColH + 34).Value = "LB"
       oWB.Sheets(SName).Cells(iRowH, iColH + 35).Value = "LN"
       oWB.Sheets(SName).Cells(iRowH, iColH + 36).Value = "LU"
       oWB.Sheets(SName).Cells(iRowH, iColH + 37).Value = "X"
       oWB.Sheets(SName).Cells(iRowH, iColH + 38).Value = "KETERANGAN"
       oWB.Sheets(SName).Cells(iRowH, iColH + 39).Value = "Nama Bank"
       oWB.Sheets(SName).Cells(iRowH, iColH + 40).Value = "No.Rekening"
       oWB.Sheets(SName).Cells(iRowH, iColH + 41).Value = "Tgl.Masuk"
       oWB.Sheets(SName).Cells(iRowH, iColH + 42).Value = "Tgl.Keluar"

       countMax = DStbl.Tables("tmpPayrollSlip").Rows.Count - 1
       ProgressBar1.Maximum = countMax + 1
       Dim iRowD As Integer = 5
       Dim iColD As Integer = 2
       Dim vNoRec As Integer = 0
       Dim vcstName As String = DStbl.Tables("tmpPayrollSlip").Rows(0).Item("cstName")
       Dim vcloContLocation As String = DStbl.Tables("tmpPayrollSlip").Rows(0).Item("cloContLocation")

       'Variable for Perhitungan summary
       Dim vGajiPokok As Decimal = 0 : Dim xGajiPokok As Decimal = 0
       Dim vFunctionalAllow As Decimal = 0 : Dim xFunctionalAllow As Decimal = 0
       Dim vSecurityAllow As Decimal = 0 : Dim xSecurityAllow As Decimal = 0
       Dim vTunjKehadiran As Decimal = 0 : Dim xTunjKehadiran As Decimal = 0
       Dim vMealAllow As Decimal = 0 : Dim xMealAllow As Decimal = 0
       Dim vOthersAllow As Decimal = 0 : Dim xOthersAllow As Decimal = 0
       Dim vLmbBiasa As Decimal = 0 : Dim xLmbBiasa As Decimal = 0
       Dim vLmbNasional As Decimal = 0 : Dim xLmbNasional As Decimal = 0
       Dim vLmbUser As Decimal = 0 : Dim xLmbUser As Decimal = 0
       Dim vPotJamsostek As Decimal = 0 : Dim xPotJamsostek As Decimal = 0
       Dim vPotJamsostekKes As Decimal = 0 : Dim xPotJamsostekKes As Decimal = 0
       Dim vPotSeragam As Decimal = 0 : Dim xPotSeragam As Decimal = 0
       Dim vPotPelatihan As Decimal = 0 : Dim xPotPelatihan As Decimal = 0
       Dim vPotInvDiklat As Decimal = 0 : Dim xPotInvDiklat As Decimal = 0
       Dim vPotAbsen As Decimal = 0 : Dim xPotAbsen As Decimal = 0
       Dim vOthersReduction As Decimal = 0 : Dim xOthersReduction As Decimal = 0
       Dim vPromosi As Decimal = 0 : Dim xPromosi As Decimal = 0
       Dim vDemosi As Decimal = 0 : Dim xDemosi As Decimal = 0
       Dim vabs21_pph As Decimal = 0 : Dim xabs21_pph As Decimal = 0
       Dim vabsGajiKotor As Decimal = 0 : Dim xabsGajiKotor As Decimal = 0
       Dim vTotal As Decimal = 0 : Dim xTotal As Decimal = 0
       Dim vTotalGaji As Decimal = 0 : Dim xTotalGaji As Decimal = 0
       For i = 0 To countMax
           'Nomor Urut
           If DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName") & DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloContLocation") <> vcstName & vcloContLocation Then
               'Write sub total summary
               oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "SUB TOTAL : "
               oWB.Sheets(SName).Cells(iRowD, iColD + 4).VALUE = vGajiPokok
               oWB.Sheets(SName).Cells(iRowD, iColD + 5).VALUE = vFunctionalAllow
               oWB.Sheets(SName).Cells(iRowD, iColD + 6).VALUE = vSecurityAllow
               oWB.Sheets(SName).Cells(iRowD, iColD + 7).VALUE = vTunjKehadiran
               oWB.Sheets(SName).Cells(iRowD, iColD + 8).VALUE = vMealAllow
               oWB.Sheets(SName).Cells(iRowD, iColD + 9).VALUE = vOthersAllow
               oWB.Sheets(SName).Cells(iRowD, iColD + 10).VALUE = vLmbBiasa
               oWB.Sheets(SName).Cells(iRowD, iColD + 11).VALUE = vLmbNasional
               oWB.Sheets(SName).Cells(iRowD, iColD + 12).VALUE = vLmbUser
               oWB.Sheets(SName).Cells(iRowD, iColD + 13).VALUE = vPotJamsostek
               oWB.Sheets(SName).Cells(iRowD, iColD + 14).VALUE = vPotJamsostekKes
               oWB.Sheets(SName).Cells(iRowD, iColD + 15).VALUE = vPotSeragam
               oWB.Sheets(SName).Cells(iRowD, iColD + 16).VALUE = vPotPelatihan
               oWB.Sheets(SName).Cells(iRowD, iColD + 17).VALUE = vPotInvDiklat
               oWB.Sheets(SName).Cells(iRowD, iColD + 18).VALUE = vPotAbsen
               oWB.Sheets(SName).Cells(iRowD, iColD + 19).VALUE = vOthersReduction
               oWB.Sheets(SName).Cells(iRowD, iColD + 20).VALUE = vPromosi
               oWB.Sheets(SName).Cells(iRowD, iColD + 21).VALUE = vDemosi
               oWB.Sheets(SName).Cells(iRowD, iColD + 22).VALUE = vabsGajiKotor
               oWB.Sheets(SName).Cells(iRowD, iColD + 23).VALUE = vabs21_pph
               oWB.Sheets(SName).Cells(iRowD, iColD + 24).VALUE = vTotal
               oWB.Sheets(SName).Cells(iRowD, iColD + 25).VALUE = vTotalGaji
               'Format excel for summary
               oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
               oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
               oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True


               'Reset sub total
               vGajiPokok = 0 : vFunctionalAllow = 0 : vSecurityAllow = 0 : vTunjKehadiran = 0
               vMealAllow = 0 : vOthersAllow = 0 : vLmbBiasa = 0 : vLmbNasional = 0 : vLmbUser = 0
               vPotJamsostek = 0 : vPotJamsostekKes = 0 : vPotSeragam = 0 : vPotPelatihan = 0 : vPotInvDiklat = 0 : vPotAbsen = 0 : vOthersReduction = 0
               vPromosi = 0 : vDemosi = 0 : vTotal = 0 : vTotalGaji = 0 : vabsGajiKotor = 0 : vabs21_pph = 0
               'Add next row
               iRowD = iRowD + 2

               'Write customer name
               vcstName = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName")
               vcloContLocation = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloContLocation")
               vNoRec = 0
           End If
           vNoRec = vNoRec + 1
           'For summary sub total
           vGajiPokok = vGajiPokok + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
           vFunctionalAllow = vFunctionalAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
           vSecurityAllow = vSecurityAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
           vTunjKehadiran = vTunjKehadiran + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
           vMealAllow = vMealAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
           vOthersAllow = vOthersAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
           vLmbBiasa = vLmbBiasa + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
           vLmbNasional = vLmbNasional + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
           vLmbUser = vLmbUser + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")
           vPotJamsostek = vPotJamsostek + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
           vPotJamsostekKes = vPotJamsostekKes + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
           vPotSeragam = vPotSeragam + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
           vPotPelatihan = vPotPelatihan + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
           vPotInvDiklat = vPotInvDiklat + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
           vPotAbsen = vPotAbsen + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
           vOthersReduction = vOthersReduction + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")
           vPromosi = vPromosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
           vDemosi = vDemosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
           vabs21_pph = vabs21_pph + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_PPh")
           vabsGajiKotor = vabsGajiKotor + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
           vTotal = vTotal + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")

           'For summary total
           xGajiPokok = xGajiPokok + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
           xFunctionalAllow = xFunctionalAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
           xSecurityAllow = xSecurityAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
           xTunjKehadiran = xTunjKehadiran + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
           xMealAllow = xMealAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
           xOthersAllow = xOthersAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
           xLmbBiasa = xLmbBiasa + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
           xLmbNasional = xLmbNasional + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
           xLmbUser = xLmbUser + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")
           xPotJamsostek = xPotJamsostek + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
           xPotJamsostekKes = xPotJamsostekKes + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
           xPotSeragam = xPotSeragam + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
           xPotPelatihan = xPotPelatihan + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
           xPotInvDiklat = xPotInvDiklat + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
           xPotAbsen = xPotAbsen + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
           xOthersReduction = xOthersReduction + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")
           xPromosi = xPromosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
           xDemosi = xDemosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
           xabs21_pph = xabs21_pph + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_pph")
           xabsGajiKotor = xabsGajiKotor + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
           xTotal = xTotal + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")

           'Write to excel cell
           oWB.Sheets(SName).Cells(iRowD, iColD - 1).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName") & " - " & DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloCOntLocation")
           oWB.Sheets(SName).Cells(iRowD, iColD - 0).Value = vNoRec
           oWB.Sheets(SName).Cells(iRowD, iColD + 1).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("IdEmp")
           oWB.Sheets(SName).Cells(iRowD, iColD + 2).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("NmEmp")
           oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("ofaName")

           oWB.Sheets(SName).Cells(iRowD, iColD + 4).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
           oWB.Sheets(SName).Cells(iRowD, iColD + 5).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
           oWB.Sheets(SName).Cells(iRowD, iColD + 6).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
           oWB.Sheets(SName).Cells(iRowD, iColD + 7).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
           oWB.Sheets(SName).Cells(iRowD, iColD + 8).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
           oWB.Sheets(SName).Cells(iRowD, iColD + 9).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
           oWB.Sheets(SName).Cells(iRowD, iColD + 10).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
           oWB.Sheets(SName).Cells(iRowD, iColD + 11).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
           oWB.Sheets(SName).Cells(iRowD, iColD + 12).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")

           oWB.Sheets(SName).Cells(iRowD, iColD + 13).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
           oWB.Sheets(SName).Cells(iRowD, iColD + 14).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
           oWB.Sheets(SName).Cells(iRowD, iColD + 15).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
           oWB.Sheets(SName).Cells(iRowD, iColD + 16).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
           oWB.Sheets(SName).Cells(iRowD, iColD + 17).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
           oWB.Sheets(SName).Cells(iRowD, iColD + 18).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
           oWB.Sheets(SName).Cells(iRowD, iColD + 19).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")

           oWB.Sheets(SName).Cells(iRowD, iColD + 20).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
           oWB.Sheets(SName).Cells(iRowD, iColD + 21).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
           oWB.Sheets(SName).Cells(iRowD, iColD + 22).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
           oWB.Sheets(SName).Cells(iRowD, iColD + 23).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_pph")
           oWB.Sheets(SName).Cells(iRowD, iColD + 24).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")

           'Total gaji (where absTotal not minus)
           If DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal") > 0 Then
               vTotalGaji = vTotalGaji + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
               xTotalGaji = xTotalGaji + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
               oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
           Else
               vTotalGaji = vTotalGaji + 0
               xTotalGaji = xTotalGaji + 0
               oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = 0
           End If

           oWB.Sheets(SName).Cells(iRowD, iColD + 26).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumH")
           oWB.Sheets(SName).Cells(iRowD, iColD + 27).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumO")
           oWB.Sheets(SName).Cells(iRowD, iColD + 28).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumC")
           oWB.Sheets(SName).Cells(iRowD, iColD + 29).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumA")
           oWB.Sheets(SName).Cells(iRowD, iColD + 30).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumDS")
           oWB.Sheets(SName).Cells(iRowD, iColD + 31).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumTS")
           oWB.Sheets(SName).Cells(iRowD, iColD + 32).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI0")
           oWB.Sheets(SName).Cells(iRowD, iColD + 33).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI1")
           oWB.Sheets(SName).Cells(iRowD, iColD + 34).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLB")
           oWB.Sheets(SName).Cells(iRowD, iColD + 35).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLN")
           oWB.Sheets(SName).Cells(iRowD, iColD + 36).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLU")
           oWB.Sheets(SName).Cells(iRowD, iColD + 37).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumX")
           oWB.Sheets(SName).Cells(iRowD, iColD + 38).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absKeterangan")
           oWB.Sheets(SName).Cells(iRowD, iColD + 39).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empAccBank")
           oWB.Sheets(SName).Cells(iRowD, iColD + 40).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empAccNo")
           oWB.Sheets(SName).Cells(iRowD, iColD + 41).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empDPermanent")
           oWB.Sheets(SName).Cells(iRowD, iColD + 42).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empDResign")
           iRowD = iRowD + 1
           ProgressBar1.Value = (i + 1)
           lblProgress.Text = (i + 1).ToString("#,##0") + " of " + (countMax + 1).ToString("#,##0") + " Write detail to excel"
       Next
       'Write sub total summary for last customer name
       oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "SUB TOTAL : "
       oWB.Sheets(SName).Cells(iRowD, iColD + 4).VALUE = vGajiPokok
       oWB.Sheets(SName).Cells(iRowD, iColD + 5).VALUE = vFunctionalAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 6).VALUE = vSecurityAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 7).VALUE = vTunjKehadiran
       oWB.Sheets(SName).Cells(iRowD, iColD + 8).VALUE = vMealAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 9).VALUE = vOthersAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 10).VALUE = vLmbBiasa
       oWB.Sheets(SName).Cells(iRowD, iColD + 11).VALUE = vLmbNasional
       oWB.Sheets(SName).Cells(iRowD, iColD + 12).VALUE = vLmbUser
       oWB.Sheets(SName).Cells(iRowD, iColD + 13).VALUE = vPotJamsostek
       oWB.Sheets(SName).Cells(iRowD, iColD + 14).VALUE = vPotJamsostekKes
       oWB.Sheets(SName).Cells(iRowD, iColD + 15).VALUE = vPotSeragam
       oWB.Sheets(SName).Cells(iRowD, iColD + 16).VALUE = vPotPelatihan
       oWB.Sheets(SName).Cells(iRowD, iColD + 17).VALUE = vPotInvDiklat
       oWB.Sheets(SName).Cells(iRowD, iColD + 18).VALUE = vPotAbsen
       oWB.Sheets(SName).Cells(iRowD, iColD + 19).VALUE = vOthersReduction
       oWB.Sheets(SName).Cells(iRowD, iColD + 20).VALUE = vPromosi
       oWB.Sheets(SName).Cells(iRowD, iColD + 21).VALUE = vDemosi
       oWB.Sheets(SName).Cells(iRowD, iColD + 22).VALUE = vabsGajiKotor
       oWB.Sheets(SName).Cells(iRowD, iColD + 23).VALUE = vabs21_pph
       oWB.Sheets(SName).Cells(iRowD, iColD + 24).VALUE = vTotal
       oWB.Sheets(SName).Cells(iRowD, iColD + 25).VALUE = vTotalGaji
       'Format excel for summary
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True

       Delay(0)
       lblProgress.Text = "..."
       ProgressBar1.Value = 0

       'Write total summary
       iRowD = iRowD + 2
       oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "TOTAL : "
       oWB.Sheets(SName).Cells(iRowD, iColD + 4).Value = xGajiPokok
       oWB.Sheets(SName).Cells(iRowD, iColD + 5).Value = xFunctionalAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 6).Value = xSecurityAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 7).Value = xTunjKehadiran
       oWB.Sheets(SName).Cells(iRowD, iColD + 8).Value = xMealAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 9).Value = xOthersAllow
       oWB.Sheets(SName).Cells(iRowD, iColD + 10).Value = xLmbBiasa
       oWB.Sheets(SName).Cells(iRowD, iColD + 11).Value = xLmbNasional
       oWB.Sheets(SName).Cells(iRowD, iColD + 12).Value = xLmbUser
       oWB.Sheets(SName).Cells(iRowD, iColD + 13).Value = xPotJamsostek
       oWB.Sheets(SName).Cells(iRowD, iColD + 14).Value = xPotJamsostekKes
       oWB.Sheets(SName).Cells(iRowD, iColD + 15).Value = xPotSeragam
       oWB.Sheets(SName).Cells(iRowD, iColD + 16).Value = xPotPelatihan
       oWB.Sheets(SName).Cells(iRowD, iColD + 17).Value = xPotInvDiklat
       oWB.Sheets(SName).Cells(iRowD, iColD + 18).Value = xPotAbsen
       oWB.Sheets(SName).Cells(iRowD, iColD + 19).Value = xOthersReduction
       oWB.Sheets(SName).Cells(iRowD, iColD + 20).Value = xPromosi
       oWB.Sheets(SName).Cells(iRowD, iColD + 21).Value = xDemosi
       oWB.Sheets(SName).Cells(iRowD, iColD + 22).Value = xabsGajiKotor
       oWB.Sheets(SName).Cells(iRowD, iColD + 23).Value = xabs21_pph
       oWB.Sheets(SName).Cells(iRowD, iColD + 24).Value = xTotal
       oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = xTotalGaji

       'Format excel
       oWB.Sheets(SName).Range("A1:A2").Font.Size = 13
       oWB.Sheets(SName).Range("A1", "AR4").Font.Bold = True
       oWB.Sheets(SName).Range("F5", "AI" + CStr(iRowD - 1)).NumberFormat = "#,##0"
       oWB.Sheets(SName).Range("F" + CStr(iRowH), "AI" + CStr(iRowD - 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

       'Format excel for summary
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True

       'Autofit column when loop ending
       oWB.Sheets(SName).Columns.AutoFit()

       oWB.Sheets(SName).Cells(1, 1).Value = "PERHITUNGAN GAJI SECURITY"
       oWB.Sheets(SName).Cells(2, 1).Value = "Periode : " + Format(dtabsDateBgn.Value, "yyyy-MM-dd") + " s/d " + Format(dtabsDateEnd.Value, "yyyy-MM-dd")

       oWB.Sheets(SName).Range("A3", "A4").MergeCells = True 'Site and Location
       oWB.Sheets(SName).Cells(3, 1).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(3, 1).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("B3", "B4").MergeCells = True 'No
       oWB.Sheets(SName).Cells(3, 2).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(3, 2).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("C3", "C4").MergeCells = True 'NIP
       oWB.Sheets(SName).Cells(3, 3).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(3, 3).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("D3", "D4").MergeCells = True 'NAMA
       oWB.Sheets(SName).Cells(3, 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(3, 4).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("E3", "E4").MergeCells = True 'JABATAN
       oWB.Sheets(SName).Cells(3, 5).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(3, 5).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("F3", "F4").MergeCells = True 'GAJI POKOK
       oWB.Sheets(SName).Cells(3, 6).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 6).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Cells(3, 7).Value = "TUNJANGAN"
       oWB.Sheets(SName).Cells(3, 7).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Range("G3", "K3").MergeCells = True

       oWB.Sheets(SName).Cells(3, 12).Value = "LEMBUR"
       oWB.Sheets(SName).Cells(3, 12).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Range("L3", "N3").MergeCells = True

       oWB.Sheets(SName).Cells(3, 17).Value = "POTONGAN"
       oWB.Sheets(SName).Cells(3, 17).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Range("O3", "U3").MergeCells = True

       oWB.Sheets(SName).Cells(3, 29).Value = "KEHADIRAN"
       oWB.Sheets(SName).Cells(3, 29).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Range("AB3", "AM3").MergeCells = True

       oWB.Sheets(SName).Range("V3", "V4").MergeCells = True 'PROMOSI
       oWB.Sheets(SName).Cells(3, 22).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 22).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("W3", "W4").MergeCells = True ' DEMOSI
       oWB.Sheets(SName).Cells(3, 23).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 23).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("X3", "X4").MergeCells = True ' Gaji Kotor
       oWB.Sheets(SName).Cells(3, 24).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 24).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("Y3", "Y4").MergeCells = True ' PPH 21
       oWB.Sheets(SName).Cells(3, 24).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 24).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("Z3", "Z4").MergeCells = True ' TOTAL
       oWB.Sheets(SName).Cells(3, 25).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 25).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AA3", "AA4").MergeCells = True ' TOTAL GAJI
       oWB.Sheets(SName).Cells(3, 26).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
       oWB.Sheets(SName).Cells(3, 26).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AN3", "AN4").MergeCells = True ' Keterangan
       oWB.Sheets(SName).Cells(3, 37).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
       oWB.Sheets(SName).Cells(3, 37).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AO3", "AO4").MergeCells = True ' Nama Bank
       oWB.Sheets(SName).Cells(3, 38).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
       oWB.Sheets(SName).Cells(3, 38).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AP3", "AP4").MergeCells = True ' No.Rekening
       oWB.Sheets(SName).Cells(3, 39).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
       oWB.Sheets(SName).Cells(3, 39).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AQ3", "AQ4").MergeCells = True ' Tgl. Masuk
       oWB.Sheets(SName).Cells(3, 40).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
       oWB.Sheets(SName).Cells(3, 40).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       oWB.Sheets(SName).Range("AR3", "AR4").MergeCells = True ' Tgl. Keluar
       oWB.Sheets(SName).Cells(3, 41).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
       oWB.Sheets(SName).Cells(3, 41).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

       'Line formated
       oRange = oWB.Sheets(SName).Range("A3", "AR" + CStr(iRowD))
       'Border
       With oRange.Borders(Excel.XlBordersIndex.xlEdgeTop)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlMedium
           .ColorIndex = 1
       End With
       With oRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlThin
           .ColorIndex = 1
       End With
       With oRange.Borders(Excel.XlBordersIndex.xlEdgeLeft)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlThin
           .ColorIndex = 1
       End With
       With oRange.Borders(Excel.XlBordersIndex.xlEdgeRight)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlThin
           .ColorIndex = 1
       End With
       'Inside
       With oRange.Borders(Excel.XlBordersIndex.xlInsideVertical)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlThin
           .ColorIndex = 1
       End With
       With oRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
           .LineStyle = Excel.XlLineStyle.xlContinuous
           .Weight = Excel.XlBorderWeight.xlThin
           .ColorIndex = 1
       End With

       'Footer tertanda
       oWB.Sheets(SName).Cells(iRowD + 3, iColD + 1).Value = "Tangerang, " + Format(Now, "dd MMM yyyy")
       oWB.Sheets(SName).Cells(iRowD + 4, iColD + 1).Value = "Dibuat oleh,"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 1).Value = "Meylitha"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 1).Font.Underline = True
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 1).Value = "Payroll"
       oWB.Sheets(SName).Cells(iRowD + 10, iColD + 1).Value = "Tembusan Kasir"

       oWB.Sheets(SName).Cells(iRowD + 4, iColD + 4).Value = "Diperiksa,"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).Value = "Ernina"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).Font.Underline = True
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 4).Value = "HR Manager"
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

       oWB.Sheets(SName).Cells(iRowD + 4, iColD + 9).Value = "Diperiksa,"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).Value = "Sastra"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).Font.Underline = True
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 9).Value = "Wakil Direktur"
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 9).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

       oWB.Sheets(SName).Cells(iRowD + 4, iColD + 13).Value = "Disetujui,"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).Value = "Thomas Torana"
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).Font.Underline = True
       oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 13).Value = "Direktur"
       oWB.Sheets(SName).Cells(iRowD + 8, iColD + 13).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

       'Password for excel
       oWB.Sheets(SName).Protect("gbn@123", AllowSorting:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True)

       'Make sure Excel is visible and give the user control of Excel's lifetime.
       oXL.Visible = True
       oXL.UserControl = True

       'Release object  {oWB.Close():oXL.Quit()}
       oSheet = Nothing : releaseObject(oSheet)
       releaseObject(oWB)
       releaseObject(oXL)
   End Sub

   Private Sub Delay(ByVal DelayInSeconds As Integer)
       Dim TS As TimeSpan
       Dim TargetTime As DateTime = DateTime.Now.AddSeconds(DelayInSeconds)
       Do
           TS = TargetTime.Subtract(DateTime.Now)
           Application.DoEvents() ' Keep App Responsive
           System.Threading.Thread.Sleep(10) ' Reduce CPU Usage
       Loop While TS.TotalSeconds > 0
   End Sub

   Private Sub DelayMSC(ByVal DelayInMilliseconds As Integer)
       Dim timeOut As DateTime = Now.AddMilliseconds(DelayInMilliseconds)
       Do
           'Keep the app from freezing and allow Windows to continue processing.
           Application.DoEvents()
       Loop Until Now > timeOut 'Keep looping until the elasped time of milliseconds.
   End Sub

   Private Sub releaseObject(ByVal obj As Object)
       Try
           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
       Catch ex As Exception
       Finally
           obj = Nothing
           GC.Collect()
       End Try
   End Sub

AnswerRe: How to faster process export to excel row by row with excel formating Pin
Richard Deeming24-Aug-21 21:37
mveRichard Deeming24-Aug-21 21:37 
QuestionBlank/Black webcam on newer model laptop running webcam program in VB.net Pin
nethelp1116-Aug-21 16:47
professionalnethelp1116-Aug-21 16:47 
AnswerRe: Blank/Black webcam on newer model laptop running webcam program in VB.net Pin
Richard MacCutchan16-Aug-21 21:46
mveRichard MacCutchan16-Aug-21 21:46 
AnswerRe: Blank/Black webcam on newer model laptop running webcam program in VB.net Pin
Eddy Vluggen17-Aug-21 1:38
professionalEddy Vluggen17-Aug-21 1:38 
QuestionOdd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher6-Aug-21 4:02
MemberPeter R. Fletcher6-Aug-21 4:02 
AnswerRe: Odd Transparency behavior in VB.Net Panel Pin
Dave Kreskowiak6-Aug-21 5:02
mveDave Kreskowiak6-Aug-21 5:02 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher6-Aug-21 5:49
MemberPeter R. Fletcher6-Aug-21 5:49 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Dave Kreskowiak6-Aug-21 6:16
mveDave Kreskowiak6-Aug-21 6:16 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher6-Aug-21 6:31
MemberPeter R. Fletcher6-Aug-21 6:31 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher6-Aug-21 8:27
MemberPeter R. Fletcher6-Aug-21 8:27 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Dave Kreskowiak6-Aug-21 13:25
mveDave Kreskowiak6-Aug-21 13:25 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher7-Aug-21 3:45
MemberPeter R. Fletcher7-Aug-21 3:45 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Dave Kreskowiak7-Aug-21 5:14
mveDave Kreskowiak7-Aug-21 5:14 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher7-Aug-21 9:10
MemberPeter R. Fletcher7-Aug-21 9:10 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Dave Kreskowiak7-Aug-21 9:40
mveDave Kreskowiak7-Aug-21 9:40 
GeneralRe: Odd Transparency behavior in VB.Net Panel Pin
Peter R. Fletcher7-Aug-21 9:59
MemberPeter R. Fletcher7-Aug-21 9:59 
AnswerRe: Odd Transparency behavior in VB.Net Panel Pin
Gerry Schmitz6-Aug-21 6:52
mveGerry Schmitz6-Aug-21 6:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.