Interactuar con varios xls

Hola Sergio,

Mas preguntas:

1) ?Cuantos ficheros (planillas) habra en total?
2) ?Cuantas hojas de cada una de las planillas llevaran los datos q se deben
evaluar? O solo es la primera hoja en todas?
3) ?Desde el punto de vista del procedimiento normal de trabajo (workflow),
es posible consolidar todas las planillas en una sola copiando las hojas
enteras, o se tiene q te trabajar con varios ficheros necesariamente? Si se
pudiera consolidar las planillas, las macros (o incluso formulas) serian
mucho mas simples.

Saludos,
KL


"SG Web Design - Sergio Gattelet" wrote in message
news:%

Gracias por la rapidez ...

1. Al decir planillas me refiero a cada xls (decime si estoy en lo
correcto), estos estaran ubicados en un directorio particular y entonces
se
tendra que hacer una lectura de los xls dentro de ese directorio para
luego
poder hacer la consulta en conjunto.

2. La idea es que los ids de los articulos por cada planilla sean unicos,
aun no cuento con esa informacion, pero suponiendo que vienen de
diferentes
proveedores ;) de todos modos se debera generar un codigo unico conformado
por un id de proveedor + el id del articulo para resolver este posible
inconveniente.

3. Si, seguramente pero cada uno de ellos tendra un codigo diferente.

Sergio

"KL" escribió en el mensaje
news:

Hola de nuevo,

Un par de preguntas:

1) Cuando hablas de "planillas" a que te refieres exactamente? A hojas o
ficheros (libros)?

2) Los IDs de los articulos supongo que son unicos. ?Puede haber mas de
un
articulos repetidos (con el mismo ID) en una planilla?

3) ?Puede haber mas de un articulos repetidos en diferentes planillas?

Saludos,
KL

"SG Web Design - Sergio Gattelet" wrote in message
news:%23VniO$
> Hola KL,
>
> No dispongo aun las listas de precios, pero para que tengas una idea
> cuentan
> con por lo menos 3 campos: id - descripcion - precio . Estos son los
> campos
> con los que voy a trabajar en cada una de los xls. Como decia al

principio

> el primer paso seria adaptar cada planilla, renombrando estos 3 campos
> para
> que los nombres de los mismos en las distintas planillas coincidan y
> entonces que la busqueda por estos se correspondan y asi obtener un
> listado
> gral, resultado de la seleccion que se realizo.
>
> Este resultado seria mostrar un listado con los mismos 3 campos,
> simplemente. Con este listado se podra decidir cual de los elementos es

el

> mas conveniente de acuerdo al precio.
>
> Un caso de ejemplo es poder buscar por el campo "descripcion" la
> palabra
> "volante" y obtener como resultado todos los articulos de las distintas
> planillas que contengan esa descripcion. O bien introducir un codigo
> especifico de un articulo y obtener solo ese articulo con la
> descripcion

y

> el precio.
>
> Espero te sirva esta info y aguardo tu respuesta. Saludos.
> Sergio
>
> "KL" escribió en el mensaje
> news:
>> Hola Sergio,
>>
>> A fin de que podamos ofrecerte una solucion concreta, tendras q

explicar

> con
>> mas detalle la estructura y otras caracteristicas (tambin como no una
>> muestra de los datos reales) de las bases de datos que manejas asi
>> como
>> el
>> resultado deseado.
>>
>> Saludos,
>> KL
>>
>> "SG Web Design - Sergio Gattelet" wrote in message
>> news:
>> > Hola Grupo,
>> > gracias de antemano
>> >
>> > Necesito interactuar con varias planillas de calculo que contienen
> listas
>> > de
>> > precios de piezas del automovil. La idea es de poder realizar una
> busqueda
>> > por un campo determinado y que realice la busqueda en cada uno de
>> > los
> .xls
>> > y
>> > me muestre el listado correspondiente.
>> >
>> > Suponiendo que todas las planillas contengan los mismos nombres de
> campos
>> > (columnas), ya que de no ser asi se las cambiariamos manualmente
>> > para
> que
>> > coincidieran en la busqueda. Para mas datos estas listas (.xls) son
>> > provistas por cada uno de los proveedores del local de venta de
> respuestos
>> > automotrices y la idea es automatizar el proceso ya que se pierde
>> > demasiado
>> > tiempo en buscar manualmente en cada uno de ellos un articulo
> determinado.
>> >
>> > Espero sus respuestas.
>> > Gracias nuevamente.
>> > Sergio Gattelet
>> >
>> >
>> >
>> >
>>
>>
>
>

