Help with Excel

Getting Started
1

Hi,

I want to export data from a listbox into excel, I am using xojo 2021. r.1.1
Everything works fine, except I cant put value from a textfield onto it.

Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Cash"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Cash.text  << this not null but still not shown in excel
Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Mesin EDC BCA"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Card.Text << this not null but still not shown in excel
Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Mesin EDC BRI"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Card2.Text << this not null but still not shown in excel

Here is the full code,

Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Cash"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Cash.text 'before was 12
Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Mesin EDC BCA"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Card.Text
Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Mesin EDC BRI"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Card2.Text
Listbox1.AddRow  ""
Listbox1.Cell(Listbox1.LastIndex,7)= "Mesin EDC Mandiri"
Listbox1.Cell(Listbox1.LastIndex,9)= t_Card3.Text
Listbox1.AddRow  ""
dim monthnames as string
monthnames="Januari;Pebruari;Maret;April;Mei;Juni;Juli;Agustus;September;Oktober;November;Desember"
Dim dx as New Date, monthID as integer
Dim ghs as string
monthID=dx.month
ghs=nthfield(monthnames,";",monthID)
dim d as new date
dim a as string
if d.Day<10 then
  a="0"+str(d.Day)+"-"+str(ghs)+"-"+str(d.Year)
else
  a=str(d.Day)+"-"+str(ghs)+"-"+str(d.Year)
end if

Listbox1.Cell(Listbox1.LastIndex,1)= "Keterangan : Uang Cash & Bukti Bank diserahkan kepada Ibu Monika tanggal "+str(a)
Listbox1.Cell(Listbox1.LastIndex,7)= "Total"
Listbox1.Cell(Listbox1.LastIndex,9)= t_total.Text

