Rellenar ListBox con datos de varias hojas.

hola Jose,

Mostrar la cita

serían 65530 filas por 3 columnas y por el nº de hojas,
> uffff, mucho tiempo.
> Alguna sugerencia Ivan?

pues no se lo que quieres hacer, pero cargar cientos de miles de
registros en un listbox,. uffff, pobre usuario cuando tenga
que buscar algo

yo intentaria condicionar/reducir la carga en base a algun criterio y
renovar el list segun condiciones (pero supongo que tus razones
tendras)

Mostrar la cita

With ThisWorkbook
For n = 2 To .Worksheets.Count 'Empieza el for a partir de la hoja> 2
With .Worksheets(n)
Resultado = .Range("c6:e" & .Range("b6").End(xlDown).Row +
5).Value
End With
Next
ListBox1.List = Resultado
End With
Habria que hacer "algo" que el 'Resultado' se vaya acumulando y en la
última linea cargar todos los 'Resultados' de todas las hojas, me
explico?

el problema, al menos segun mis pruebas, es que parece que no se puede
redimensionar una matriz creada a partir de un rango, al menos para
añadirle otro rango, (supongo que se podran ir añadiendo elementos
individuales)

supongo que sera porque solo se puede redimensionar la ultima
dimension de una matriz, y segun mi experiencia (y aunque mis
conclusiones pueden ser erroneas) excel asigna la 1ª dimension a las
filas y la 2º a las columnas, y al ir incrementando (redimensionando)
la matriz lo que estarias intentando (si es que no se genera
automaticamente un error) es aumentar la !º dimension

esto mismo me imagino que se podra aplicar para la propiedad list del
listbox

Mostrar la cita

Y si quizás hay que emplear RowSource?

segun mis pruebas, yo al menos no lo he conseguido (incluso si no
recuerdo mal de algunas pruebas antiguas, ni siquiera admite rangos
discontinuos de una misma hoja, aunque quizas este equivocado)

Mostrar la cita

Alguna idea?

quizas se pueda transponer el rango en el 'eter' y usar la propiedad
column del list (aunque parezca una boutade, algo de eso me esta
rondando la cabeza)

quizas rellenar una matriz o coleccion de la form habitual, pero creo
que el problema del tiempo seguiria ahi. Quizas un objeto dictionary
pero no tengo ni idea de ellos

otra posibilidad al menos creo que a mi alcance es cargar solo los
datos de una hoja y si el usuario desea seleccioar otro mas alto (no
se si tienes algun mecanismo mas de seleccion o filtrado a disposicion
del usuario [si no es asi ,,,,Ufffff...]) o el 'campo de vision del
list' llega al final/principio automaticamente recargar el list con el
contenido de la siguiente/anterior hoja

supongo que habra soluciones mucho mas eficaces, pero me temo que no
en mi mano

un saludo
Ivan

a la espera de algun experto, otra posibilidad, parecida a la anterior
y si no un poco mas chapucera, si al menos un poco mas *cara* (aunque
posiblemente mas rapida al menos durante la ejecucion posterior a la
carga) seria tener tantos listbox identicos [y en la misma posicion]
como hojas haya crgados con los registros de cada una y jugar con el
orden z

como digo, una chapuza, pero si es imprescindible

un saludo
Ivan

Hola, que tal?
Voy a hacer un pequeño resumen del problema y conclusión de este hilo.
Tengo un libro excel que está compuesto por la primera hoja con texto
y unas instrucciones para el usuario final, la segunda hoja con datos
desde la celda c5 hasta la e65536. La tercera, con datos desde la c5
hasta e60000 ~. Tengo un formulario desde donde se escriben los datos
para agragarlos a la hoja, si una hoja se llena (como fué el ejemplo
de la hoja2), entonces una macro automáticamente crea una hoja nueva e
inserta ahí el contenido de los textbox del formulario.
El formulario lo que contiene a parte de los textbox y algun label es
un ListBox que lo quería utilizar para mostrar el contenido de todos
los datos de todas las hojas (menos la primera) juntos. La verdad,
este ListBox no hace nada más que mostrar los datos para que el
usuario vea en algún sitio todos los registros ya que en este libro
SÓLO se trabaja desde el formulario, por eso si esto que pido no se
puede realmente no pasa mucho, ya haré un ListBox que sólo muestre las
coincidencias de la búsqueda de datos.

De todas formas gracias por intentarlo a los dos vale?
Ya se que "siempre" que pregunto en el foro pregunto cosas "raras",
pero bueno, que le vamos a hacer!!!
Un saludo,
jose

hola, chicos !

Mostrar la cita

... resumen del problema y conclusion de este hilo.
Tengo un libro... compuesto por la primera hoja con texto y unas instrucciones para el usuario final
la segunda hoja con datos desde la celda c5 hasta la e65536. La tercera, con datos desde la c5 hasta e60000 ~.
Tengo un formulario desde donde se escriben los datos para agragarlos a la hoja
si una hoja se llena (como... la hoja2), entonces una macro... crea una hoja nueva e inserta... el contenido de los textbox
El formulario... contiene... un ListBox que lo queria... para mostrar... todos los datos de todas las hojas (menos la primera) juntos.
La verdad, este ListBox no hace nada mas que mostrar los datos para que el usuario vea en algun sitio todos los registros
ya que en este libro SOLO se trabaja desde el formulario, por eso si esto que pido no se puede realmente no pasa mucho
ya hare un ListBox que solo muestre las coincidencias de la busqueda de datos...
Ya se que "siempre" que pregunto en el foro pregunto cosas "raras", pero bueno, que le vamos a hacer!!!

[*casi siempre*]... hay *formas* de resolver [casi] todo tipo de *problematicas* [solo que *a veces* sale mas caro *el caldo*] :))
el siguiente ejemplo hace una copia de las hojas 2 a 'n' de tu libro, elimina las 5 primeras filas y dos primeras columnas [segun tu ejemplo]
despues cada libro/hoja se guarda como archivo de texto [*.CSV] en dos *pasos*:
- el primer paso es un CSV *temporal*, y el segundo va *acumulando* los temporales a un CSV [obviamente *acumulativo*] :D