Hola KL,

1. La cantidad de ficheros puede variar, es mas creo que los proveedores
para estos negocios de respuestos automotrices es muy variada. Es de suponer
que cada xls (planilla) sea de un proveedor en particular y su composicion
depende exclusivamente de ellos, es decir no le podemos solicitar que nos
mande una planilla diseñada como nosotros las necesitamos, eso seria
imposible. Por eso la idea de poder realizar un servicio de mantenimiento
mensual justamente para que las nuevas listas de precios antes de ser
indexada por este proyecto, la adaptemos a nuestras necesidades.

2. No lo se, pero creeria que seria una sola. De no ser asi la adapatariamos
manualmente.

3. Es posible consolidar las planillas en una sola, haciendolo manualmente
pero no seria lo mas conveniente ya que seria mas limpio manejarlas por
separado.

Estube pensando como desarrollarlo sin conocer el VBA ya que hace mas de 6
años que no toco nada de codigo de este tipo de aplicaciones. Pero quiero
decirte que manejaba bastante bien VB, solo que me he olvidado por falta de
practica. Bueno la idea seria:

Crear un xls con 3 hojas,
. hoja 1 tendria un formulario de consulta que lo resolveremos a lo ultimo.
. hoja 2 tendra un boton escondido referido por el nombre que se le pondra.
La funcion de este boton es de leer los xls de un directorio especifico
(todos estos previamente adaptados) e indexarlos en la misma hoja. Al
finalizar este proceso grabar un reporte en la hoja 3.
. hoja 3 es un reporte que agregara una fila en la posicion "Fila 2" (para
mantener siempre el ultimo reporte arriba) con los siguientes datos: fecha -
tiempo del proceso de indexacion de listados - cantidad de registros.

Sin conocer como procesar todo esto, no creo que sea necesario trabajar con
ADO o DAO como me habias solicitado anteriormente.
Lo primero que deberiamos pensar seria en la adaptacion de cada xls es decir
mantener los siguientes parametros:
Creo que la manera mas apropiada seria adaptar cada xls respetando la misma
columna para el mismo campo. Es decir que el codigo de articulo lo
ubicaremos en la columna "B"; la descripcion en la columna "C"; el precio en
la columna "D", estos son unicamente los campos que nos interesa. Lo segundo
a considerar es de unificar el listado del proveedor a que solo contenga una
sola hoja (la primera). Tambien no podemos descartar que las planillas no
tengan titulos, los cuales eliminaremos manualmente o bien por medio de
codigo corroborando que el registro al indexarse este completo, es decir
tenga su codigo - descripcion y precio, de lo contrario que no lo indexe.

Dato adicional:
Al indexar todas las planillas habria que conformar un codigo de proveedor,
este estara ubicado en la primer columna "A" y estara conformada por el
siguiente rango "001 ... 999", es decir la primer planilla seria 001.xls que
corresponderia a "pepe". Las correspondencias entre el nombre del proveedor
y el nombre de planilla seria llevada en un txt de manera privada.

Esto es lo que se me ocurre, contame como lo ves vos.
Espero tus comentarios.

Sergio




"KL" escribió en el mensaje
news:

Hola Sergio,

Mas preguntas:

1) ?Cuantos ficheros (planillas) habra en total?
2) ?Cuantas hojas de cada una de las planillas llevaran los datos q se

deben

evaluar? O solo es la primera hoja en todas?
3) ?Desde el punto de vista del procedimiento normal de trabajo

(workflow),

es posible consolidar todas las planillas en una sola copiando las hojas
enteras, o se tiene q te trabajar con varios ficheros necesariamente? Si

se

pudiera consolidar las planillas, las macros (o incluso formulas) serian
mucho mas simples.

Saludos,
KL


