UTILIZAR CUADRO DE DIÁLOGO PARA SELECCIONAR ARCHIVO Y BUSCARV

Hoy vamos a ver otra forma de realizar una búsqueda con la función buscarv en VBA (fruto de una petición que me trasladó un lector).

Imaginad que tenéis una serie de ID y que cada mes necesitáis actualizar cierta información buscando datos de una carpeta donde se va generando un archivo por cada mes. Esta tarea se podría realizar de varias formas, tan solo tenemos que introducir una fórmula o proceso de búsqueda y detallar el archivo en donde debe buscar.

Sin embargo, esto requeriría modificar la cadena de conexión cada vez que necesitásemos actualizar los datos, puesto que estaría en otro archivo, o carpeta o directorio. Para salvar este problema he implementado una serie de modificaciones en una macro que solucionará este problema.

Como íbamos comentando, tenemos una serie de ID en un archivo que actualizamos cada mes:

UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV

Y tenemos una carpeta con varios archivos denominados con una fecha, cada uno se corresponde a un mes. Por ejemplo, vamos a actualizar nuestro archivo con los datos del archivo “01_01_2016”, los datos son estos y están en situados en la primera hoja:

UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV2

Ahora en el archivo con el que estamos trabajando y actualizando la información, incluimos esta macro en un módulo estándar:

Sub Buscarv()
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Definimos variables
Dim i As Double, Libro As Variant, Hoja As Variant
'Asignamos Milibro como thisworkbook
Set Milibro = ThisWorkbook
'Con mi libro en la hoja1 limpiamos el rango de datos que vamos a buscar
With Sheets(1)
fin = Application.CountA(.Range("A:A"))
.Range("B2:D" & fin).ClearContents
'Abrimos cuadro de diálogo para elegir archivo donde buscar
Archivo = Application.GetOpenFilename
'Si no seleccionamos nada salimos del proceso
If Archivo = False Then
Exit Sub
End If
'Asignamos BaseBuscar como el libro abierto
Set BaseBuscar = Workbooks.Open(Archivo)
'Obtenemos el nombre del libro
Libro = ActiveWorkbook.Name
'Obtenemos el nombre de la hoja
Hoja = Sheets(1).Name
'Volvemos a activar el libro en uso
Milibro.Activate
'Iniciamos un bucle y buscamos el valor de la primera columna de nuestro libro
'en el libro que hemos seleccionado y la hoja(1)
For i = 2 To fin
.Cells(i, 2) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0), "")
.Cells(i, 3) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0), "")
.Cells(i, 4) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0), "")
Next i
End With
'Cerramos el libro que hemos abierto para buscar
BaseBuscar.Close
End Sub

Como podéis apreciar lo primero que vais a ver es como Excel abre la ventana de diálogo de “Abrir Archivo”, es ahí cuando debéis seleccionar el archivo desde el cual queréis actualizar la información, en este caso “01_01_2016”:

UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV3

Ahora la macro detectará la ruta del archivo (ubicación), nombre del libro y nombre de la hoja, datos suficientes para aplicar la función Buscarv. (los pasos los comento en la macro):

Y este es el resultado final:

UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV4

Y para el siguiente mes, hacemos exactamente lo mismo, buscamos en la carpeta y actualizamos. De esta forma no tenemos que estar modificando el código, Excel lo hace por nosotros.

Es importante tener en cuenta que para este ejemplo, los datos a buscar están en la “hoja1”, sin embargo podéis especificar la hoja en la que se buscar modificando en la macro la siguiente línea Hoja = Sheets(1).Name

Hoja = Activesheet.NameSheets("el nombre de la hoja").Name

*  Para versiones en 2003 es necesario que modifiquéis la línea de código de buscarv, en concreto se debe a la función Application.WorksheetFunction.IfError dado que aparece en Excel a partir de la versión 2007. Para que pueda funcionar en 2003 es necesario sustituirla por un condicional y la fórmula eserror (iserror). Es decir debería estar así:

For i = 2 To fin
'Primer buscarv
If IsError(.Cells(i, 2) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0)) Then
.Cells(i, 2) = ""
Else
.Cells(i, 2) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0)
End If
'Segundo buscarv
If IsError(.Cells(i, 3) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0)) Then
.Cells(i, 3) = ""
Else
.Cells(i, 3) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0)
End If
'Tercer buscarv
If IsError(.Cells(i, 4) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0)) Then
.Cells(i, 4) = ""
Else
.Cells(i, 4) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0)
End If
Next i

Tan solo tenéis que modificar los datos y os funcionará perfectamente en todas las versiones. Para este ejemplo, lo dejo con la función .IfError, que además reduce considerablemente el código.

Para finalizar os dejo dos archivos, uno contiene la macro y el otro el archivo con la fecha, que contiene los datos a buscar, tan solo tenéis que descargarlos y realizar la prueba 🙂

Espero que os resulte de utilidad!

Descarga el archivo de ejemplo pulsando en: UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV

Descarga el archivo de ejemplo pulsando en: ARCHIVO DE BÚSQUEDA 01/01/2016

 

Anuncios

2 pensamientos en “UTILIZAR CUADRO DE DIÁLOGO PARA SELECCIONAR ARCHIVO Y BUSCARV

  1. Buenos días.

    En primer lugar quiero felicitarte por el foro. Las entradas que he leido siempre las he encontrado mas que interesantes.
    He descargado los dos archivos de ejemplo y en mi caso, no funciona.
    El programa deja de funcionar en la linea:
    “.Cells(i, 2) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range(“A2:E65000”), 2, 0), “”)” y da el error número 438 “El objeto no admite esta propiedad o método” .

    Saludos.

    Le gusta a 1 persona

  2. Hola Vicente,

    Qué versión de Excel tienes?. De todas formas te he enviado un correo con una posible solución. Si va Ok publicaré una nota para posibles incidencias.

    Saludos.

    — Editado–

    Después de haber comprobado el problema, resulta que el archivo estaba en Excel 2003, con lo que la función introducida con buscarv (iferror) propia de 2007 y versiones superiores es la que ocasionaba el error.
    Aunque sea una versión antigua, me gusta siempre que puedo que todo el código que escribo se pueda utilizar en todas las versiones, de modo que he actualizado la entrada proporcionando una solución para que funcione en 2003.

    Saludos.

    Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s