Enviar datos del listbox a una hoja de Excel en bucle

Hello Pol,

Eso es bastante sencillo, ¿pero seguro que lo quieres hacer así? No sé de que trata tu libro, pero dices que quieres registrar pedidos de una empresa. En una situación real, los artículos que se ordenaron en UN pedido, no deberían determinar los registros, eso lo debería hacer el pedido como tal. Mejor te pongo un ejemplo, observa la diferencia:

Como tu lo quieres:

Empresa 1 | Articulo 1

Empresa 1 | Articulo 2

Empresa 1 | Articulo 3

Como yo lo haría:

Pedido 1 | Empresa 1 | Articulo 1 | Articulo 2 | Articulo 3

Pedido 2 | Empresa 2 | Articulo 1 | Articulo 2

etc ect

En una base de datos real, la primera opción seria un mal gasto de recursos. El registro debería estar liderado por el pedido en sí, no por cada articulo individual de cada pedido ya que terminarías con una tabla extrañamente grande. Pero es solo una sugerencia.

De todas formas, aquí te dejo el código para como lo quieres:

Private Sub CommandButton1_Click()
Dim datos As Worksheet: Set datos = Sheets("datos")
Dim nF As Long
Dim totalArt As Long: totalArt = Me.ListBox1.ListCount
Dim i As Long
If totalArt > 0 Then
    For i = 0 To totalArt - 1
        nF = datos.Cells(Rows.Count, 1).End(xlUp).Row + 1
        datos.Cells(nF, 1).Value = Me.Label1.Caption
        datos.Cells(nF, 2).Value = Me.ListBox1.List(i, 0)
        datos.Cells(nF, 3).Value = Me.ListBox1.List(i, 1)
    Next i
Else
    MsgBox "No hay articulos...", vbInformation, "Nada..."
End If
End Sub

Así es como funciona: video demo

Andy

Muchas gracias Andy. Me parece muy interesante esto que propones.

Arriba he intentado simplificar los datos para poder expresarme un poco mejor y que fuera más fácil de entender. En mi cabeza lo había estructurado así (no tengo mucha idea de VBA) porque era la manera "coherente" o "mejor" (desde mi ignorancia).

Los campos que tengo en mi tabla son estos (de la columna A a la M):

• Número (número único que identifica al la sucursal)
• Tipología (tipo de sucursal)
• Nom Equipament (nombre de la sucursal)
• Municipio (localidad especifica)
• Comarca (localidad genérica)
• FA (Número de la hoja donde se solicita material de una sucursal hacia central)
• PAD (número de pedido al proveedor. De central a la empresa)
• Proveedor (empresa a la que se pide el material)
• Material (tipo de material)
• Importe (Importe total del PAD)
• fecha (fecha solicitada)

Desde mi desconocimiento si hago la forma en la que me propones, ¿se me van a modificar las columnas en la parte de material dependiendo de la cantidad de productos que se pidan no? Por lo tanto, para que no afectara al contenido de la tabla, ¿el item material debería ser el ultimo de la tabla no? (¿Pero desde mi punto de vista no es lo mejor que ese dato este tan al final no? - Es un dato importante-)

Me gustaría a ver si me puedes contar más sobre tu método o como se podría solucionar estos inconvenientes, ya que seria un método más eficiente ya que no se deberían repetir tantos datos.
Aprovecho para decir que este excel se hace anualmente (no es que tenga que almacenar una gran cantidad de datos)

Muchas gracias de nuevo Andy

El código que me has pasado me funciona perfectamente, solo que me excluye los datos de la tabla. En la hoja datos tengo un rango dianamico llamada DatosTabla y tiene un formato de tabla. Al pulsar el botón me manda los registros debajo de la tabla (excluyéndolos) además de que no es la primera fila vacía.

Espero explicarme bien.

Adjunto una imagen donde puedes ver que encima de la fecha se termina la tabla de datos y que lo datos introducidos van debajo y no se incluyen en esa tabla.

He intentando hacer yo las modificaciones para el registros siempre vaya dentro del rango de la tabla y a la primera celda vacía pero no lo he logrado.

Ah, ¿es en una tabla donde quieres colocar los datos? Si es así, el código cambia ligeramente, ya que usaríamos las propiedades de la tabla que en VBA es un ListObject.

Private Sub CommandButton1_Click()
Dim datos As Worksheet: Set datos = Sheets("datos")
Dim TablaPro As ListObject: Set TablaPro = datos.ListObjects("TablaPro")
Dim TablaProRow As ListRow: Set TablaProRow = TablaPro.ListRows.Add
Dim totalArt As Long: totalArt = Me.ListBox1.ListCount
Dim i As Long
If totalArt > 0 Then
    For i = 0 To totalArt - 1
        TablaProRow.Range(i + 1, 1).Value = Me.Label1.Caption
        TablaProRow.Range(i + 1, 2).Value = Me.ListBox1.List(i, 0)
        TablaProRow.Range(i + 1, 3).Value = Me.ListBox1.List(i, 1)
    Next i
Else
    MsgBox "No hay articulos...", vbInformation, "Nada..."
End If
End Sub

Pruebalo y me avisas.

En cuanto a lo que me preguntas si el material debe ir al final, bueno, pues depende, lo ideal es que sí. La idea detrás de eso es que sea el ultimo ya que su "largo" es variable.

