Macro en excel que al ejecutar solicite la ruta de consulta externa desde microsoft query

Su ayuda para crear una macro que al momento de ejecutar solicite la ruta de las conexiones existentes cada vez que se ejecute, tengo un libro en excel el cual obtiene datos de otros 2 libros en excel y me crean 2 tablas, en el libro tengo un botón el cual pregunta si desea actualizar conexión existente

Dim respuesta As Integer
respuesta = MsgBox("Esta seguro que desea Actualizar?", vbYesNo, "Actualizar Bases")
If respuesta = 7 Then
Exit Sub
Else
ActiveWorkbook. Connections("Consulta desde Excel Files"). Refresh
ActiveWorkbook. Connections("Consulta desde Excel Files1"). Refresh
End If

Al llevar este libro a otro pc, me toca crear de nuevo las conexiones, deseo crear una macro que el momento de ejecutar solicita la ruta de para actualizar cada conexión.

1

1 respuesta

Respuesta
1

Bueno si entiendo bien solo necesitas añadir a tu código antes de la conexión o en otro botón a parte, la parte de creación. Sería algo como:

Workbooks("Book1").Connections.AddFromFile _
 "C:\Program Files (x86)\Microsoft Office\Office12\QUERIES\My_query.iqy"
 With ActiveWorkbook.Connections("Consulta desde Excel Files")
 .name = "Consulta desde Excel Files"
 .Description = "Es mi conexion Excel"
 End With

Tendrás que adaptarlo a tus necesidades. Puedes hacer que la ruta la tome de una celda por ejemplo:

Workbooks("Book1"). Connections. AddFromFile Range("A1").Value 

siempre que A1 contenga un string o que pida pregunte por ella. Para ese caso necesitarías crear un formulario que contenga un textbox y en el botón Aceptar que ejecute el código. Para este caso sería algo como:

Workbooks("Book1"). Connections. AddFromFile TextBox1.Text

Hola prozac, gracias por tu tiempo, por favor me puedes aclarar como agrego el código,

en la parte de creación de la consulta tengo el siguiente código (obtenido del grabador de macros excel)

Sheets("PRUEBA").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Users\afelipeo\Desktop\libro1.xls;DefaultDir=C:\Users\afelipeo\Desktop;DriverId=1046;MaxBuffer" _
), Array("Size=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _etc)


estoy intentando modificar la parte de la ruta para que se actualice según la ubicación del Informe.xlsm donde ejecuto la macro, es decir, si las bases se encuentran en la misma carpeta el informe se actualizara sin importar si cambio de pc


DBQ=C:\Users\afelipeo\Desktop\libro1.xls
DefaultDir=C:\Users\afelipeo\Desktop

, intento reemplazando con

Consulta = ThisWorkbook.Path & "\Libro1.xls"
rutaConsulta= ThisWorkbook.Path

DBQ=Consulta
DefaultDir=rutaconsulta

pero no funciono. gracias

Vamos paso a paso a ver donde está error. Lo primero, recomiendo el código siguiente. Hace en teoría lo mismo que el tuyo pero tal vez sea ligeramente mejor.

Al inicio del módulo, fuera de cualquier Sub o Function escribe

Option Explicit

Esto obliga a declarar todas las variables y previene errores cuando nos equivocamos con el nombre de las mismas.

Sub AbreConexion()
Dim ruta as String
Dim Consulta As String
ruta = ThisWorkBook.Path
Range("A1").value = ruta ' escribe la ruta en A1 para poder verla al ejecutar la macro. no haria falta
'Se puede hacer al revés y que la ruta la tome del valor de la celda A1 evitando así usar el mismo directorio
Consulta = ruta & "\Libro1.xls" 
'Ahora la creación de la conexión
Sheets("PRUEBA").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=Consulta;DefaultDir=ruta;DriverId=1046;MaxBuffer" _
), Array("Size=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _etc)
end sub

Y lógicamente la macro a ejecutar es AbreConexion o el nombre que hayas escogido. Ten en cuenta que para que esto pueda funcionar el libro que contiene la macro no puede ser un libro nuevo. Debe guardarse antes con un nombre. De lo contrario el path vale "" y no funciona.

Si sigue sin funcionar, puedes entrar el en editor (Alt+F11), situarte en cualquier punto del procedimiento y pulsar F8 para ejecutar paso a paso. Ahí puedes ver los valores de las variables (situando el cursor encima) y donde da el error.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas