copiar datos de varios archivos xls

hola de nuevo Juan

permiteme una disculpa

tras echar un ojo a la 'consolidacion' en excel creo que puedo haber
metido la pata hasta el fondo, que diriamos por aqui.

aunque en lo que a mi respecta todo lo dicho es valido (no consigo
entender tu consulta), es muy posible que se deba a mi desconocimiento
del tema.

lo dicho, disculpa si he dicho alguna tonteria


un saludo
Ivan

PD: de todas formas lo de los datos concretos y claros creo que no
sobra

Muchas gracias Ivan.

Tienes razon, no dejo en claro exactamente lo que trato de hacer.

1. cuento con tres archivos (detalle1, detalle2, detalle3), ya que son
sucursales que capturan informacion. cada uno de los archivos cuenta con
varias hojas, pero una sola es la que me interesa(captura), los tres
archivos cuentan con la misma estructura, asi como el mismo nombre de hoja
(Captura), el rango utilizado en dicha hoja es desde a1:a200, y el punto que
no aclare, es que va aumentando dicho rango, en filas

En un archivo nuevo ("consolidado"), en la Hoja1, busco copiar toda la
informacion de la hoja "captura" del archivo "detalle1", enseguida, la hoja
"captura" del archivo "detalle2", enseguida, copiar la hoja "captura" del
archivo "detalle3".; a esto es a lo que me refiero con consolidado.



en la Hoja2 del archivo "consolidado", quiero guardar la informacion de
direccion y nombre de dichos archivos:
a1:"c: \sucursal1\detalle1.xls"
a2:"c:\sucursal2\detalle2.xls"
a3:"c:\sucursal3\detalle3.xls"
para que al momento de actualizar la informacion del detalle, busque en
estas celdas la direccion de los archivos que hay que copiar, para en caso
de mantenimiento.
otro punto que no aclare, es que si puedo indicarle en esta misma hoja2, el
nombre de la hoja a copiar, estaria mas que bien, esto es:
b1:captura
b2:captura
b3:captura
para en dado caso, que por "x" razon o error se cambie el nombre de una de
las hojas, solo cambiarlo en las celdas antes dichas.



Esto es lo que quisiera que me ayudaran a realizar. y de antemano
muchisimas gracias.

PDTA:lo del filtro, para crear una clave unica para obtener la consulta, ya
lo tengo listo, con un mismo comentario de este foro (JC del 21/01/08)








"Ivan" escribió en el mensaje
news:
hola Juan,

Perdon por no poner mas detalle en correo anterior;<<<

espero no te molestes, pero me temo que, al menos desde mi punto de
vista, no solo es que pusiieras pocos detalles, es que la consulta era
[casi] totalmente diferente


de todas formas, no se si es que el tema va por uno derroteros que se
me escapan (consultas a bases de datos con ado o similar)o es que a lo
mejor estoy un poco espeso, pero al menos en mi caso, la consulta
queda todavia mas confusa. Creo que no acabo de comprender ninguno de
los enunciados que publicas =>

1ª) >>> se busca realizar una consulta en base a ciertos datos
capturados en celdas

b2:b7 (listas de datos que se han capturado en los archivos detalle),<<<<

¿que entiendes por 'consulta en base a ciertos datos'? ¿obtener solo
los registros coincidentes? y si fuese asi ¿coincidentes en que
campos?

por otra parte si los datos de b2:b7 'ya' se han capturado en los
'archivos detalle', no acabo de ver lo que quieres hacer que no
hubieses podido hacer durante la 'captura'

2ª) >>> mismos que estoy utilizando para obtener una clave unica,<<<

aqui la verdad es que no se que comentar ¿una clave unica en 5 celdas
para 600 (casi) registros? como supongo que no es esto, no me imagino
a que puedes referirte

3º) >>> que al conseguir obtener el consolidado de los 3 archivos <<<

tampoco acabo de entender a lo que te refieres con 'consolidado' (pude
ser culpa mia, pues nunca me he puesto a analizar dicho termino en el
contexto de excel)

4º) >>> de una sola hoja<<<

¿cada uno de los tres archivos tiene solo una hoja?

5º) >>> en particular de cada archivo, a una sola hoja<<<

¿a una hoja por archivo? si es asi, entonces ¿a tres hojas diferentes?

6º) >>>, agregandole una columna con los campos consulta
seleccionados, para establecer tambien

la clave unica.<<<

aqui me remitiria a las dudas del punto 2º

7º) >>> mostrar los registros que cumplan con la consulta o clave
unica de consulta,<<<

