Help with MBS XL Plugin

  1. 10 months ago

    Hello good day, could you help me someone to solve the following case, I will be very grateful.

    In a list of data, there is this information that is variable:

    Description |Amount1|Amount2|Amount3
    Ball-Point Pens | $85| $200| $50
    T-Shirts | $150| $80| $100
    Tea cups | $50 |$100| $200

    The following code (MBS XL Plugin) I try to insert automatically the content of the list data, this content can vary from 10 to 200 records or more.

    I appreciate the help!
    Use google translator

    sheet = book.AddSheet("Rec")

    call sheet.WriteString(2, 1, "Rec No. 0001", titleFormat)

    call sheet.WriteString(4, 1, "Name: NAME")
    call sheet.WriteString(5, 1, "Address: San Ramon, CA 94583 USA")

    call sheet.WriteString(7, 1, "Description", headerFormat)
    call sheet.WriteString(7, 2, "Amount1", headerFormat)
    call sheet.WriteString(7, 3, "Amount2", headerFormat)
    call sheet.WriteString(7, 4, "Amount3", headerFormat)

    call sheet.WriteString(8, 1, "Ball-Point Pens", descriptionFormat)
    call sheet.WriteNumber( 8, 2, 85, amountFormat)
    call sheet.WriteNumber( 8, 3, 200, amountFormat)
    call sheet.WriteNumber( 8, 4, 50, amountFormat)

    call sheet.WriteString(9, 1, "T-Shirts", descriptionFormat)
    call sheet.WriteNumber( 9, 2, 200, amountFormat)
    call sheet.WriteNumber( 9, 3, 80, amountFormat)
    call sheet.WriteNumber( 9, 4, 100, amountFormat)

    call sheet.WriteString(10, 1, "Tea cups", descriptionFormat)
    call sheet.WriteNumber( 10, 2, 50, amountFormat)
    call sheet.WriteNumber( 10, 3, 100, amountFormat)
    call sheet.WriteNumber( 10, 4, 200, amountFormat)

  2. Christian S

    16 Feb 2017 Pre-Release Testers, Xojo Pro Germany

    What is your problem?

  3. Hello, Christian Schmitz!

    Thank you for answering,

    The problem is that I want to insert a list of data values containing, try it as shown in the attached example.
    Something like: For i As Integer = 0 To Datalist.listcount - 1
    In Excel begin apartir of the b9 column / line 8 begins to show the information, try the For Next placing the i, for example:

    call sheet. WriteString(i, 1, "Ball-Point Pens", descriptionFormat)
    call sheet. WriteNumber (i, 2, 85, amountFormat)
    call sheet. WriteNumber (i, 3, 200, amountFormat)
    call sheet. WriteNumber (i, 4, 50, amountFormat)

    But did not work, you think to be able to help me?
    Sorry if I don't understand.

    This code is MBS XL Plugin, it works but I have to place code manually the description and quantity, when I want to fill from a list of data.

    call sheet. WriteString(8, 1, "Ball-Point Pens", descriptionFormat)
    call sheet. WriteNumber (8, 2, 85, amountFormat)
    call sheet. WriteNumber (8, 3, 200, amountFormat)
    call sheet. WriteNumber (8, 4, 50, amountFormat)

    call sheet. WriteString(9, 1, "T-Shirts", descriptionFormat)
    call sheet. WriteNumber (9, 2, 200, amountFormat)
    call sheet. WriteNumber (9, 3, 80, amountFormat)
    call sheet. WriteNumber (9, 4, 100, amountFormat)

  4. Jared F

    16 Feb 2017 Pre-Release Testers, Xojo Pro Answer Milwaukee, WI

    What is the source of your data?

    If you have the data in a listbox (called dataList in this code) you can do something like this:

    dim row as integer = 8 // This is the row where you want to start writing the data in excel
    		
    for i as integer = 0 to dataList.ListCount - 1
    				
    	call sheet.WriteString(row,1,dataList.Cell(i,0),descriptionFormat)
    	call sheet.WriteNumber(row,2,dataList.Cell(i,1).Val,amountFormat)
    	call sheet.WriteNumber(row,3,dataList.Cell(i,2).Val,amountFormat)
    	call sheet.WriteNumber(row,4,dataList.Cell(i,3).Val,amountFormat)
    				
    	row = row + 1
    				
    next

    The columns are hard coded here, but your example seems to be pretty simple.
    You can add an inner for loop if you want the column count to be dynamic.

    Is that what you are looking for?

  5. If it is what you seek, a million thanks, Jared Feder!
    It worked.

    Thank you for your valuable help.

or Sign Up to reply!