Visual Basic

Need help with this question

6-Sep-21 5:47

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

Member 153482515-Sep-21 22:18

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.

Richard MacCutchan5-Sep-21 22:33

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.

Member 153482515-Sep-21 22:40

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.

Richard MacCutchan5-Sep-21 22:45

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.

Member 153482515-Sep-21 23:55

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

Richard MacCutchan6-Sep-21 0:25

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.

Member 153482516-Sep-21 0:59

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.

Richard MacCutchan6-Sep-21 5:44

6-Sep-21 5:44

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

Richard MacCutchan6-Sep-21 22:28

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.

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

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!

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

Re: What is the problem here?

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.

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

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

Richard Deeming24-Aug-21 21:37

Richard Deeming

24-Aug-21 21:37

You need to profile your code to find out where the bottleneck is before you can start fixing it. Just dumping 669 lines of unexplained code in a message and expecting us to profile it for you isn't going to work - especially since we don't have access to your database. And since you can't be bothered to remove irrelevant code, such as the unused DelayMSC function, you clearly don't care about helping us to help you.

For a start, look at your queries. If your outer query returns 2000 rows, you will be executing 2001 queries to load the data, since you execute a separate query for every row in the "header data" query. There will almost certainly be a way to combine the queries so that you only need to execute one.

You have multiple calls to the Delay function, which will delay your code by more than 10ms each time you call it.

You're calling Application.DoEvents, which is always a code-smell. It indicates that you're doing work on the UI thread which should actually be performed on a background thread using a BackgroundWorker[^] or similar.

Also, Office Interop can be quite slow. You might have better luck using the Open XML SDK[^] to generate the file instead.

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

Blank/Black webcam on newer model laptop running webcam program in VB.net

nethelp1116-Aug-21 16:47

nethelp11

16-Aug-21 16:47

I'm using "avicap32.dll" for my program which found on this website. However the webcam preview only works on older laptop built in webcam but display black/blank on newer model laptop.

Anyone has the solution to this? Been cracking my head for few months trying to solve it.

Attach below with my code:

