Trabajar con tablas dinamicas en excel 2013 #1

Siguiendo con la serie de tutoriales aquí les dejo un video de como trabajar con tablas dinámicas en excel 2013, no varia casi nada en cuanto a otras versiones anteriores de excel.

Posteriormente estaré subiendo la segunda parte de este tutorial ya que el tiempo ha quedado corto.
Espero lo disfruten .

Mover la barra de desplazamiento SmallScroll usando excel vba

En excel existe un código para mover la barra de desplazamiento y de esta manera podemos dirigirnos hacia cualquiera de los lados izquierda, derecha, abajo y arriba

Por ejemplo si queremos mover la barra de desplazamiento hacia 15 filas abajo debemos escribir el siguiente código:

Sub MoverAbajo()
ActiveWindow.SmallScroll Down:=15
End Sub

De igual manera podemos hacerlo para los demás lados:

Up: Hacia Arriba

Down: Hacia Abajo

ToRight: Hacia la derecha

ToLeft: Hacia la izquierda.

Una vez explicado la manera de usar este código vamos a realizar un ejemplo completo que consiste en lo siguiente:

Ejemplo:

En una hoja de nuestro libro (ver figura 1) se tiene movimientos de venta de varios meses.

Vemos que en la figura se muestra el mes enero en la columna "A". De esa manera mas abajo (No se ve en la imagen) se tiene información del mes febrero, marzo, etc.

El objetivo es seleccionar el mes en la celda B1 y que automáticamente se muestre en pantalla el movimiento de ese mes. Eso lo lograremos haciendo uso de la propiedad SmallScroll en vba.

Figura 1

Trabajando...

Aquí podrás bajar el archivo con el cual trabajaremos, cuenta con dos hojas, la "Consulta" contiene información de las ventas desde el mes de enero a abril. En la celda B1 se encuentra una lista desplegable con validación de datos, el cual contiene los meses de enero a diciembre que servirá para mostrar las ventas de un determinado mes que elija el usuario.

Se tiene también la hoja "Meses" el cual contiene los meses de enero a diciembre que se usa en la hoja consulta.

Bien entonces vamos a presionar las teclas Alt+F11 para ingresar a nuestro editor de vba.

Añadiremos un modulo y pegaremos el siguiente código

Sub MiScroll()
'Buscar mes
On Error GoTo Err
Range("B2").Select
pos = Range("A:A").Find(Range("B1").Value).Row - 2
ActiveWindow.SmallScroll Down:=pos
Exit Sub
Err:
If Err.Number = 91 Then
    MsgBox "No se encuentra el mes seleccionado", vbInformation, "Error"
Else
    MsgBox Err.Description, vbCritical, "Error"
End If
End Sub

Explicacion del codigo:

pos = Range("A:A").Find(Range("B1").Value).Row - 2

Con este codigo buscaremos el mes que elegimos en la celda B1 de nuestra hoja consulta en toda la columna "A" y si lo encuentra guardamos la posición del mes en la variable pos y le restamos menos 2.

ActiveWindow.SmallScroll Down:=pos

Como la variable pos almacena el numero de la fila donde encontramos el mes, utilizaremos ese numero para movernos hacia abajo y colocamos la variable pos en vez de un numero fijo en nuestro codigo.

If Err.Number = 91 Then
    MsgBox "No se encuentra el mes seleccionado", vbInformation, "Error"
Else
    MsgBox Err.Description, vbCritical, "Error"
End If

 En caso no se ubique el mes nos mostrara un mensaje personalizado.

Luego estando dentro de nuestro entorno de desarrollo le damos doble  clic al objeto Hoja1(Consulta) y pegamos el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 1 Then
    Call MiScroll
End If
End Sub

Este código sirve para llamar a la función MiScroll que tenemos en nuestro modulo 1 cada vez que seleccionamos el mes desde la Celda B1.

Una vez finalizado el nuestro ejemplo lo guardamos en nuestra pc como tipo de libro "Libro de excel habilitado para macros".

En este enlace les dejo el archivo finalizado

Funcion para generar sentencia SQL Insert into desde excel

Vamos a crear una función en excel la cual nos permita generar una cadena de texto de la sentencia INSERT INTO de SQL Server a partir de los datos de una o varias columnas así como vemos en la imagen inferior.
Tenemos el registro de países con los campos idpais, descripción y a la derecha se genera la sentencia insert into de todos los registros.

Esto lo lograremos utilizando una función personalizada en vba.

DESARROLLO

Presionamos Alt + F11 para dirigirnos a nuestro entorno de visual basic

Menu insertar - > Modulo

Pegamos el siguiente código:

Function GenerarSQL(Rango As Range, Tabla As String)
For Each celda In Rango
    If IsNumeric(celda) Then
        Concat = Concat & celda & ","
    Else
        Concat = Concat & Chr(39) & celda & Chr(39) & ","
    End If
Next
    GenerarSQL = Left(Concat, Len(Concat) - 1)
    GenerarSQL = "INSERT INTO " & Tabla & " VALUES (" & GenerarSQL & ")"
End Function

Esta funcion llamada GenerarSQL va a constar de dos parámetros de entrada:
Rango: Rango de celdas las cuales contienen información y que representaran a un campo de una tabla.
Tabla: Cadena de texto que sera el nombre de la tabla.

Si los valores de nuestro parámetro Rango son de tipo texto se le  agrega una camilla simple antes y después del valor. Ya que para el SQL la cadena de texto lo necesita.