lo del *caldo caro* es porque las pruebas las realice [por demas obvio que] con muy pocas lineas de texto en cada hoja
[no quisiera *verme* acumulando un archivo de ~65000 registros por 'n' hojas ocupadas] :-((
ademas, tampoco me gustaria ser un usuario *consultando* un control de lista con ~200k registros [asumiendo 3 hojas solamente]

OJO: requiere establecer en el proyecto de macros una referencia [menu: herramientas / referencias...]
-> a la libreria: Microsoft ActiveX Data Objects x.0 Library [el x.0 corresponde a la version mas baja de los equipos donde correra]

en las pruebas utilice un UserForm1 con un ListBox1 y el siguiente codigo en el evento '_initialize'

Private Sub UserForm_Initialize()
Acumula_Registros
Carga_Lista
End Sub

y las dos macros que hacen el trabajo son las siguientes [estas en un modulo de codigo estandar/general/normal/...]
-> modifica la constante *Ruta* como corresponda a tus necesidades
corre algunas pruebas [te sugiero empezar con POCOS *elementos/filas/hojas/...* para analizar el *rendimiento*]

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

Public Const Ruta As String = "c:\documents and settings\<usuario>\mis documentos"
Public Const Archivo As String = "lista.csv"
Dim LIsta As String, Temp As String
Sub Acumula_Registros(): Application.ScreenUpdating = False
Dim n As Byte, Acumula As Integer, Registra As Integer, Agrega As String
LIsta = Ruta & "\" & Archivo: Temp = Ruta & "\" & "temp.txt"
If Dir(LIsta) <> "" Then Kill LIsta
Acumula = FreeFile: Open LIsta For Append As #Acumula
With ThisWorkbook
For n = 2 To .Worksheets.Count
If Dir(Temp) <> "" Then Kill Temp
.Worksheets(n).Copy
With ActiveWorkbook
With .Worksheets(1)
.Rows("1:5").Delete: .Columns("a:b").Delete: End With
.SaveAs Temp, xlCSV: .Close False: End With
Registra = FreeFile: Open Temp For Input As #Registra
Agrega = Input(LOF(Registra) - 2, #Registra)
Close #Registra: Print #Acumula, Agrega
Next: Close #Acumula
End With
End Sub
Sub Carga_Lista()
Dim Conexion As ADODB.Connection, _
Registros As ADODB.Recordset, Consulta As String
Consulta = "select * from " & Archivo
Set Conexion = New ADODB.Connection
Conexion.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & Ruta & ";Extensions=asc,csv,tab,txt;"
Set Registros = New ADODB.Recordset
Registros.Open Consulta, Conexion, adOpenForwardOnly, adLockReadOnly, adCmdText
With UserForm1.ListBox1
.ColumnCount = Registros.Fields.Count
.Column = Registros.GetRows: End With
Registros.Close: Set Registros = Nothing
Conexion.Close: Set Conexion = Nothing
If Dir(Temp) <> "" Then Kill Temp
If Dir(LIsta) <> "" Then Kill LIsta
End Sub

Hola, que tal?
es curioso, he estado probando lo que propones pero no funciona bien.
Lo estoy ejecutando con F8 y veo que el archivo CSV se crea
correctamente, con los datos de las hojas 2 y 3 (por hacer pruebas),
pero en la macro 'Carga_Lista' es donde está el fallo, en el listbox1
me muestra el contenido de la hoja 2 EXCEPTO la última fila con datos
que de la hoja 2 es la 65536 y la primera fila que sería la 6, el
contenido de la hoja 3 que son sólo 3 filas no me muestra ninguna.
Otra pega más es que me muestra el listbox en una columna así que los
datos están separados por comas, tendría que aparecer 3 columnas,
además aparecen de una forma muy curiosa, por ejemplo en la primera
columna tengo 123 en la segunda 212 y la tercera 456 el resultado en
el lisbox es 123,2124 raro no?

Bueno, pues aquí dejo esto vale?
Venga! un saludo y gracias a todos
jose

_propuesta de Hector_
Mostrar la cita

hola, chicos !

> ... resumen del problema y conclusion de este hilo.
> Tengo un libro... compuesto por la primera hoja con texto y unas instrucciones para el usuario final
> la segunda hoja con datos desde la celda c5 hasta la e65536. La tercera, con datos desde la c5 hasta e60000 ~.
> Tengo un formulario desde donde se escriben los datos para agragarlos a la hoja
> si una hoja se llena (como... la hoja2), entonces una macro... crea una hoja nueva e inserta... el contenido de los textbox
> El formulario... contiene... un ListBox que lo queria... para mostrar... todos los datos de todas las hojas (menos la primera) juntos.
> La verdad, este ListBox no hace nada mas que mostrar los datos para que el usuario vea en algun sitio todos los registros
> ya que en este libro SOLO se trabaja desde el formulario, por eso si esto que pido no se puede realmente no pasa mucho
> ya hare un ListBox que solo muestre las coincidencias de la busqueda de datos...
> Ya se que "siempre" que pregunto en el foro pregunto cosas "raras", pero bueno, que le vamos a hacer!!!

[*casi siempre*]... hay *formas* de resolver [casi] todo tipo de *problematicas* [solo que *a veces* sale mas caro *el caldo*] :))
el siguiente ejemplo hace una copia de las hojas 2 a 'n' de tu libro, elimina las 5 primeras filas y dos primeras columnas [segun tu ejemplo]
despues cada libro/hoja se guarda como archivo de texto [*.CSV] en dos *pasos*:
- el primer paso es un CSV *temporal*, y el segundo va *acumulando* los temporales a un CSV [obviamente *acumulativo*] :D

lo del *caldo caro* es porque las pruebas las realice [por demas obvio que] con muy pocas lineas de texto en cada hoja
[no quisiera *verme* acumulando un archivo de ~65000 registros por 'n' hojas ocupadas] :-((
ademas, tampoco me gustaria ser un usuario *consultando* un control de lista con ~200k registros [asumiendo 3 hojas solamente]

OJO: requiere establecer en el proyecto de macros una referencia [menu: herramientas / referencias...]
-> a la libreria: Microsoft ActiveX Data Objects x.0 Library [el x.0 corresponde a la version mas baja de los equipos donde correra]

en las pruebas utilice un UserForm1 con un ListBox1 y el siguiente codigo en el evento '_initialize'

Private Sub UserForm_Initialize()
Acumula_Registros
Carga_Lista
End Sub

y las dos macros que hacen el trabajo son las siguientes [estas en un modulo de codigo estandar/general/normal/...]
-> modifica la constante *Ruta* como corresponda a tus necesidades
corre algunas pruebas [te sugiero empezar con POCOS *elementos/filas/hojas/...* para analizar el *rendimiento*]

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

Public Const Ruta As String = "c:\documents and settings\<usuario>\mis documentos"
Public Const Archivo As String = "lista.csv"
Dim LIsta As String, Temp As String
Sub Acumula_Registros(): Application.ScreenUpdating = False
Dim n As Byte, Acumula As Integer, Registra As Integer, Agrega As String
LIsta = Ruta & "\" & Archivo: Temp = Ruta & "\" & "temp.txt"
If Dir(LIsta) <> "" Then Kill LIsta
Acumula = FreeFile: Open LIsta For Append As #Acumula
With ThisWorkbook
For n = 2 To .Worksheets.Count
If Dir(Temp) <> "" Then Kill Temp
.Worksheets(n).Copy
With ActiveWorkbook
With .Worksheets(1)
.Rows("1:5").Delete: .Columns("a:b").Delete: End With
.SaveAs Temp, xlCSV: .Close False: End With
Registra = FreeFile: Open Temp For Input As #Registra
Agrega = Input(LOF(Registra) - 2, #Registra)
Close #Registra: Print #Acumula, Agrega
Next: Close #Acumula
End With
End Sub
Sub Carga_Lista()
Dim Conexion As ADODB.Connection, _
Registros As ADODB.Recordset, Consulta As String
Consulta = "select * from " & Archivo
Set Conexion = New ADODB.Connection
Conexion.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & Ruta & ";Extensions=asc,csv,tab,txt;"
Set Registros = New ADODB.Recordset
Registros.Open Consulta, Conexion, adOpenForwardOnly, adLockReadOnly, adCmdText
With UserForm1.ListBox1
.ColumnCount = Registros.Fields.Count
.Column = Registros.GetRows: End With
Registros.Close: Set Registros = Nothing
Conexion.Close: Set Conexion = Nothing
If Dir(Temp) <> "" Then Kill Temp
If Dir(LIsta) <> "" Then Kill LIsta
End Sub