parametros - hacer consultas desde excel a sql server

¿Cómo agregar parámetros a una consulta de datos externa en Excel que no se puede mostrar gráficamente?

Question

A menudo utilizo Get External Data MS Excel para crear informes simples, ejecutar consultas contra bases de datos y mostrar muy bien en Excel. Las excelentes características de Excel, como las tablas de filtro y pivote, y la interfaz familiar para los usuarios lo hacen bastante bueno para esto. Sin embargo, una limitación con Microsoft Query es que no puede agregar parámetros a las consultas que no se pueden mostrar gráficamente, lo que limita considerablemente el SQL que puede escribir.

¿Hay alguna solución al error "los parámetros no están permitidos en las consultas que no se pueden mostrar gráficamente"?

Answer 1

La interfaz de Excel para consultas de SQL Server no le permitirá tener parámetros personalizados. Una forma de evitar esto es crear una Microsoft Query genérica, luego agregar parámetros y luego pegar la consulta parametralizada en las propiedades de la conexión. Estos son los pasos detallados para Excel 2010:

1. Abrir Excel
2. Ir a la pestaña Datos
3. Desde el botón Desde otras fuentes, elija Desde Microsoft Query
4. Aparecerá la ventana "Elegir origen de datos". Elija una fuente de datos y haga clic en Aceptar .
5. El Qizard de Query
1. Elija Columna: aparecerá la ventana. El objetivo es crear una consulta genérica. Recomiendo elegir una columna de una pequeña mesa.
2. Filtrar datos: solo haga clic en Siguiente
3. Ordenar: haga clic en Siguiente
4. Finalizar: solo haz clic en Finalizar .
6. Aparecerá la ventana "Importar datos":
1. Haga clic en el botón Propiedades ...
1. Elija la pestaña Definición
2. En la sección "Texto de comando:", agregue una cláusula WHERE que incluya parámetros de Excel. Es importante agregar todos los parámetros que desee ahora. Por ejemplo, si quiero dos parámetros, podría agregar esto:
   DONDE 1 =? y 2 =?
3. Haga clic en Aceptar para volver a la ventana "Importar datos"
2. Elija Informe de tabla dinámica
3. Click OK
7. Se le pedirá que ingrese el valor de los parámetros para cada parámetro.
8. Una vez que haya ingresado los parámetros, estará en su tabla dinámica
9. Vaya a la pestaña Datos y haga clic en el botón Propiedades de conexiones
1. Haga clic en la pestaña Definición
2. En la sección "Texto de comando:", pegue en la consulta SQL real que desee con el mismo número de parámetros que definió anteriormente.
3. Haga clic en el botón Parámetros ...
1. ingrese los valores de Prompt para cada parámetro
2. Click OK
4. Haga clic en Aceptar para cerrar la ventana de propiedades
10. Felicidades, ahora tienes parámetros.

Answer 2

SÍ - la solución es guardar el libro de trabajo en un archivo XML (por ejemplo, ''XML Spreadsheet 2003'') y editar este archivo como texto en el bloc de notas. use la función "BÚSQUEDA" del bloc de notas para buscar texto de consulta y cambie sus datos a "?".

guárdelo y ábralo en Excel, intente actualizar los datos y Excel se monit sobre los parámetros.

Answer 3

Si tiene Excel 2007, puede escribir VBA para modificar las conexiones (es decir, las consultas de datos externos) en un libro y actualizar la propiedad CommandText. Si simplemente agregas ? donde desee un parámetro, la próxima vez que actualice los datos, ¡solicitará los valores para las conexiones! magia. Cuando mira las propiedades de la Conexión, el botón de Parámetros estará ahora activo y usable como es normal.

Por ejemplo, escribiría una macro, lo atravesaría en el depurador y lo haría configurar el CommandText de manera apropiada. Una vez que haya hecho esto, puede eliminar la macro; solo es un medio para actualizar la consulta.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            '' If you do have multiple connections you would want to modify  
            '' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.CommandText = "select blah from someTable where blah like ?" 
        End If
    Next
End Sub

Answer 4

Solución fácil (no se requiere VBA)

1. Haga clic derecho en la tabla, expanda el menú de contexto "Tabla", seleccione "Propiedades de datos externos"
2. Haga clic en el botón "Propiedades de conexión" (etiquetado solo con información sobre herramientas)
3. Ir a la pestaña "Definición"

Desde aquí, edite el SQL directamente agregando ''?'' donde quieras un parámetro. Funciona de la misma manera que antes, excepto que no te molesten.