EJECUCIÓN
Nos ubicamos en la celda E4 de nuestra hoja de calculo y escribimos la función que hemos creado anteriormente: =GenerarSQL(B4:C4,"TB_PAISES")

Desde B4:C4 se encuentran los datos y "TB_PAISES" es el nombre de la tabla a la cual le vamos a insertar los datos.

SQL SERVER

En nuestra base de datos creamos nuestra tabla llamada TB_PAISES

CREATE TABLE [dbo].[TB_PAISES](
[IDPAIS] [int] NULL,
[DESCRIPCION] [varchar](250) NULL
)

Luego copiamos la sentencia de la columna "E" de nuestra hoja de excel y pegamos en el sql server, Ejecutamos las sentencias y se registraran todos los países que teníamos en nuestra hoja de excel.

Esto puede servirnos para insertar registros de nuestra hoja de excel hacia cualquier tabla ya que es personalizable el rango de celdas y el nombre de la tabla.

Desde aqui podran bajar el archivo de ejemplo.

Función en vba para concatenar múltiples celdas en excel

Existen muchas maneras de concatenar las celdas de una hoja en excel, lo común es hacerlo con la función concatenar o con el símbolo "&". Pero cuando deseamos concatenar varias celdas nos resulta muy trabajoso realizarlo de manera individual así que a veces nuestra paciencia se acaba.

La otra de forma de  realizarlo es crear una función UDF en excel vba que nos permita concatenar las celdas que nosotros necesitemos en un solo paso.  Entonces pasaremos a crear la sintaxis de nuestra función a crear.


Una vez que tengamos abierto nuestro libro de excel realizaremos lo siguiente:

- Presionamos ALT + F11 para ingresar a VBA
- Clic en el menú insertar / Agregar modulo

Luego en el modulo pegamos el siguiente código

Function UnirCeldas(Rango As Range, Sep As String)
For Each celda In Rango
    concat = concat & celda & Sep
Next
UnirCeldas = Left(concat, Len(concat) - 1)
End Function

Esta función nos sirve para poder concatenar todas las celdas tanto verticales como horizontales que nosotros necesitemos.

Entonces vamos a explicar la sintaxis de nuestra función para luego aplicarla en una hoja de excel

=UnirCeldas(Rango,Sep)

Rango: Rango de celdas a concatenar
Sep: Separador de cada celda al momento de concatenar

Ponemos un ejemplo para su mayor comprensión

Luego tienen que guardarlo como libro de excel habilitado para macros 

Función para obtener el nombre de una columna a partir de un número

Para realizar nuestro ejemplo Función para obtener el nombre de una columna a partir de un número primero debemos tener en cuenta lo siguiente.

Como todos sabemos las hojas de excel están compuestas por filas y columnas y en este caso nos centraremos en las columnas.Las columnas se nombran con letras del alfabeto ingles tales como A,B,C,etc. Estas columnas también tienen una posición numérica.

Por ejemplo la Columna A es nuestra primera columna por lo tanto tiene la posición 1, La B tiene posición 2 y así sucesivamente.

Entonces una vez que tenemos esto claro, vamos a explicar en que consiste nuestro ejercicio:
Crearemos una función udf usando programación vba la cual nos va a permitir ingresar un numero entero y como resultado nos dará el nombre de una columna, por ejemplo si ingreso el numero 1 nos mostrara la letra "A", el numero 2 la letra "B", el 5 la letra "E"

Bueno ahora empecemos a programar...

Creamos nuestra función llamada NumLet la cual tiene como parámetro la variable Num de tipo integer, esta variable nos indicara la posicion de la columna que queremos mostrar

Function NumLet(Num As Integer) As String
End Function

La cantidad de columnas que existe en una hoja de excel tiene un limite que en el caso de mi versión 2007 es 16384 que es la columna "XFD".En caso que ustedes utilicen otra versión pueden cambiarla.
Esta validación nos sirve para salir de la función en caso que el usuario ingrese un numero fuera del rango

If Num > 16384 Then Exit Function

Una vez tengamos el valor de nuestra variable "num" solo nos hará falta convertirlo en una referencia de rango. Entonces a nuestra propiedad cells le asignaremos la fila 1 y la columna Num (valor entero) e indicamos que queremos la dirección de la celda con la propiedad .Address y lo almacenamos en la variable dato. Si el valor de nuestra variable Num es 1, nuestro dato almacenara "SA$1"

dato = Cells(1, Num).Address

Ahora solo nos falta obtener La letra que se encuentra en la referencia de celda.'La letra siempre va a estar entre los símbolos de "$" por lo tanto habría que extraer la letra usando la función mid (extraer)
Pongamos de ejemplo que nuestra variable dato sea "$A$1":

Como parámetro "string" le indicaremos la variable dato, obtendremos información a partir del segundo dígito y por ultimo le indicaremos que nos tome la cantidad de dígitos restando "len(dato)" que es la cantidad de dígitos de la variable dato menos 3. Esto nos retornara a la función NumLet la letra A que es lo que necesitamos finalmente.

NumLet = Mid(dato, 2, Len(dato) - 3)

Finalmente nuestro código quedara de la siguiente manera

Function NumLet(Num As Integer) As String
If Num > 16384 Then Exit Function
    dato = Cells(1, Num).Address
    NumLet = Mid(dato, 2, Len(dato) - 3)
End Function

El cual podemos aplicarlo a una hoja

O también utilizarlo en otra función o subrutina:

Sub Columna()
    MsgBox NumLet(1)
End Sub

Espero que la explicación haya sido clara y puedan usar esta función al máximo.