VB.Net and Google docs - Visual Basic Discussion Boards

Drno Strz18-Sep-21 8:07

18-Sep-21 8:07

Hallo,
can I ask a question about agilitypack here?
From this website
https://update.kbv.de/ita-update/
I need a listing of the directories and files (ListDirectoryDetails under FTP). From this I would like to make a TreeView. This is fully programmed in C# and has been populated with FTP up to now. I am missing the file list on this website with HTTP. Is there a code snippet somewhere for this problem with the agilitypack?

Best regards
Norbert

Re: ListDirectoryDetails

RedDk18-Sep-21 13:55

RedDk

18-Sep-21 13:55

Perhaps these might be of use:
"ListDirectoryDetails"@cp[^]

Re: ListDirectoryDetails

Drno Strz18-Sep-21 21:33

Drno Strz

18-Sep-21 21:33

Thanks. With FTP everthing has gone very well so far. But now the server has been switched off and access is only possible via HTTP(s). Please visit this website Index of /ita-update
How do I get the directories and file links for a offline TreeView with the HtmlAgilityPack? There does not seem to be any other way with Vb.Net

Best regards
Norbert

Re: ListDirectoryDetails

Mycroft Holmes19-Sep-21 12:10

Mycroft Holmes

19-Sep-21 12:10

So the server admin turned off FTP and I don't think you can get the server directories via HTTPS. Unless you install a tool on the server with higher permissions that will supply the directories to the browser (HTTPS) you are probably not going to be able to do what you want.

Caveat - I have limited knowledge of this area so a better answer may come along.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: ListDirectoryDetails

Dave Kreskowiak19-Sep-21 12:39

Dave Kreskowiak

19-Sep-21 12:39

HTTP won't give you a folder structure. You have to get the root page, parse it for any subdirectories (if available), then make another request for each directory, building out a data structure as you go.

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

building an IT helpdesk tool

Mustafa M. Al-Nassah8-Sep-21 10:17

Mustafa M. Al-Nassah

8-Sep-21 10:17

Hello
Im IT manager in small company and
I have around 100 user, im building an IT helpdesk tool and i thinking to add more features than add/search/edit/tickets
I need something so useful like run a service directly from my tool.

Every time to close a ticket, i connect to user pc and if i need to edit something, i've to using the local administrator user & password
Any idea to skip this process (like single sign from my pc)

Simthing like push a packet to the target pc to run the cmd or explorer++ with using (my credentials) the local admin credentials

Note:
I've the local admin user & password
(All users working with Microsoft account)
I've access on Azure Active Directory admin center.

Re: building an IT helpdesk tool

CHill6016-Sep-21 22:11

CHill60

16-Sep-21 22:11

We used to grant admin rights to our support staff on their normal creds (hardly secure) and then use Windows Sysinternals - Windows Sysinternals | Microsoft Docs[^] for much of our remote support work.

Quite how this is relevant to a "Visual Basic" forum however ... beyond me

VB.Net and Google docs

Malcolm Bacchus6-Sep-21 3:31

Malcolm Bacchus

6-Sep-21 3:31

I wondered if anybody had an examples of/lessons on creating a google doc in .Net using Visual Basic. I understand you use documents.create in the Google API and then use documents.batchUpdate to update the blank document with all the formatting and text but I am having difficulty understanding how exactly you create the set of updates.

Basically we have an existing program written in vb.net which, amongst a lot of other things, creates (from a number of sources) tailored MS Word documents. However the company would like to modify the module which does this so that it creates a Google doc rather than an an MC Word document. This would be my first foray into the Google API and any pointers would be gratefully appreciated.

(PS, Yes I know it might be better to do it in another language, but that would be a major re-write of the whole thing rather than just a module and there are not the resources for that at present.)

Re: VB.Net and Google docs

Richard MacCutchan6-Sep-21 5:47

Richard MacCutchan

6-Sep-21 5:47

The best place to find the answers is the Google Docs API | Google Developers[^].

Need help with this question

Member 153482515-Sep-21 22:18

Member 15348251

5-Sep-21 22:18

Hey everybody I have this question in visual basic which I need help with which is

create an array (26) and then use for loop to:

1. assign to its elements the English letters from A to Z.

2. print the letters on a Message boxes.

If any body know how to solve it the right way please help because it keeps giving me errors and I need to turn it as an assignment fast .
Thanks in advance.

Re: Need help with this question

Richard MacCutchan5-Sep-21 22:33

Richard MacCutchan

5-Sep-21 22:33

Member 15348251 wrote:
it keeps giving me errors

Unless you show your code and the complete details of the errors, it is impossible for anyone to help.

Re: Need help with this question

Member 153482515-Sep-21 22:40