VB.NET

  1  Imports System.IO
  2  Imports System.Threading
  3  Imports System.Diagnostics
  4  Imports System.Runtime.InteropServices
  5  
  6  Public Class frmTest
  7    Private Structure BITMAPINFOHEADER
  8      Dim biSize As Integer
  9      Dim biWidth As Integer
 10      Dim biHeight As Integer
 11      Dim biPlanes As Short
 12      Dim biBitCount As Short
 13      Dim biCompression As Integer
 14      Dim biSizeImage As Integer
 15      Dim biXPelsPerMeter As Integer
 16      Dim biYPelsPerMeter As Integer
 17      Dim biClrUsed As Integer
 18      Dim biClrImportant As Integer
 19    End Structure
 20  
 21    Private Structure BITMAPINFO
 22      Dim bmiHeader As BITMAPINFOHEADER
 23      Dim bmiColors() As Integer
 24    End Structure
 25  
 26    Const WM_CAP As Short = &H400S
 27    Const WM_CAP_DRIVER_CONNECT As Integer = WM_CAP + 10
 28    Const WM_CAP_DRIVER_DISCONNECT As Integer = WM_CAP + 11
 29    Const WM_CAP_EDIT_COPY As Integer = WM_CAP + 30
 30    Const WM_CAP_SET_VIDEOFORMAT = WM_CAP + 45
 31    Const WM_CAP_SET_PREVIEW As Integer = WM_CAP + 50
 32    Const WM_CAP_SET_PREVIEWRATE As Integer = WM_CAP + 52
 33    Const WM_CAP_SET_SCALE As Integer = WM_CAP + 53
 34  
 35    Const WS_CHILD As Integer = &H40000000
 36    Const WS_VISIBLE As Integer = &H10000000
 37    Const SWP_NOMOVE As Short = &H2S
 38    Const SWP_NOZORDER As Short = &H4S
 39    Const HWND_BOTTOM As Short = 1
 40  
 41    Declare Function SetWindowPos Lib "user32" Alias "SetWindowPos" (ByVal hwnd As Integer, _
 42    ByVal hWndInsertAfter As Integer, ByVal x As Integer, ByVal y As Integer, _
 43    ByVal cx As Integer, ByVal cy As Integer, ByVal wFlags As Integer) As Integer
 44  
 45    Declare Function capCreateCaptureWindowA Lib "avicap32.dll" _
 46    (ByVal lpszWindowName As String, ByVal dwStyle As Integer, _
 47    ByVal x As Integer, ByVal y As Integer, ByVal nWidth As Integer, _
 48    ByVal nHeight As Short, ByVal hWndParent As Integer, _
 49    ByVal nID As Integer) As Integer
 50  
 51    Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
 52        (ByVal hwnd As Integer, ByVal wMsg As Integer, ByVal wParam As Integer, _
 53        <MarshalAs(UnmanagedType.AsAny)> ByVal lParam As Object) As Integer
 54  
 55    Declare Function DestroyWindow Lib "user32" (ByVal hndw As Integer) As Boolean
 56  
 57    Private iDevice As Integer = 0 ' Current device ID
 58    Private hHwnd As Integer '
 59  
 60    Private intSecurity As ENSECURITY
 61    Private m_strImage As String
 62    Private m_strFinger As String
 63    Private m_imageRawData() As Byte = Nothing
 64    Private m_strReader As String
 65  
 66   
 67  
 68  
 69    Public WriteOnly Property SetSecurity() As Integer
 70      Set(ByVal value As Integer)
 71        intSecurity = value
 72        If intSecurity = ENSECURITY.EN_READ Then
 73  
 74        End If
 75      End Set
 76    End Property
 77  
 78    
 79  
 80    Private Sub OpenPreviewWindow()
 81      Dim iHeight As Integer = pbPhoto.Height
 82      Dim iWidth As Integer = pbPhoto.Width
 83  
 84      '
 85      ' Open Preview window in picturebox
 86      '
 87      hHwnd = capCreateCaptureWindowA(iDevice.ToString, WS_VISIBLE Or WS_CHILD, 0, 0, 1280, _
 88          1024, pbPhoto.Handle.ToInt32, 0)
 89  
 90      '
 91      ' Connect to device
 92      '
 93      If SendMessage(hHwnd, WM_CAP_DRIVER_CONNECT, iDevice, 0) Then
 94        '
 95        'Set the preview scale
 96        '
 97        SendMessage(hHwnd, WM_CAP_SET_SCALE, True, 0)
 98  
 99        '