También esta la opción de mantenerlos en la misma columna pero separarlos con algún separador, quizás la coma o una barra diagonal. Hay quienes prefieren un método u otro. No es complicado obtener los artículos individuales cuando están separados por un separador, ya que se puede usar la función Split. Lo único malo de la segunda opción es que si una orden tiene muchos artículos entonces la columna seria muy ancha quizás.

Yo tengo un proyecto en el que uso el primer método con algo mas que es la cantidad.

Por ejemplo, digamos que un cliente compró 8 clavos, 6 tornillos y 2 destornilladores. En mi caso queda así (En realidad uso el ID del producto, no el nombre):

CLAVOS(8) | TORNILLOS(6) | DESTORNILLADORES(2)

Es el producto mas la cantidad entre paréntesis.

PD: Si me compartes el libro, tal vez te pueda ayudar mejor.

Hola de nuevo Andy, he intentando adaptar el código que me has pasado pero me salta error en esta parte :

Set TablaPro = datos.ListObjects("TablaPro")

Error 9 sub indicefuera de intervalo (es lo que me dice). He intentando solucionarlo pero no encuentro el modo.

La verdad que lo que propones es interesante además de minimizar la base de datos (ya que con mi método repetiría datos). ¿El único "contra" que le puedo ver es que para visualizar un pedido para hacerlo de una manera más clara debería programar otro form para visualizar los datos de un modo más legible no?.

Aquí te dejo el enlace del libro.

https://drive.google.com/file/d/10GdGFmSy2Br-WyX9vW9nLyZhnF3mmeyy/view?usp=sharing 
El documento esta en catalán, es un idioma que también se habla en españa y que se parece mucho al castellano (no creo que tengas problemas para entenderlo) Ej: Importe -> Import, formulario -> formulari ... (como ves es casi igual, aunque alguna palabra puede variar un poco)

Me gustaría volverte a dar las gracias por la ayuda que me estas dando.

Hello Pol, disculpa la demora, me han llegado tantas preguntas que me perdí en el orden de a quien atendía. Me acorde ahora que tenia esto pendiente contigo.

Pude entender el catalán, ya puedo decir que hablo otro idioma JA!

Sin embargo no he podido probar bien el libro, ya que el formulario es bastante grande y no se que cosas poner, para poder llegar al final y ver el botón enviar (que esta invisible junto con muchas cosas mas) muchas cosas dependen de otras y bueno, no quise involucrarme tanto.

Pero ese error me parece que es porque no cambiaste el nombre de la tabla. "TablaPro" es el nombre que yo le puse a mi tabla para hacer mis pruebas, la tuya se llama Taula4 (parece ser un nombre genérico por defecto)

Creo que si cambias solo ese detalle, ya te funcionará.

Aunque no pude probarlo, adapte el código, creo que esta bien, pruebalo tu:

Private Sub Enviar_Click()
Dim dades As Worksheet: Set dades = Sheets("dades")
Dim Taula4 As ListObject: Set Taula4 = dades.ListObjects("Taula4")
Dim Taula4Row As ListRow: Set Taula4Row = Taula4.ListRows.Add
Dim totalArt As Long: totalArt = Me.list_productes.ListCount
Dim i As Long
If FA.Value = Empty Then
FA_final.Caption = "-"
End If
If PAD.Value = Empty Then
pad_final.Caption = "-"
End If
If totalArt > 0 Then
    For i = 0 To totalArt - 1
        nF = dades.Cells(Rows.Count, 1).End(xlUp).Row + 1
        Taula4Row.Range(i + 1, 1).Value = Me.num_equipament_final.Caption
        Taula4Row.Range(i + 1, 2).Value = Me.tipologia_final.Caption
        Taula4Row.Range(i + 1, 3).Value = Me.nom_equipament_final.Caption
        Taula4Row.Range(i + 1, 4).Value = Me.municipi.Value
        Taula4Row.Range(i + 1, 5).Value = Me.comarca.Value
        Taula4Row.Range(i + 1, 6).Value = Me.Entitat.Caption
        Taula4Row.Range(i + 1, 7).Value = Me.list_productes.List(i, 3)
        Taula4Row.Range(i + 1, 8).Value = Me.FA_final.Caption
        Taula4Row.Range(i + 1, 9).Value = Me.pad_final.Caption
        Taula4Row.Range(i + 1, 10).Value = Me.list_productes.List(i, 2)
        Taula4Row.Range(i + 1, 11).Value = Me.list_productes.List(i, 0)
        Taula4Row.Range(i + 1, 12).Value = Me.list_productes.List(i, 1)
        Taula4Row.Range(i + 1, 13).Value = Me.total_pad.Caption
        Taula4Row.Range(i + 1, 14).Value = Me.data.Value
    Next i
Else
    MsgBox "No hi ha material a la llista", vbInformation, "Res..."
End If
End Sub

Andy

Hola Andy, muchas gracias de nuevo por tu ayuda. Este código me da un error en la línea:

nF = dades.Cells(Rows.Count, 1).End(xlUp).Row + 1

dice que nf no se ha definido la variable. Despues de este mensaje se me cuelga el excel y se me reinicia.

¿Qué puede ser?