try
  Dim excel as ExcelApplication
  Dim book as ExcelWorkbook
  Dim sheet as ExcelWorksheet
  Dim i as Integer
  excel = New ExcelApplication
  excel.Visible = True
  book = excel.Workbooks.Add
  excel.ActiveSheet.Name = ComboBox1.Text
  excel.Columns.Range("B1:M1").Select_
  'alignment 1 = Left
  'alignment 3 = center
  excel.Columns.Range("B1:M1").VerticalAlignment=3
  excel.Columns.Range("B1:M1").HorizontalAlignment=3
  'excel.ActiveWindow.Selection.merge
  excel.Columns.Range("B2").ColumnWidth=12.57
  excel.Columns.Range("C2").ColumnWidth=12.43
  excel.Columns.Range("D2").ColumnWidth=13.29
  excel.Columns.Range("E2").ColumnWidth=66.43
  excel.Columns.Range("F2").ColumnWidth=6.00
  excel.Columns.Range("G2").ColumnWidth=15.00
  excel.Columns.Range("H2").ColumnWidth=18.43
  excel.Columns.Range("I2").ColumnWidth=18.43
  excel.Columns.Range("J2").ColumnWidth=48.00
  excel.Columns.Range("K2").ColumnWidth=9.14
  excel.Columns.Range("L2").ColumnWidth=30.00
  excel.Columns.Range("M2").ColumnWidth=43.14
  
  For i = 0 to Listbox1.listcount - 1
    excel.Range("B1:M3").Font.Bold=true
    dim gh as string
    gh=Uppercase(ComboBox1.Text)
    excel.Range("A1:Z200").Font.Name="arial"
    excel.Range("B1:M3").Font.Bold=true
    excel.Range("B1:M3").Font.Size=16
    excel.Range("B1").Value = "LAPORAN PENJUALAN TPC "+ gh 
    excel.Range("B3:M3").Font.Size=11
    excel.Range("B3:M3").interior.ColorIndex=36 'lightBlue
    excel.Range("B3").Value = "Tanggal"
    excel.Range("C3").Value ="Nomor Nota"
    excel.Range("D3").Value = "Kode"
    excel.Range("E3").Value ="Rincian"
    excel.Range("F3").Value ="Qty"
    excel.Range("G3").Value ="Harga"
    excel.Range("H3").Value ="Qty x Harga"
    excel.Range("I3").Value ="Subtotal (Rp)"
    excel.Range("J3").Value ="Keterangan"
    excel.Range("K3").Value ="Jam"
    excel.Range("L3").Value ="Customer"
    excel.Range("M3").Value ="Sales"
  next
  
  For i = 0 to Listbox1.listcount - 1
    excel.Range("A1:Z200").Font.Name="Arial"
    excel.Range("B3:M3").Font.Size=11
    excel.Range("G4:"+"G"+str(i+4)).NumberFormat="_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" '"_(* #.##0_);_(* (#.##0);_(* ""-""_);_(@_)"
    excel.Range("H4:"+"H"+str(i+4)).NumberFormat="_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" '"_(* #.##0_);_(* (#.##0);_(* ""-""_);_(@_)"
    excel.Range("I4:"+"I"+str(i+4)).NumberFormat="_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" '"_(* #.##0_);_(* (#.##0);_(* ""-""_);_(@_)"
    'excel.Range("F4:"+"H"+str(i+4)).NumberFormat="General"
    excel.Range("B3:"+"M"+str(i+4)).Borders.LineStyle=1
    excel.Range("B" + Str(i + 4), "B" + Str(i + 4)).value = Listbox1.cell(i, 1) 'tgl
    excel.Range("C" + Str(i + 4), "C" + Str(i + 4)).value = Listbox1.cell(i, 3)' nota
    excel.Range("D" + Str(i + 4), "D" + Str(i + 4)).value = Listbox1.cell(i, 4)' kode
    
    if Listbox1.cell(i, 5)<>""  then
      dim dd,dz as string
      dd=Listbox1.cell(i,4).Left(1)
      excel.Range("E" + Str(i +4), "E" + Str(i + 4)).value = Listbox1.cell(i, 5)+" ("+str(dd)+")" 'rincian/deskripsi
    end if
    
    if Listbox1.cell(i, 8)<>"" and Listbox1.cell(i, 4)<>""  then
      dim de,dd as String
      de=Listbox1.cell(i,4).Left(1)
      
      dim a1,b1 as integer
      dim c1 as double  // otherwise 12,5 becomes 12
      dim d1 as string
      a1=CDbl(listbox1.cell(i,6))* CDbl(window1.listbox1.cell(i,7))
      b1=CDbl(window1.listbox1.cell(i,8))
      c1=ABS(b1/a1)*100
      d1= Format(c1, "###")+"%"
      excel.Range("E" + Str(i +4), "E" + Str(i + 4)).value = Listbox1.cell(i, 5)+" ("+str(de)+") (Disc "+str(d1)+")"'rincian/deskripsi
    end if
    excel.Range("F" + Str(i + 4), "F" + Str(i + 4)).value = Listbox1.cell(i, 6) 'qty
    excel.Range("G" + Str(i + 4), "G" + Str(i + 4)).value = Listbox1.cell(i, 7) 'harga
    
    dim z as Integer
    z=CDbl(listbox1.cell(i,7))* CDbl(window1.listbox1.cell(i,6))
    dim klx,klz,klk as Integer
    klx=CDbl(listbox1.cell(i,7))* CDbl(window1.listbox1.cell(i,6))
    klz=CDbl(window1.listbox1.cell(i,8))
    klk=klx+klz
    if IsNumeric(Listbox1.cell(i, 6)) then
      excel.Range("H" + Str(i + 4), "H" + Str(i + 4)).value = str(klx) 'qty x harga
      excel.Range("I" + Str(i + 4), "I" + Str(i + 4)).value = str(klk) ' subtotal
    end if
    excel.Range("J" + Str(i + 4), "J" + Str(i + 4)).value = Listbox1.cell(i, 12) 'payment type
    excel.Range("K4:"+"M"+str(i+4)).NumberFormat="h:mm;@"
    excel.Range("K" + Str(i + 4), "K" + Str(i + 4)).value = Listbox1.cell(i, 2) 'jam
    excel.Range("L" + Str(i + 4), "L" + Str(i + 4)).value = Listbox1.cell(i, 10) 'customer
    excel.Range("M" + Str(i + 4), "M" + Str(i + 4)).value = Listbox1.cell(i, 13) 'customer
    
    
    
    if Listbox1.cell(i, 1).left(2) = "Ke" then
      excel.Range("B" + Str(i + 4), "E" + Str(i + 4)).Select_
      excel.Range("B" + Str(i + 4), "E" + Str(i + 4)).Font.Bold=true
      'alignment 1 = Left
      'alignment 3 = center
      excel.Range("B" + Str(i + 4)).VerticalAlignment=3
      excel.Range("B" + Str(i + 4)).HorizontalAlignment=1
      'excel.ActiveWindow.Selection.merge
    end if
    
    if Listbox1.cell(i, 7).left(2) = "Ca" then
      excel.Range("F" + Str(i + 4), "H" + Str(i + 4)).Select_
      excel.Range("F" + Str(i + 4), "I" + Str(i + 4)).Font.Bold=true
      excel.Range("F" + Str(i + 4)).VerticalAlignment=4
      excel.Range("F" + Str(i + 4)).HorizontalAlignment=4
      'excel.ActiveWindow.Selection.merge
    end if
    
    if Listbox1.cell(i, 7).left(2) = "Me" then
      excel.Range("F" + Str(i + 4), "H" + Str(i + 4)).Select_
      excel.Range("F" + Str(i + 4), "I" + Str(i + 4)).Font.Bold=true
      excel.Range("F" + Str(i + 4)).VerticalAlignment=4
      excel.Range("F" + Str(i + 4)).HorizontalAlignment=4
      'excel.ActiveWindow.Selection.merge
    end if
    
    if Listbox1.cell(i, 7).left(2) = "To" then
      excel.Range("F" + Str(i + 4), "H" + Str(i + 4)).Select_
      excel.Range("F" + Str(i + 4), "I" + Str(i + 4)).Font.Bold=true
      excel.Range("F" + Str(i + 4)).VerticalAlignment=4
      excel.Range("F" + Str(i + 4)).HorizontalAlignment=4
      'excel.ActiveWindow.Selection.merge
    end if
  next

