Excel Automation

Hi,
I want to export data from listbox into an excel app.

excel.Range("C23").Value=" New product" excel.Range("C24").Value=" Reject" excel.Range("C25").Value=" No Attrib" excel.Range("C26").Value=" New year"

this code is running and work, but the problem is I have to define which cell will be inserted.
I cant calculate how many rows in Listbox, because its depend to a user.

I tried to modificate it into,

  [code]excel.Range("C" + Str(i + 3)).value = " New product"
  excel.Range("C" + Str(i + 3)).value = " Reject"
  excel.Range("C" + Str(i + 3)).value = " No Attrib"
  excel.Range("C" + Str(i + 3)).value = " New year"[/code]

but it won’t follow the last rows of listbox exported.

this is the code of exporting process into excel.

[code] excel.Range(“G3:G100”).HorizontalAlignment=4 'right
excel.Range(“A2”).Value = LstMasuk.heading(0)
excel.Range(“B2”).Value = LstMasuk.heading(1)
excel.Range(“C2”).Value = LstMasuk.heading(2)
excel.Range(“D2”).Value =LstMasuk.heading(3)
excel.Range(“E2”).Value = LstMasuk.heading(4)
excel.Range(“F2”).Value =LstMasuk.heading(5)
excel.Range(“G2”).Value =LstMasuk.heading(6)
excel.Range(“H2”).Value =LstMasuk.heading(7)
excel.Range(“I2”).Value =LstMasuk.heading(8)
excel.Range(“J2”).Value =LstMasuk.heading(9)
excel.Range(“K2”).Value =LstMasuk.heading(10)
excel.Range(“L2”).Value =LstMasuk.heading(11)
excel.Range(“M2”).Value =LstMasuk.heading(12)
excel.Range(“N2”).Value =LstMasuk.heading(13)
excel.Range(“O2”).Value =LstMasuk.heading(14)

  excel.Range("A2:O2").Font.Bold=true
  excel.Range("A2:O2").Interior.ColorIndex=2
  excel.Range("A2:O2").Borders.LineStyle=1
  
  excel.Range("A2:"+"O"+str(i+3)).Borders.LineStyle=1
  excel.Range("A2:O2").interior.ColorIndex=56'dark grey
  excel.Range("A" + Str(i + 3), "A" + Str(i + 3)).value = lstMasuk.cell(i, 0)
  excel.Range("B" + Str(i + 3), "B" + Str(i + 3)).value =lstMasuk.cell(i, 1)
  excel.Range("C" + Str(i + 3), "C" + Str(i + 3)).value = lstMasuk.cell(i, 2)
  
  excel.Range("D" + Str(i +3), "D" + Str(i + 3)).value =lstMasuk.cell(i, 3)
  excel.Range("E" + Str(i + 3), "E" + Str(i + 3)).value = lstMasuk.cell(i, 4)
  excel.Range("F" + Str(i + 3), "F" + Str(i + 3)).value = lstMasuk.cell(i, 5)
  excel.Range("G" + Str(i + 3), "G" + Str(i + 3)).value =lstMasuk.cell(i, 6)
  excel.Range("H" + Str(i + 3), "H" + Str(i + 3)).value =lstMasuk.cell(i, 7)
  excel.Range("I" + Str(i + 3), "I" + Str(i + 3)).value =lstMasuk.cell(i, 8)
  excel.Range("J" + Str(i + 3), "J" + Str(i + 3)).value = lstMasuk.cell(i, 9)
  excel.Range("K" + Str(i + 3), "K" + Str(i + 3)).value = lstMasuk.cell(i, 10)
  excel.Range("L" + Str(i + 3), "L" + Str(i + 3)).value = lstMasuk.cell(i, 11)
  excel.Range("M" + Str(i + 3), "M" + Str(i + 3)).value = lstMasuk.cell(i, 12)[/code]

any helps?

thanks
arief

Hi, I use:

Excel.Cells(i+1,j+1).Value = Listbox1.Cell(i,j) // i = row, j = column

because Listbox is 0 based and Excel is 1 based.

So:

excel.Range("C23").Value=" New product" excel.Range("C24").Value=" Reject" excel.Range("C25").Value=" No Attrib" excel.Range("C26").Value=" New year"
I will use:

excel.Cells(23,3).Value=" New product" excel.Cells(24,3).Value=" Reject" excel.Cells(25,3).Value=" No Attrib" excel.Cells(26,3).Value=" New year"
Hope this helps.

Dear Mr. Poo,

Its still define in which cell the text should be print.
my goals is, how to insert it at the end after exporting the content of a listbox,

for example, If I have 20 rows in a listbox, so, that text should be in cell no C21, or if I have 15 rows, then it should be start in cell no. C16.