Member 15348251

5-Sep-21 22:40

The thing is I'm new to visual basic because it's a mandatory requirement to graduate in our university so you can say I'm a beginner in it . So can you help in solving it for me if you may.

Re: Need help with this question

Richard MacCutchan5-Sep-21 22:45

Richard MacCutchan

5-Sep-21 22:45

As I said, without the details of the code and the errors we cannot begin to guess what the problem is. And if you thought that someone here would write your assignment for you, I am afraid you are in the wrong place.

Re: Need help with this question

Member 153482515-Sep-21 23:55

Member 15348251

5-Sep-21 23:55

As I said I'm a beginner in this subject and I found two codes that might apply to this mater
The first one is :
Dim L(26) as string
Dim X as integer
For X= A to Z
Msgbox (L(X))
Next
* but it keeps saying that A and Z are not declared

The second one is :
Public
Dim A as integer
..............................
Dim L(26) as string
For A=65 to 90
Msgbox(chr(A))
Next

** I think the second one is the proper one . But if there is a thing that I can do to make it better or if you can explain how to write the code the right way I will be thankful

Re: Need help with this question

Richard MacCutchan6-Sep-21 0:25

Richard MacCutchan

6-Sep-21 0:25

In the first case you have declared an array of integers, but you are supposed to be using characters. if the second one works then that looks fine.

Re: Need help with this question

Member 153482516-Sep-21 0:59

Member 15348251

6-Sep-21 0:59

Ok nice is there anything I can do to make the second code better or any advice about it like if you were to write it differently what would you add to it in your point of view.

Re: Need help with this question

Richard MacCutchan6-Sep-21 5:44

Richard MacCutchan

6-Sep-21 5:44

I cannot really judge, as I do not know what your teacher is expecting from you.

Re: Need help with this question

Richard MacCutchan6-Sep-21 22:28

Richard MacCutchan

6-Sep-21 22:28

My apologies, but I had to go somewhere at short notice yesterday. Having had some time to review my answers I suggest the following code:

Dim letters(26) as Char
For k As Integer = 0 To 25
    letters(k) = Chr(Asc("A") + k) ' add the index value to the value of character A, and convert back to a character
    Console.Write("{0}", letters(k))
Next
Console.WriteLine("")

I have used the Console.Write method merely as a testing tool. You can replace it with: Msgbox(letters(k)) and it will still work.

Re: Need help with this question

OriginalGriff6-Sep-21 19:44

OriginalGriff

6-Sep-21 19:44

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!

Text box auto takes serial no when items select form combo box

GOBIND KUMAR SHARMA31-Aug-21 23:48

GOBIND KUMAR SHARMA

31-Aug-21 23:48

i built one program but facing problem what i try to do is when i select items from combobox eg. Primary vertical(PV), secondary vertical(SV) etc. next textbox will auto take serial no. like PV-01/today_date, next entry will be PV-02/today_date and so on and when next day again i try to add entries serial no. started from PV-01/.....
I AM USING VB 2010 with access database for this.

dont know what to do i am new in vb

[REPOST] Text box auto takes serial no when items select form combo box

Richard Deeming1-Sep-21 0:06

Richard Deeming

1-Sep-21 0:06

You have already posted this in QA:
Text box auto takes serial no when items select form combo box[^]

You didn't provide enough information for anyone to help you. Reposting exactly the same thing isn't going to change that.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

What is the problem here?

Member 1495490331-Aug-21 9:59

Member 14954903

31-Aug-21 9:59

So if i copy something, it goes directly into "Textbox2" but sometimes the same text as "Textbox1" goes into "Textbox2" regardless of it not being "allowed". (see code below)

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Dim clip As String = Clipboard.GetText
    If clip.StartsWith(TextBox1.Text) Then

    Else
        TextBox2.Text = Clipboard.GetText
    End If
End Sub

The timer has an interval of 100ms (i think my pc isnt to slow to handle it) but this also happens if the timer has a interval of 3000ms or anything higher (it just happens later). What is the issue here?

Re: What is the problem here?

Richard MacCutchan31-Aug-21 21:57

Richard MacCutchan

31-Aug-21 21:57

Member 14954903 wrote:
What is the issue here?

Impossible to tell. It is possible that between the moment your code reads the clipboard and the next statement, the contents of Textbox1 changes. You need to add some debug code to try and find out exactly what data exists in each place.

Re: What is the problem here?

Richard Deeming31-Aug-21 22:50

Richard Deeming

31-Aug-21 22:50

He's a spammer:
Spammer from moderation queue (Member 14954903)[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

How to faster process export to excel row by row with excel formating

Anton Setiawan 202124-Aug-21 18:33

Anton Setiawan 2021

24-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

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