any helps,

thanks
arief

2

if this works:

Listbox1.Cell(Listbox1.LastIndex,7)= “Cash”

does this put words in the same place?
Listbox1.Cell(Listbox1.LastIndex,7)= t_Cash.text

If it does, then the listbox has the wrong number of columns set in the IDE… Im guessing it needs to be 12 or more.

You say it is not nil… what do you see if you insert
msgbox t_Cash.text
at that point, or examine it in the debugger?

3

hi,

No, its not.

but the strange thing, if I remove this code, works normally,

'dim z as Integer
'z=CDbl(listbox1.cell(i,7))* CDbl(window1.listbox1.cell(i,6))
'dim klx,klz,klk as Integer
'klx=CDbl(listbox1.cell(i,7))* CDbl(window1.listbox1.cell(i,6))
'klz=CDbl(window1.listbox1.cell(i,8))
'klk=klx+klz
'if IsNumeric(Listbox1.cell(i, 6)) then
excel.Range("H" + Str(i + 4), "H" + Str(i + 4)).value = Listbox1.cell(i, 8) 'qty x harga
excel.Range("I" + Str(i + 4), "I" + Str(i + 4)).value = Listbox1.cell(i, 9) ' subtotal
'end if

Actually I need it to make sure the value calculation.

anyways,

thanks for the help…

regards,
arief

4

Yeah. It’s clear that I don’t know what you are actually asking then.

probably the problem lies here:

if IsNumeric(Listbox1.cell(i, 6)) then

since in your code, I cannot see anywhere that you are placing a number into column 6 of your listbox.
Should you be looking in Listbox1.cell(i, 9) … the Cash ?

5

yes, I have found the problem in this line.

its a mistake the cell format was in wrong format. now its fixed.

thanks
arief