Thanks,
Regards,
Arief

You can use Listcount to have an integer with the # of rows on a listbox.

So if you need to put something after the 20 or 15 rows:

Dim k as integer = listbox1.listcount excel.Cells(k+1,3).Value=" New product" // will be C16 or C21 excel.Cells(k+2,3).Value=" Reject" // will be C17 or C22 excel.Cells(k+3,3).Value=" No Attrib" // will be C18 or C23 excel.Cells(k+4,3).Value=" New year" // will be C19 or C24

Note: ListCount is 1 based and not 0 based

still no luck,

I just want to export it as a footer.

here’s the screenshot about my goals.
https://ibb.co/ks42Gd

link text

thanks
Regards,
Arief

humbly, what you want is so easy that i dont understand what the problem is

Arief,
can you show us some of your code?

I think you could consider getting Eugene Dakin’s book Program Excel 2016 with Xojo in Windows from xdevlibrary and follow the examples there.

I don’t think the problem is what you want:

but what you have on your listbox (can you share a picture?) and the code to transfer that to Excel?.

What do the parameters of the for loop look like?
for i=0 to …

hi,
yes, I am plan to get mr. dakin’s book soon.

here’s the code for exporting,

[code] case “Export ke Excel”
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

If r1.Value then
  excel.Range("A1:O1").MergeCells=true
  excel.ActiveSheet.Name = "Laporan Barang Masuk"
else
  excel.Range("A1:O1").MergeCells=true
  excel.ActiveSheet.Name = "Laporan Barang Retur"
end if

excel.Columns.Range("A1").Value=excel.ActiveSheet.name
excel.Columns.Range("A1").Font.Size=18
excel.Columns.Range("A1").Font.Bold=true
excel.Columns.Range("A1").HorizontalAlignment=3 'center
excel.Columns.Range("A1").VerticalAlignment=1'top
excel.Columns.Range("A1").RowHeight=46.50

excel.Columns.Range("A2").ColumnWidth=11.00
excel.Columns.Range("B2").ColumnWidth=10.71
excel.Columns.Range("C2").ColumnWidth=38.86
excel.Columns.Range("D2").ColumnWidth=11.14
excel.Columns.Range("E2").ColumnWidth=51.43
excel.Columns.Range("F2").ColumnWidth=9.00
excel.Columns.Range("G2").ColumnWidth=6.00
excel.Columns.Range("H2").ColumnWidth=6.29
excel.Columns.Range("I2").ColumnWidth=6.00
excel.Columns.Range("J2").ColumnWidth=6.00
excel.Columns.Range("K2").ColumnWidth=6.00
excel.Columns.Range("L2").ColumnWidth=6.00
excel.Columns.Range("M2").ColumnWidth=6.00
excel.Columns.Range("N2").ColumnWidth=6.00
excel.Columns.Range("O2").ColumnWidth=27.00

For i = 0 to lstMasuk.listcount - 1
  excel.Range("A2:R2").Font.Bold=true
  excel.Range("D3:D100").NumberFormat="@"
  'excel.Range("A2").Value = listbox1.heading(0)
  excel.Range("B2").Value = lstMasuk.heading(1)
  excel.Range("C2").Value = lstMasuk.heading(2)
  excel.Range("D2").Value =lstMasuk.heading(3)
  excel.Range("E2").Value = lstMasuk.heading(4)
  excel.Range("F2").Value =lstMasuk.heading(5)
  excel.Range("G2").Value =lstMasuk.heading(6)
  excel.Range("H2").Value =lstMasuk.heading(7)
  excel.Range("I2").Value =lstMasuk.heading(8)
  excel.Range("J2").Value =lstMasuk.heading(9)
  excel.Range("K2").Value =lstMasuk.heading(10)
  excel.Range("L2").Value =lstMasuk.heading(11)
  excel.Range("M2").Value =lstMasuk.heading(12)
next