100        'Set the preview rate in milliseconds
101        '
102        SendMessage(hHwnd, WM_CAP_SET_PREVIEWRATE, 66, 0)
103  
104        '
105        'Start previewing the image from the camera
106        '
107        SendMessage(hHwnd, WM_CAP_SET_PREVIEW, True, 0)
108  
109        '
110        ' Resize window to fit in picturebox
111        '
112        SetWindowPos(hHwnd, HWND_BOTTOM, 0, 0, pbPhoto.Width, pbPhoto.Height, _
113                SWP_NOMOVE Or SWP_NOZORDER)
114  
115  
116  
117      Else
118        '
119        ' Error connecting to device close window
120        ' 
121        DestroyWindow(hHwnd)
122  
123      End If
124    End Sub
125  
126    Private Sub fncStart()
127      Try
128        File.Delete(Application.StartupPath & "\" & "Web.jpg")
129        If hHwnd = 0 Then
130          hHwnd = capCreateCaptureWindowA(iDevice, WS_VISIBLE Or WS_CHILD, 0, 0, pbPhoto.Width, pbPhoto.Height, pbPhoto.Handle.ToInt32, 0)
131        End If
132  
133        ' Connect to device
134        If SendMessage(hHwnd, WM_CAP_DRIVER_CONNECT, iDevice, 0) Then
135          ' set the video size
136          Dim myBitMapInfo As BITMAPINFO
137          With myBitMapInfo.bmiHeader
138            .biSize = Len(myBitMapInfo.bmiHeader)
139            .biWidth = 1024
140            .biHeight = 768
141            .biPlanes = 1
142            .biBitCount = 24
143          End With
144  
145          SendMessage(hHwnd, WM_CAP_SET_VIDEOFORMAT, Len(myBitMapInfo), myBitMapInfo)
146          'Set the preview scale
147          SendMessage(hHwnd, WM_CAP_SET_SCALE, True, 0)
148          'Set the preview rate in milliseconds
149          SendMessage(hHwnd, WM_CAP_SET_PREVIEWRATE, 66, 0)
150          'Start previewing the image from the camera
151          SendMessage(hHwnd, WM_CAP_SET_PREVIEW, True, 0)
152          ' Resize window to fit in picturebox
153          SetWindowPos(hHwnd, HWND_BOTTOM, 0, 0, pbPhoto.Width, pbPhoto.Height, SWP_NOMOVE Or SWP_NOZORDER)
154  
155        Else
156          ' Error connecting to device close window
157          DestroyWindow(hHwnd)
158        End If
159      Catch ex As Exception
160  
161      End Try
162  
163    End Sub
164  
165    Private Sub ClosePreviewWindow()
166      '
167      ' Disconnect from device
168      '
169      SendMessage(hHwnd, WM_CAP_DRIVER_DISCONNECT, iDevice, 0)
170  
171      '
172      ' close window
173      '
174  
175      DestroyWindow(hHwnd)
176    End Sub
177  
178    Private Sub fncCapture()
179      Dim data As IDataObject
180      Dim bmap As Bitmap
181      Dim strImageFileName As String
182      Dim fsImage As FileStream = Nothing
183      '
184      ' Copy image to clipboard
185      '
186      SendMessage(hHwnd, WM_CAP_EDIT_COPY, 0, 0)
187  
188      '
189      ' Get image from clipboard and convert it to a bitmap
190      '
191      data = Clipboard.GetDataObject()
192      If data.GetDataPresent(GetType(System.Drawing.Bitmap)) Then
193        strImageFileName = Application.StartupPath & "\" & "Web.jpg"
194        bmap = CType(data.GetData(GetType(System.Drawing.Bitmap)), Bitmap)
195        pbPhoto.Image = bmap
196  
197        ClosePreviewWindow()
198  
199        Trace.Assert(Not (bmap Is Nothing))
200  
201        bmap.Save(strImageFileName, Imaging.ImageFormat.Jpeg)
202  
203        pbPhoto.ImageLocation = strImageFileName
204        fsImage = New FileStream(strImageFileName, FileMode.Open, FileAccess.Read)
205        m_imageRawData = New Byte(fsImage.Length) {}
206        fsImage.Read(m_imageRawData, 0, fsImage.Length)
207        fsImage.Close()
208  
209      End If
210      hHwnd = Nothing
211    End Sub
212  
213    
214  
215    Private Sub frmTest_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
216      Dim retval As Int32
217      Dim readers(-1)() As Char
218      Try
219  
220   
221  
222        retval = GetReaders(readers)
223        'If retval = SCARD_E_NO_SERVICE Then
224        '    MessageBox.Show("Smart Card service not started")
225        '    Exit Sub
226        'ElseIf retval = SCARD_E_NO_READERS_AVAILABLE Then
227        '    MessageBox.Show("No readers available")
228        '    Exit Sub
229        'ElseIf retval <> 0 Then
230        '    MessageBox.Show("Error listing reader")
231        '    Exit Sub
232        'End If
233        m_strReader = CStr(readers(0))
234        If txtName.CanFocus Then txtName.Focus()
235  
236  
237      Catch ex As Exception
238        cmdMyKad.Visible = False
239        'MessageBox.Show(ex.Message)
240      End Try
241    End Sub
242  
243   
244  
245  
246    Private Sub pbPhoto_Resize(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles pbPhoto.Resize
247      fncStart()
248    End Sub

modified 17-Aug-21 3:45am.

Re: Blank/Black webcam on newer model laptop running webcam program in VB.net

Richard MacCutchan16-Aug-21 21:46