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 Respuesta
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 WithTendrá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 subY 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.