"SG Web Design - Sergio Gattelet" wrote in message
news:%
> Gracias por la rapidez ...
>
> 1. Al decir planillas me refiero a cada xls (decime si estoy en lo
> correcto), estos estaran ubicados en un directorio particular y entonces
> se
> tendra que hacer una lectura de los xls dentro de ese directorio para
> luego
> poder hacer la consulta en conjunto.
>
> 2. La idea es que los ids de los articulos por cada planilla sean

unicos,

> aun no cuento con esa informacion, pero suponiendo que vienen de
> diferentes
> proveedores ;) de todos modos se debera generar un codigo unico

conformado

> por un id de proveedor + el id del articulo para resolver este posible
> inconveniente.
>
> 3. Si, seguramente pero cada uno de ellos tendra un codigo diferente.
>
> Sergio
>
> "KL" escribió en el mensaje
> news:
>> Hola de nuevo,
>>
>> Un par de preguntas:
>>
>> 1) Cuando hablas de "planillas" a que te refieres exactamente? A hojas

o

>> ficheros (libros)?
>>
>> 2) Los IDs de los articulos supongo que son unicos. ?Puede haber mas de
>> un
>> articulos repetidos (con el mismo ID) en una planilla?
>>
>> 3) ?Puede haber mas de un articulos repetidos en diferentes planillas?
>>
>> Saludos,
>> KL
>>
>> "SG Web Design - Sergio Gattelet" wrote in message
>> news:%23VniO$
>> > Hola KL,
>> >
>> > No dispongo aun las listas de precios, pero para que tengas una idea
>> > cuentan
>> > con por lo menos 3 campos: id - descripcion - precio . Estos son los
>> > campos
>> > con los que voy a trabajar en cada una de los xls. Como decia al
> principio
>> > el primer paso seria adaptar cada planilla, renombrando estos 3

campos

>> > para
>> > que los nombres de los mismos en las distintas planillas coincidan y
>> > entonces que la busqueda por estos se correspondan y asi obtener un
>> > listado
>> > gral, resultado de la seleccion que se realizo.
>> >
>> > Este resultado seria mostrar un listado con los mismos 3 campos,
>> > simplemente. Con este listado se podra decidir cual de los elementos

es

> el
>> > mas conveniente de acuerdo al precio.
>> >
>> > Un caso de ejemplo es poder buscar por el campo "descripcion" la
>> > palabra
>> > "volante" y obtener como resultado todos los articulos de las

distintas

>> > planillas que contengan esa descripcion. O bien introducir un codigo
>> > especifico de un articulo y obtener solo ese articulo con la
>> > descripcion
> y
>> > el precio.
>> >
>> > Espero te sirva esta info y aguardo tu respuesta. Saludos.
>> > Sergio
>> >
>> > "KL" escribió en el mensaje
>> > news:
>> >> Hola Sergio,
>> >>
>> >> A fin de que podamos ofrecerte una solucion concreta, tendras q
> explicar
>> > con
>> >> mas detalle la estructura y otras caracteristicas (tambin como no

una

>> >> muestra de los datos reales) de las bases de datos que manejas asi
>> >> como
>> >> el
>> >> resultado deseado.
>> >>
>> >> Saludos,
>> >> KL
>> >>
>> >> "SG Web Design - Sergio Gattelet" wrote in

message

>> >> news:
>> >> > Hola Grupo,
>> >> > gracias de antemano
>> >> >
>> >> > Necesito interactuar con varias planillas de calculo que contienen
>> > listas
>> >> > de
>> >> > precios de piezas del automovil. La idea es de poder realizar una
>> > busqueda
>> >> > por un campo determinado y que realice la busqueda en cada uno de
>> >> > los
>> > .xls
>> >> > y
>> >> > me muestre el listado correspondiente.
>> >> >
>> >> > Suponiendo que todas las planillas contengan los mismos nombres de
>> > campos
>> >> > (columnas), ya que de no ser asi se las cambiariamos manualmente
>> >> > para
>> > que
>> >> > coincidieran en la busqueda. Para mas datos estas listas (.xls)

son

>> >> > provistas por cada uno de los proveedores del local de venta de
>> > respuestos
>> >> > automotrices y la idea es automatizar el proceso ya que se pierde
>> >> > demasiado
>> >> > tiempo en buscar manualmente en cada uno de ellos un articulo
>> > determinado.
>> >> >
>> >> > Espero sus respuestas.
>> >> > Gracias nuevamente.
>> >> > Sergio Gattelet
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Hola Sergio,

Prueba el codigo q te pongo abajo. Tal como te decia sigo creyendo q la
mejor solucion es mediante llamadas ADO. Este codigo lo he escrito a partir
del procedimiento expuesto aqui:
http://www.erlandsendata.no/english...dacwbdbado

Como usarlo:
- Asegurate q todos los ficheros q serviran como base de datos respondan a
las siguientes condiciones:
1. que esten en la misma carpeta
2. que tengan los datos en la primera hoja y que esta se llame igual en
todos los ficheros
3. que los campos q vas a filtrar tengan exactamente los mismos nombres y
esten en el mismo orden

- Copia el codigo entero a un modulo normal (q no sea de hoja, libro o
formulario, vamos, de clase) dentro del fichero a donde vas a extraer los
datos.

- Acuerdate de establecer la referencia a "Microsoft ActiveX Data Objects
x.x Object Library" antes de usar el codigo.

- Ajusta las variables Hoja, fCampo1, fCriterio1, fCampo2, fCriterio2,
fCampo3 y fCriterio3 a tus necesidades reales. Si yo fuera tu, haria una de
dos: les asignaria celdas en las q se pondrian los valores o crearia un
formulario q recogeria el input de usuario para todas estas variables.
Aunque tambien puedes modificarlas dentro del codigo. Si no quieres utilizar
las condiciones asignalas "" (vacio)

- Presta especial atencion a la sintaxis de los criterios:
para operar con valores numericos usa: "= 20" o "< 5", etc.
para las cadenas de texto usa: "= 'MiTexto'" o "LIKE 'M%'", etc.
Puedes buscar mas informacion sobre la sintaxis de SQL en la web.

- Tambien podrias crear mas criterios (usando el operador AND) dentro del
macro ExtraerDatos.

- Finalmente, para extraer los datos usa el macro ExtraerDatos. Para
seleccionar multiples ficheros desde el dialogo GetOpenFilename usa Ctrl o
Shift.

Espero te sirva.

Saludos,
KL

'Inicio Codigo
Sub GetWorksheetData(strSourceFile As String, _
strSQL As String, TargetCell As Range)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim f As Integer, r As Long

If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection

On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"DriverIdy0;ReadOnly=True;DBQ=" & strSourceFile & ";"

On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", _
vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, _
adLockReadOnly, adCmdText

On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", _
vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If

TargetCell.CopyFromRecordset rs

If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Sub ExtraerDatos()
Dim Archivo As String, Hoja As String, BD As Variant
Dim fCampo1 As String, fCampo2 As String, fCampo3 As String
Dim fCriterio1 As String, fCriterio2 As String, fCriterio3 As String
Dim CondStr1 As String, CondStr2 As String, CondStr3 As String

BD = Application.GetOpenFilename _
("Archivos Microsoft Excel (*.xls), *.xls", , _
"Seleccionar las Bases de Datos", , True)

Hoja = "Sheet1"
fCampo1 = "MONTH"
fCriterio1 = "= 'May'" 'o "LIKE 'M%'"
fCampo2 = "AMOUNT"
fCriterio2 = "> 60"
fCampo3 = ""
fCriterio3 = ""

If fCampo1 <> "" Then _
CondStr1 = " WHERE [" & fCampo1 & "] " & fCriterio1
If fCampo2 <> "" Then _
CondStr2 = " AND [" & fCampo2 & "] " & fCriterio2
If fCampo3 <> "" Then _
CondStr3 = " AND [" & fCampo3 & "] " & fCriterio3

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from recordset..."
End With
On Error GoTo ManejoErrores
For i = LBound(BD) To UBound(BD)
On Error GoTo 0
With ThisWorkbook.Worksheets(1)
UFila = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Archivo = BD(i)

GetWorksheetData Archivo, _
"SELECT * FROM [" & Hoja & "$]" & _
CondStr1 & CondStr2 & CondStr3, .Cells(UFila, 1)
End With
Next i
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub

ManejoErrores:
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
MsgBox "La operacion se ha cancelado."
Exit Sub
End Sub
'Fin Codigo

Hola Sergio,

Prueba el codigo q te pongo abajo. Tal como te decia sigo creyendo q la
mejor solucion es mediante llamadas ADO. Este codigo lo he escrito a partir
del procedimiento expuesto aqui:
http://www.erlandsendata.no/english...dacwbdbado

Como usarlo:
- Asegurate q todos los ficheros q serviran como base de datos respondan a
las siguientes condiciones:
1. que esten en la misma carpeta
2. que tengan los datos en la primera hoja y que esta se llame igual en
todos los ficheros
3. que los campos q vas a filtrar tengan exactamente los mismos nombres y
esten en el mismo orden

- Copia el codigo entero a un modulo normal (q no sea de hoja, libro o
formulario, vamos, de clase) dentro del fichero a donde vas a extraer los
datos.

- Acuerdate de establecer la referencia a "Microsoft ActiveX Data Objects
x.x Object Library" antes de usar el codigo.

- Ajusta las variables Hoja, fCampo1, fCriterio1, fCampo2, fCriterio2,
fCampo3 y fCriterio3 a tus necesidades reales. Si yo fuera tu, haria una de
dos: les asignaria celdas en las q se pondrian los valores o crearia un
formulario q recogeria el input de usuario para todas estas variables.
Aunque tambien puedes modificarlas dentro del codigo. Si no quieres utilizar
las condiciones asignalas "" (vacio)

- Presta especial atencion a la sintaxis de los criterios:
para operar con valores numericos usa: "= 20" o "< 5", etc.
para las cadenas de texto usa: "= 'MiTexto'" o "LIKE 'M%'", etc.
Puedes buscar mas informacion sobre la sintaxis de SQL en la web.

- Tambien podrias crear mas criterios (usando el operador AND) dentro del
macro ExtraerDatos.

- Finalmente, para extraer los datos usa el macro ExtraerDatos. Para
seleccionar multiples ficheros desde el dialogo GetOpenFilename usa Ctrl o
Shift.

Espero te sirva.

Saludos,
KL

'Inicio Codigo
Sub GetWorksheetData(strSourceFile As String, _
strSQL As String, TargetCell As Range)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim f As Integer, r As Long

If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection

On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"DriverIdy0;ReadOnly=True;DBQ=" & strSourceFile & ";"

On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", _
vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, _
adLockReadOnly, adCmdText

On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", _
vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If

TargetCell.CopyFromRecordset rs

If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Sub ExtraerDatos()
Dim Archivo As String, Hoja As String, BD As Variant
Dim fCampo1 As String, fCampo2 As String, fCampo3 As String
Dim fCriterio1 As String, fCriterio2 As String, fCriterio3 As String
Dim CondStr1 As String, CondStr2 As String, CondStr3 As String

BD = Application.GetOpenFilename _
("Archivos Microsoft Excel (*.xls), *.xls", , _
"Seleccionar las Bases de Datos", , True)

Hoja = "Sheet1"
fCampo1 = "MONTH"
fCriterio1 = "= 'May'" 'o "LIKE 'M%'"
fCampo2 = "AMOUNT"
fCriterio2 = "> 60"
fCampo3 = ""
fCriterio3 = ""

If fCampo1 <> "" Then _
CondStr1 = " WHERE [" & fCampo1 & "] " & fCriterio1
If fCampo2 <> "" Then _
CondStr2 = " AND [" & fCampo2 & "] " & fCriterio2
If fCampo3 <> "" Then _
CondStr3 = " AND [" & fCampo3 & "] " & fCriterio3

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from recordset..."
End With
On Error GoTo ManejoErrores
For i = LBound(BD) To UBound(BD)
On Error GoTo 0
With ThisWorkbook.Worksheets(1)
UFila = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Archivo = BD(i)

GetWorksheetData Archivo, _
"SELECT * FROM [" & Hoja & "$]" & _
CondStr1 & CondStr2 & CondStr3, .Cells(UFila, 1)
End With
Next i
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub

ManejoErrores:
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
MsgBox "La operacion se ha cancelado."
Exit Sub
End Sub
'Fin Codigo

Hola KL
Gracias por la info, la voy a poner en practica y en cuanto tenga alguna
novedad te aviso.
Saludos,
Sergio