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.

Contar la cantidad de celdas de un determinado color de fondo

En excel no existe una función predeterminada que nos permita contar la cantidad de celdas que contienen un determinado color de fondo. Sin embargo podemos realizar una función udf que nos permita realizar tal cosa haciendo uso de programación vba

Analisis:

Veremos en nuestra imagen inferior, dos bloques: el bloque A el cual contiene datos desde la celda A2:A12 cada celda con un determinado color (con colores repetidos), y por otro lado el bloque B con una lista de 3 colores únicos los cuales son iguales al del Bloque B.

La idea es contar la cantidad de veces que se repiten los colores del bloque B en el Bloque A, es como si fuera un contar.si pero con colores de fondo de una celda.

Antes de comenzar con nuestro código vamos a explicar que la manera de saber el color de fondo de una celda usando vba es a través del código interior.color que hace referencia a una celda.
Por ejemplo :
Al ejecutar el siguiente código nos muestra un mensaje con el numero de color de la celda .
De esta manera cada color podemos identificarlo con un numero. que en este caso para el amarillo sera 16777215

Sub ColorFondo()
MsgBox Range("A2").Interior.Color
End Sub

Bueno una vez analizado lo que se requiere, crearemos la funcion udf en vba.
Para lo cual ingresamos al editor de vba e insertamos un modulo en el cual escribiremos este codigo que a continuación pasaremos a explicar

Creamos nuestra función  llamada ContarColorRelleno y le colocamos dos parámetros de tipo Range: MatrizColores y ColorCriterio:

MatrizColores: Es la lista de colores que se va a contar, en este caso esta en nuestro bloque A
ColorCriterio: Es el color con el cual deseamos comparar la lista de colores. Este criterio seria nuestro bloque B.

codigo:

Function ContarColorRelleno(MatrizColores As Range, ColorCriterio As Range)

 La explicacion de cada linea del codigo se encuentra comentada de color verde.

Dim cont
Application.Volatile 'Permite actualizar la funcion al editar cualquier celda de la hoja
For Each celda In MatrizColores
    If celda.Interior.Color = ColorCriterio.Interior.Color Then ' Valida si el color es igual
        cont = cont + 1 'si es verdadero entonces incrementa la variable cont
    End If
Next
    ContarColorRelleno = cont 'Asignamos la cantidad de veces que se repite un color a la funcion

Una vez que hayamos finalizado con nuestro la sintaxis queda de la siguiente manera:

=ContarColorRelleno(MatrizColores,ColorCriterio)

Lo usaremos en la celda E2: =ContarColorRelleno($A$2:$A$12,D2)

De esta manera nos cuenta en E2 cuantas celdas de color amarillo existe en nuestro rango A2:A12

Y asi podemos realizarlo con la cantidad de celdas que nos haga falta.

Espero les haya sido de mucha utilidad este pequeño codigo. Pueden descargar el ejemplo en este enlance

Unir todas las hojas de un libro de excel en una sola

En esta oportunidad haré entrega de una pequeña aplicación la cual cumple con la tarea de unir todas las hojas de un libro de excel en una sola.
Esto nos facilita el trabajo de realizarlo manualmente y nos ahorra tiempo.

Pasaré a explicar como funciona esta aplicación paso a paso.

Paso 1:
Presionar la combinación de teclas: Ctrl + H y se nos mostrara la siguiente ventana donde se cargara en una lista todos los libros abiertos para elegir a cual de ellos le vamos a unir sus hojas.

Paso 2:

En la parte que dice "Nombre de la hoja nueva" escribiremos el nombre de la hoja en la cual se unificara los datos de todas las hojas del libro seleccionado en el paso 1.

Paso 3 :

Por ultimo le daremos clic al botón unir el cual creara una nueva hoja con el nombre definido en el paso 2, y nos mostrara el siguiente mensaje.

El proceso ha finalizado y se realizo la consolidación de todas las hojas de nuestro libro en una sola hoja, tal como vemos en la siguiente imagen:

Consideraciones a tener en cuenta:

- Solo se puede seleccionar un solo libro por tarea.

- El aplicativo colocara el nombre de cada hoja en la primera columna.

Descargar aquí

Abrir archivo de excel mediante formulario de login

Aqui se muestra un ejemplo en el cual mediante programacion vba creamos un formulario de login que nos pide ingresar el usuario y contraseña para abrir nuestro libro de excel.

Una vez ingresado los datos correctos y aceptamos, nos dirige hacia el archivo de excel que lo contiene.

Concatenar multiples celdas con excel vba

En esta oportunidad aprenderemos como concatenar múltiples celdas de manera fácil en excel , creando una función definida de usuario (udf) con programación vba (Visual basic for applications)
Esto se puede aplicar a cualquier versión de excel.
Espero les sea de mucha utilidad.