la verdad es que aqui, lo que me da la impresion es lo que te
comentaba en el punto 1º, que lo que realmente quieres hacer es
obtener (en los 3 archivos) los registros que coincidan con lo
introducido en b2:b7, o sea una especie de filtro. Aun siendo asi,
tampoco acabo de verlo. Tampoco su tratamiento posterior

disculpame si a lo mejor es que no estoy viendo algo evidente y el no
poder ayudarte, pero creo que convendria (tanto para mi como [creo
que] para cualquier otro, que expusieras mas claramente lo que
quieres hacer, usando para ello 'terminos de excel', es decir, rangos
de la/s lista/s con los que trabajar las diferentes opciones que
planteas (pej: 'a1:a200' que comentabas en tu primer post, buscar en
la columna 'C', añadir una nueva hoja con el 'consolidado' en tal
archivo, o añadir los registros en tal sitio, y/o cualquier cosa que
pueda ayudar a que, desde este lado, 'veamos' tu/s archivo/s lo mas
parecido posible a como son en realidad

bueno, lo dicho, siento no ser de gran ayuda y espero que alguien haya
sabido entender tus detalles

un saludo
Ivan

hola Juan,

parece que al final la cosa iba mas bien por lo que crei entender en
mi 1er mensaje

si quieres prueba esta adptacion del 1er codigo, pero antes creciorate
de=>

a) en el archivo que va a contener la macro la 1ª hoja 'real', es
decir la que seria la Hoja1 al crearlo deberia ser en la que se van a
pegar las listas y la 2ª la que contenga los nombres de archivo y
hojas

con real me refiero a que, aunque se cambien sus nombres en la
interfaze del usuario (los de las pestañas de las hojas) su CodeName
si debe de ser 'Hoja1' y 'Hoja2' respectivamente

(el codename puedes verlo si vas al editor de vba (alt+F11) y miras en
el explorador de proyectos (control+R si no esta visible), en los
'Objetos de excel' veras que los nombres de los modulos de las hojas
aparecen +/- asi => 'HojaNº(NombreDeLaHoja)' donde NombreDeLaHoja
puede ser tambien 'HojaNº' o el qeu aparezca en la pestaña de la hoja
si se le ha cambiado. El codename es el que va antes de los
parentesis)

el 2º requisito es que las cosas sean como expones, es decir que en la
Hoja2 pongas en la columna A y a partir de A1 (incluida) la ruta
completa(incluido el nombre) y en la B, tambien a partir de B1
incluido, el nombre de la hoja correspondiente a la hoja de la columna
A

puedes poner tantos archivos como quieras para 'consolidar', pero en
la columna A no debe de haber nada en la fila inmediatamente siguiente
al ultimo nombre/ruta de archivo

lo demas seria simplemante asegurarte de que los nombres rutas y hojas
son correctos (de todas formas incluye un control de errores, que si
quieres probarlo puedes poner alguno de los datos erroneamente.)

posiblemente convenga controlar alguna circunstancia/imprevisto mas

OJO: la macro no 'consolida' en terminos de excel, ni comprueba la
posible existencia de datos duplicados. Simplemente recopila el
contenido de tantos archivos/hojas como le pongas en la Hoja2, en una
sola hoja (Hoja1) (atencion, cada vez borra lo anterior, aunque no
antes de verificar que no hay errores)

bueno, este es el codigo (esta hecho un poco a la carrera y
posiblemente sea muy mejorable, pero parece no ir mal)

pegalo en un modulo 'normal' (no de Hoja, ni en Thisworkbook, ni de
Clase) y prueba a llamarlo desde la hoja 1

''--copia desde aqui
Sub ResumenLibros()
Dim n As Byte, msj As String, ref As String, _
hj As Worksheet, Abierto As Boolean
Application.ScreenUpdating = False
With ThisWorkbook
.Worksheets.Add Before:=.Worksheets(1)
Set hj = .Worksheets(1)
For n = 1 To Hoja2.[a1].End(xlDown).Row
Abierto = LibroAbierto(Hoja2.Cells(n, 1).Value)
On Error Resume Next
If Not Abierto Then
Workbooks.Open Hoja2.Cells(n, 1).Value
Else
Workbooks(Hoja2.Cells(n, 1).Value).Activate
End If
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 1).Address(0, 0)
msj = "el archivo "
GoTo salir
Else
With ActiveWorkbook.Worksheets(Hoja2.Cells(n, 2).Value)
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 2).Address(0, 0)
msj = "la hoja "
If Not Abierto Then ActiveWorkbook.Close False
GoTo salir
Else
If hj.[a1] = "" Then .Rows(1).Copy hj.[a1]
.Range("a2:z" & .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
_
.Copy hj.Cells(hj.Rows.Count, 1).End(xlUp).Offset(1)
.Parent.Close False
End If
End With
End If
On Error GoTo 0
Next
Hoja1.Columns.Clear
hj.[a1].CurrentRegion.Copy Hoja1.[a1]
' Hoja1.Name = "Resumen_" & Format(Now, "d-mmm-yy_hmmss")
.Save
End With
GoTo salir2
salir:
MsgBox "No se puede encontrar " & msj & _
"""" & Hoja2.Range(ref) & """" & vbCr & vbCr & _
"Comprueba que son correctos los datos introducidos en la celda "
_
& ref & " de la hoja " & Hoja2.Name & vbCr & _
" y/o que no se ha movido o eliminado.", vbCritical, _
"No se encuentra " & msj
salir2:
Application.DisplayAlerts = False
hj.Delete
Application.DisplayAlerts = True
Set hj = Nothing
End Sub
''-
Function LibroAbierto(NombreLibro As String) As Boolean
If InStr(NombreLibro, "\") > 0 Then NombreLibro = _
Right(NombreLibro, Len(NombreLibro) - InStrRev(NombreLibro, "\"))
On Error Resume Next
LibroAbierto = Workbooks(NombreLibro).Sheets.Count > 0
End Function
''-copia hasta aqui -

NOTA: la instruccion que esta como comentario es por si quisieras
actualizar el nombre de la Hoja1 con el momento en que haces la
actualizacion. Para usarla solo tienes que quitar la comilla simple/
apostrofe que hay al principio de la linea. Si no la vas a usar puedes
eliminarla

ten cuidado en que los codigos queden igual que los ves en el foro
(guiones de continuacion de linea incluidos)

espero te ayude y si quieres comentas

un saludo
Ivan

hola Juan,

parece que al final la cosa iba mas bien por lo que crei entender en
mi 1er mensaje


si quieres prueba esta adptacion del 1er codigo, pero antes
creciorate
de=>


a) en el archivo que va a contener la macro la 1ª hoja 'real', es
decir la que seria la Hoja1 al crearlo deberia ser en la que se van a
pegar las listas y la 2ª la que contenga los nombres de archivo y
hojas


con real me refiero a que, aunque se cambien sus nombres en la
interfaze del usuario (los de las pestañas de las hojas) su CodeName
si debe de ser 'Hoja1' y 'Hoja2' respectivamente


(el codename puedes verlo si vas al editor de vba (alt+F11) y miras
en
el explorador de proyectos (control+R si no esta visible), en los
'Objetos de excel' veras que los nombres de los modulos de las hojas
aparecen +/- asi => 'HojaNº(NombreDeLaHoja)' donde NombreDeLaHoja
puede ser tambien 'HojaNº' o el qeu aparezca en la pestaña de la hoja
si se le ha cambiado. El codename es el que va antes de los
parentesis)


el 2º requisito es que las cosas sean como expones, es decir que en
la
Hoja2 pongas en la columna A y a partir de A1 (incluida) la ruta
completa(incluido el nombre) y en la B, tambien a partir de B1
incluido, el nombre de la hoja correspondiente a la hoja de la
columna
A


puedes poner tantos archivos como quieras para 'consolidar', pero en
la columna A no debe de haber nada en la fila inmediatamente
siguiente
al ultimo nombre/ruta de archivo


lo demas seria simplemante asegurarte de que los nombres rutas y
hojas
son correctos (de todas formas incluye un control de errores, que si
quieres probarlo puedes poner alguno de los datos erroneamente.)


posiblemente convenga controlar alguna circunstancia/imprevisto mas


OJO: la macro no 'consolida' en terminos de excel, ni comprueba la
posible existencia de datos duplicados. Simplemente recopila el
contenido de tantos archivos/hojas como le pongas en la Hoja2, en una
sola hoja (Hoja1) (atencion, cada vez borra lo anterior, aunque no
antes de verificar que no hay errores)


bueno, este es el codigo (esta hecho un poco a la carrera y
posiblemente sea muy mejorable, pero parece no ir mal)


pegalo en un modulo 'normal' (no de Hoja, ni en Thisworkbook, ni de
Clase) y prueba a llamarlo desde la hoja 1


''--copia desde aqui
Sub ResumenLibros()
Dim n As Byte, msj As String, ref As String, _
hj As Worksheet, Abierto As Boolean
Application.ScreenUpdating = False
With ThisWorkbook
.Worksheets.Add Before:=.Worksheets(1)
Set hj = .Worksheets(1)
For n = 1 To Hoja2.[a1].End(xlDown).Row
Abierto = LibroAbierto(Hoja2.Cells(n, 1).Value)
On Error Resume Next
If Not Abierto Then
Workbooks.Open Hoja2.Cells(n, 1).Value
Else
Workbooks(Hoja2.Cells(n, 1).Value).Activate
End If
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 1).Address(0, 0)
msj = "el archivo "
GoTo salir
Else
With ActiveWorkbook.Worksheets(Hoja2.Cells(n, 2).Value)
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 2).Address(0, 0)
msj = "la hoja "
If Not Abierto Then ActiveWorkbook.Close False
GoTo salir
Else
If hj.[a1] = "" Then .Rows(1).Copy hj.[a1]
.Range("a2:z" & .Cells(.Rows.Count, 1) _
.End(xlUp).Row + 1).Copy hj.Cells(hj.Rows _
.Count, 1).End(xlUp).Offset(1)
.Parent.Close False
End If
End With
End If
On Error GoTo 0
Next
Hoja1.Columns.Clear
hj.[a1].CurrentRegion.Copy Hoja1.[a1]
' Hoja1.Name = "Resumen_" & Format(Now, "d-mmm-yy_hmmss")
.Save
End With
GoTo salir2
salir:
MsgBox "No se puede encontrar " & msj & _
"""" & Hoja2.Range(ref) & """" & vbCr & vbCr & _
"Comprueba que es correcto el dato introducido en la celda " _
& ref & " de la hoja " & Hoja2.Name & vbCr & _
" y/o que no se ha movido o eliminado.", vbCritical, _
"No se encuentra " & msj
salir2:
Application.DisplayAlerts = False
hj.Delete
Application.DisplayAlerts = True
Set hj = Nothing
End Sub
''-
Function LibroAbierto(NombreLibro As String) As Boolean
If InStr(NombreLibro, "\") > 0 Then NombreLibro = _
Right(NombreLibro, Len(NombreLibro) - InStrRev(NombreLibro, "\"))
On Error Resume Next
LibroAbierto = Workbooks(NombreLibro).Sheets.Count > 0
End Function
''-copia hasta aqui -


NOTA: la instruccion que esta como comentario es por si quisieras
actualizar el nombre de la Hoja1 con el momento en que haces la
actualizacion. Para usarla solo tienes que quitar la comilla simple/
apostrofe que hay al principio de la linea. Si no la vas a usar
puedes
eliminarla


ten cuidado en que los codigos queden igual que los ves en el foro
(guiones de continuacion de linea incluidos)


espero te ayude y si quieres comentas


un saludo
Ivan


PD: he eliminado el anterior mensaje para quitar los trunques que
habian aparecido en el editor del foro

Muchisimas gracias..

En verdad que me has dado una gran ayuda..

sabes... una cosa mas...que como dices, no supe aclarar desde el
inicio...

si quiero que estos datos se copien como valores, con los formatos,
es decir, el pegado especial de formatos, y el pegado especial de valores,
para que lo que sea formula, no me afecte por cuestiones de errores por no
encontrar tablas

De antemano muchisimas gracias.

PDTA: perdon la tardanza en corroborar el correcto funcionamiento, es solo
que carecia de internet




"Ivan" escribió en el mensaje
news:
hola Juan,

parece que al final la cosa iba mas bien por lo que crei entender en
mi 1er mensaje


si quieres prueba esta adptacion del 1er codigo, pero antes
creciorate
de=>


a) en el archivo que va a contener la macro la 1ª hoja 'real', es
decir la que seria la Hoja1 al crearlo deberia ser en la que se van a
pegar las listas y la 2ª la que contenga los nombres de archivo y
hojas


con real me refiero a que, aunque se cambien sus nombres en la
interfaze del usuario (los de las pestañas de las hojas) su CodeName
si debe de ser 'Hoja1' y 'Hoja2' respectivamente


(el codename puedes verlo si vas al editor de vba (alt+F11) y miras
en
el explorador de proyectos (control+R si no esta visible), en los
'Objetos de excel' veras que los nombres de los modulos de las hojas
aparecen +/- asi => 'HojaNº(NombreDeLaHoja)' donde NombreDeLaHoja
puede ser tambien 'HojaNº' o el qeu aparezca en la pestaña de la hoja
si se le ha cambiado. El codename es el que va antes de los
parentesis)


el 2º requisito es que las cosas sean como expones, es decir que en
la
Hoja2 pongas en la columna A y a partir de A1 (incluida) la ruta
completa(incluido el nombre) y en la B, tambien a partir de B1
incluido, el nombre de la hoja correspondiente a la hoja de la
columna
A


puedes poner tantos archivos como quieras para 'consolidar', pero en
la columna A no debe de haber nada en la fila inmediatamente
siguiente
al ultimo nombre/ruta de archivo


lo demas seria simplemante asegurarte de que los nombres rutas y
hojas
son correctos (de todas formas incluye un control de errores, que si
quieres probarlo puedes poner alguno de los datos erroneamente.)


posiblemente convenga controlar alguna circunstancia/imprevisto mas


OJO: la macro no 'consolida' en terminos de excel, ni comprueba la
posible existencia de datos duplicados. Simplemente recopila el
contenido de tantos archivos/hojas como le pongas en la Hoja2, en una
sola hoja (Hoja1) (atencion, cada vez borra lo anterior, aunque no
antes de verificar que no hay errores)


bueno, este es el codigo (esta hecho un poco a la carrera y
posiblemente sea muy mejorable, pero parece no ir mal)


pegalo en un modulo 'normal' (no de Hoja, ni en Thisworkbook, ni de
Clase) y prueba a llamarlo desde la hoja 1


''--copia desde aqui
Sub ResumenLibros()
Dim n As Byte, msj As String, ref As String, _
hj As Worksheet, Abierto As Boolean
Application.ScreenUpdating = False
With ThisWorkbook
.Worksheets.Add Before:=.Worksheets(1)
Set hj = .Worksheets(1)
For n = 1 To Hoja2.[a1].End(xlDown).Row
Abierto = LibroAbierto(Hoja2.Cells(n, 1).Value)
On Error Resume Next
If Not Abierto Then
Workbooks.Open Hoja2.Cells(n, 1).Value
Else
Workbooks(Hoja2.Cells(n, 1).Value).Activate
End If
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 1).Address(0, 0)
msj = "el archivo "
GoTo salir
Else
With ActiveWorkbook.Worksheets(Hoja2.Cells(n, 2).Value)
If Err.Number <> 0 Then
ref = Hoja2.Cells(n, 2).Address(0, 0)
msj = "la hoja "
If Not Abierto Then ActiveWorkbook.Close False
GoTo salir
Else
If hj.[a1] = "" Then .Rows(1).Copy hj.[a1]
.Range("a2:z" & .Cells(.Rows.Count, 1) _
.End(xlUp).Row + 1).Copy hj.Cells(hj.Rows _
.Count, 1).End(xlUp).Offset(1)
.Parent.Close False
End If
End With
End If
On Error GoTo 0
Next
Hoja1.Columns.Clear
hj.[a1].CurrentRegion.Copy Hoja1.[a1]
' Hoja1.Name = "Resumen_" & Format(Now, "d-mmm-yy_hmmss")
.Save
End With
GoTo salir2
salir:
MsgBox "No se puede encontrar " & msj & _
"""" & Hoja2.Range(ref) & """" & vbCr & vbCr & _
"Comprueba que es correcto el dato introducido en la celda " _
& ref & " de la hoja " & Hoja2.Name & vbCr & _
" y/o que no se ha movido o eliminado.", vbCritical, _
"No se encuentra " & msj
salir2:
Application.DisplayAlerts = False
hj.Delete
Application.DisplayAlerts = True
Set hj = Nothing
End Sub
''-
Function LibroAbierto(NombreLibro As String) As Boolean
If InStr(NombreLibro, "\") > 0 Then NombreLibro = _
Right(NombreLibro, Len(NombreLibro) - InStrRev(NombreLibro, "\"))
On Error Resume Next
LibroAbierto = Workbooks(NombreLibro).Sheets.Count > 0
End Function
''-copia hasta aqui -


NOTA: la instruccion que esta como comentario es por si quisieras
actualizar el nombre de la Hoja1 con el momento en que haces la
actualizacion. Para usarla solo tienes que quitar la comilla simple/
apostrofe que hay al principio de la linea. Si no la vas a usar
puedes
eliminarla


ten cuidado en que los codigos queden igual que los ves en el foro
(guiones de continuacion de linea incluidos)


espero te ayude y si quieres comentas


un saludo
Ivan


PD: he eliminado el anterior mensaje para quitar los trunques que
habian aparecido en el editor del foro