For i = 0 to LstMasuk.listcount - 1
  
  excel.Range("G3:G100").HorizontalAlignment=4 'right
  excel.Range("A2").Value = LstMasuk.heading(0)
  excel.Range("B2").Value = LstMasuk.heading(1)
  excel.Range("C2").Value = LstMasuk.heading(2)
  excel.Range("D2").Value =LstMasuk.heading(3)
  excel.Range("E2").Value = LstMasuk.heading(4)
  excel.Range("F2").Value =LstMasuk.heading(5)
  excel.Range("G2").Value =LstMasuk.heading(6)
  excel.Range("H2").Value =LstMasuk.heading(7)
  excel.Range("I2").Value =LstMasuk.heading(8)
  excel.Range("J2").Value =LstMasuk.heading(9)
  excel.Range("K2").Value =LstMasuk.heading(10)
  excel.Range("L2").Value =LstMasuk.heading(11)
  excel.Range("M2").Value =LstMasuk.heading(12)
  excel.Range("N2").Value =LstMasuk.heading(13)
  excel.Range("O2").Value =LstMasuk.heading(14)
  
  excel.Range("A2:O2").Font.Bold=true
  excel.Range("A2:O2").Interior.ColorIndex=2
  excel.Range("A2:O2").Borders.LineStyle=1
  
  excel.Range("A2:"+"O"+str(i+3)).Borders.LineStyle=1
  excel.Range("A2:O2").interior.ColorIndex=56'dark grey
  excel.Range("A" + Str(i + 3), "A" + Str(i + 3)).value = lstMasuk.cell(i, 0)
  excel.Range("B" + Str(i + 3), "B" + Str(i + 3)).value =lstMasuk.cell(i, 1)
  excel.Range("C" + Str(i + 3), "C" + Str(i + 3)).value = lstMasuk.cell(i, 2)
  excel.Range("D" + Str(i +3), "D" + Str(i + 3)).value =lstMasuk.cell(i, 3)
  excel.Range("E" + Str(i + 3), "E" + Str(i + 3)).value = lstMasuk.cell(i, 4)
  excel.Range("F" + Str(i + 3), "F" + Str(i + 3)).value = lstMasuk.cell(i, 5)
  excel.Range("G" + Str(i + 3), "G" + Str(i + 3)).value =lstMasuk.cell(i, 6)
  excel.Range("H" + Str(i + 3), "H" + Str(i + 3)).value =lstMasuk.cell(i, 7)
  excel.Range("I" + Str(i + 3), "I" + Str(i + 3)).value =lstMasuk.cell(i, 8)
  excel.Range("J" + Str(i + 3), "J" + Str(i + 3)).value = lstMasuk.cell(i, 9)
  excel.Range("K" + Str(i + 3), "K" + Str(i + 3)).value = lstMasuk.cell(i, 10)
  excel.Range("L" + Str(i + 3), "L" + Str(i + 3)).value = lstMasuk.cell(i, 11)
  excel.Range("M" + Str(i + 3), "M" + Str(i + 3)).value = lstMasuk.cell(i, 12)
  
  // this has to be print at the end, while the listbox content has been exported into excel
  
  excel.Range("C" + Str(i + 3)).value=" New product" // will be C16 or C21
  excel.Range("C" + Str(i + 3)).Value=" Reject" // will be C17 or C22
  excel.Range("C" + Str(i + 3)).Value=" No Attrib" // will be C18 or C23
  excel.Range("C" + Str(i + 3)).Value=" New year" // will be C19 or C24
next[/code]

Thanks
Regards,
Arief

Arief,
I think you have a lot of things inside for - next loops that you only need to define once. Also, you still using excel.Range when I think you should use excel.Cells at least in some parts. Can you post a picture of the end results on the excel from your code?

For example, this part:

[code] // this has to be print at the end, while the listbox content has been exported into excel

  excel.Range("C" + Str(i + 3)).value=" New product" // will be C16 or C21
  excel.Range("C" + Str(i + 3)).Value=" Reject" // will be C17 or C22
  excel.Range("C" + Str(i + 3)).Value=" No Attrib" // will be C18 or C23
  excel.Range("C" + Str(i + 3)).Value=" New year" // will be C19 or C24
next[/code]

should be:

next // this has to be print at the end, while the listbox content has been exported into excel dim j as integer = LstMasuk.listcount excel.Cells(j+2,3).value=" New product" excel.Cells(j+3,3).Value=" Reject" excel.Cells(j+4,3).Value=" No Attrib" excel.Cells(j+5,3).Value=" New year"

if I create another loop, then it will start again from 1 + 3, so the cells is replaced,

here’s the screen shot.
https://ibb.co/kpajxJ

thanks
Arief

I used j+2 (listcount + 2) because your code show excel.Range("C" + Str(i + 3)).value=" New product", but because you already using Str(i+3) in other places then you have to change j+2 to j+3 or even j+4. Then j+3 change to higher value (either j+4 or j+5), and so on.

If you want to create another loop, for other listbox or anything else, then you have to know the last loop listcount, then start a few rows below all what you wrote to excel. Maybe Dim StartPoint As Integer = 1, then if you going to put all information in, let’s say, listcount + 8, then make StartPoint value be StartPoint = StartPoint + listcount + 10.

Now use i + StartPoint + 3 and j + StartPoint + 4 (and 5, 6, 7)

thanks a lot.

its works now.

regards,
arief