Hola a todos!!

En el post de hoy vamos a confeccionar un pequeño programa que nos ayudará a generar facturas.

Lo haremos mediante programación, la razón es que el uso de fórmulas y VBA al mismo tiempo pueden ralentizar nuestros procesos y hacerlo todo con código hará más eficiente nuestro proyecto.

Cuando hablamos de generar facturas, siempre tenemos que pensar en la fuente y la estructura de los datos a partir de la cual confeccionaremos nuestras facturas. Para este ejemplo y para esta programación partiré de una estructura de datos específica, en cuanto a la fuente de datos (el programa que nos devuelve esa información), vamos a suponer que es un archivo Excel, de Access, TXT o el report de un programa específico (que puede estar diseñado en Excel, Access, etc).

El concepto que debemos tener claro es que en esta base de datos inicial, cada factura se va a repetir tantas veces como elementos tenga. Es decir, si en una factura tenemos 6 productos, esa factura (y todos sus datos) se repetirán 6 veces.

Os muestro una imagen de esa base de datos y sus conceptos, vamos a suponer que somos una empresa distribuidora de material eléctrico:

Como podéis observar, existen multitud de campos y todos ellos los vamos a necesitar para poder generar facturas a partir de esta plantilla base:

Pues bien, he creado tres botones a la derecha (fuera del área de impresión) que serán los comandos que vamos a utilizar para crear la factura. Además en la propia factura existen dos ComboBox dependientes para poder seleccionar las facturas por cliente, es decir cuando seleccionemos un cliente, el combo que muestra los números de factura solo nos mostrará las facturas relacionadas a ese cliente.

Dado siempre vamos a interactuar con esos botones de comando, os iré comentando su función y la macro que tienen asociada. Empezamos por el botón ACTUALIZAR INFORMACIÓN:

Con este botón vamos a cargar o actualizar el nombre de las empresas y el número de factura de los ComboBox 1 y 2. La macro asociada es la siguiente:

Sub ACTUALIZAR_EMPRESAS()
'Definimos las variables
Dim Dataread As Object, obSQL As String
Dim cnn As Object, MiLibro As String
'Vaciamos combobox1
With Sheets("FACTURA")
.ComboBox1.Clear
'Iniciamos consulta para cargar registros únicos de Nombre de la empresa
obSQL = "SELECT distinct [DATOS_FACTURA$].[NOMBRE] " & _
"FROM [DATOS_FACTURA$] " & _
"Where [DATOS_FACTURA$].[NOMBRE]"
'Guardamos el nombre del libro activo para utilizarlo en la conexión ADO
MiLibro = ActiveWorkbook.Name
'Iniciamos la conexión ADO
Set cnn = CreateObject("ADODB.Connection")
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 12.0"
.Open
End With
'Procedemos a grabar los datos de la consulta
Set Dataread = CreateObject("ADODB.Recordset")
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorType = adOpenForwardOnly
.Open
End With
Do Until Dataread.EOF
'Pasamos la información al Combobox1
.ComboBox1.AddItem Dataread("NOMBRE")
Dataread.MoveNext
Loop
MsgBox ("SE HA ACTUALIZADO LA INFORMACIÓN"), vbInformation
'Liberamos y cerramos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
End Sub

Esta macro carga y actualiza los datos del Combobox1 que muestra los nombres de las empresas. Utilizamos ADO para dar mayor rapidez a nuestro proceso.

La segunda macro, no está en un módulo estándar y tampoco está asociada a este botón, sino que está asociada al Combobox1, en concreto al evento Change, esta sería la macro:

Private Sub ComboBox1_Change()
'Definimos las variables
Dim Dataread As Object, obSQL As String
Dim cnn As Object, MiLibro As String, vNombre As String
'Vaciamos combobox2
With Sheets("FACTURA")
.ComboBox2.Clear
'Iniciamos la consulta para mostrar las facturas de la empresa seleccionada
vNombre = .ComboBox1.Value
obSQL = "SELECT [DATOS_FACTURA$].[Nº DE FACTURA] " & _
"FROM [DATOS_FACTURA$] " & _
"Where [DATOS_FACTURA$].[NOMBRE]='" & vNombre & "' " & _
"GROUP BY [DATOS_FACTURA$].[Nº DE FACTURA]"
'Guardamos el nombre del libro activo para utilizarlo en la conexión ADO
MiLibro = ActiveWorkbook.Name
'Iniciamos la conexión ADO
Set cnn = CreateObject("ADODB.Connection")
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 12.0"
.Open
End With
'Procedemos a grabar los datos de la consulta
Set Dataread = CreateObject("ADODB.Recordset")
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorType = adOpenForwardOnly
.Open
End With
Do Until Dataread.EOF
'Cargamos en el combobox2 listado de facturas dependiendo de la empresa seleccionada
.ComboBox2.AddItem Dataread("Nº DE FACTURA")
Dataread.MoveNext
Loop
'Liberamos y cerramos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
End Sub

Con esta macro, muy similar a la anterior, lo que estamos haciendo es que cada vez que abramos el Combobox2 (el del número de factura), los datos que nos muestre serán todas aquellas facturas que dependan de la empresa seleccionada en el Combobo1, esto lo hacemos con esta instrucción SQL incluida en la macro:

obSQL = "SELECT [DATOS_FACTURA$].[Nº DE FACTURA] " & _
"FROM [DATOS_FACTURA$] " & _
"Where [DATOS_FACTURA$].[NOMBRE]='" & vNombre & "' " & _
"GROUP BY [DATOS_FACTURA$].[Nº DE FACTURA]"

Una vez que hemos actualizado la información, debemos generar la factura, esto lo hacemos con el botón GENERAR FACTURA:

Quizás pueda parecer un proceso complejo, pero en realidad es bastante sencillo en casi todo el código. La macro asociada a está botón de comando es:

Sub GENERAR_FACTURA()
'Declaramos variables
Dim Fin As Long, i As Long, Final As Long, x As Long, j As Long, fInicio As Long
Dim sCantidad As String, sDescripcion As String, sPrecio As String
Dim MatrizCantidad As Variant, MatrizDescripcion As Variant, MatrizPrecio As Variant
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Borramos datos de la factura
With Sheets("FACTURA")
.Range("B8").ClearContents
.Range("B12:B16").ClearContents
.Range("E12:E16").ClearContents
.Range("A21:C21").ClearContents
Final = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A24:F" & Final + 7).Clear
'Obligamos a seleccionar empresa y factura, de lo contrario salimos del proceso
Fin = Application.CountA(Sheets("DATOS_FACTURA").Range("B:B"))
If .ComboBox1.Value = vbNullString Or .ComboBox2.Value = vbNullString Then
MsgBox "DEBES SELECCIONAR UN CLIENTE Y UNA FACTURA, VERIFICA QUE HAS ACTUALIZADO LA INFORMACIÓN", vbExclamation, "SELECCIONAR INFORMACIÓN"
Exit Sub
End If
'Cargamos datos
For i = 2 To Fin
If Trim(Sheets("DATOS_FACTURA").Cells(i, 2)) = .ComboBox2.Value Then
'Fecha factura
.Cells(8, 2) = Sheets("DATOS_FACTURA").Cells(i, 1)
'Nombre,Dirección, Ciudad CP, Teléfono, Email (Facturar a)
.Cells(12, 2) = Sheets("DATOS_FACTURA").Cells(i, 3)
.Cells(13, 2) = Sheets("DATOS_FACTURA").Cells(i, 4)
.Cells(14, 2) = Sheets("DATOS_FACTURA").Cells(i, 5)
.Cells(15, 2) = Sheets("DATOS_FACTURA").Cells(i, 6)
.Cells(16, 2) = Sheets("DATOS_FACTURA").Cells(i, 7)
'Nombre,Dirección, Ciudad CP, Teléfono, Email (Enviar a)
.Cells(12, 5) = Sheets("DATOS_FACTURA").Cells(i, 8)
.Cells(13, 5) = Sheets("DATOS_FACTURA").Cells(i, 9)
.Cells(14, 5) = Sheets("DATOS_FACTURA").Cells(i, 10)
.Cells(15, 5) = Sheets("DATOS_FACTURA").Cells(i, 11)
.Cells(16, 5) = Sheets("DATOS_FACTURA").Cells(i, 12)
'Vendedor, Fecha de envío, Tipo de envío
.Cells(21, 1) = Sheets("DATOS_FACTURA").Cells(i, 13)
.Cells(21, 2) = Sheets("DATOS_FACTURA").Cells(i, 14)
.Cells(21, 3) = Sheets("DATOS_FACTURA").Cells(i, 15)
End If
Next
'Mediante matrices cargamos listado de Cantidades, Descripciones y precios
sCantidad = vbNullString
sDescripcion = vbNullString
sPrecio = vbNullString
For n = 2 To Fin
If .ComboBox2 = Trim(Sheets("DATOS_FACTURA").Cells(n, 2)) Then sCantidad = sCantidad & "|" & Sheets("DATOS_FACTURA").Cells(n, 16)
If .ComboBox2 = Trim(Sheets("DATOS_FACTURA").Cells(n, 2)) Then sDescripcion = sDescripcion & "|" & Sheets("DATOS_FACTURA").Cells(n, 17)
If .ComboBox2 = Trim(Sheets("DATOS_FACTURA").Cells(n, 2)) Then sPrecio = sPrecio & "|" & Sheets("DATOS_FACTURA").Cells(n, 18)
Next n
'Depuramos datos de las variables eliminando la barra al inicio del string
sCantidad = Mid((sCantidad), 2, Len(sCantidad))
sDescripcion = Mid((sDescripcion), 2, Len(sDescripcion))
sPrecio = Mid((sPrecio), 2, Len(sPrecio))
fInicio = 24
'Pasamos los datos de las variables a una matriz y luego a cada elemento de la factura
MatrizCantidad = Split(sCantidad, "|")
MatrizDescripcion = Split(sDescripcion, "|")
MatrizPrecio = Split(sPrecio, "|")
For j = 0 To UBound(MatrizCantidad)
.Cells(fInicio, 1) = MatrizCantidad(j)
.Cells(fInicio, 2) = MatrizDescripcion(j)
.Cells(fInicio, 5) = MatrizPrecio(j) * 1
.Cells(fInicio, 5).NumberFormat = "#,##0.00 $"
fInicio = fInicio + 1
Next j
'Calculamos importe total por unidad y formateamos a moneda
Final = .Range("A" & Rows.Count).End(xlUp).Row
For x = 24 To Final
.Cells(x, 6) = .Cells(x, 1) * .Cells(x, 5)
.Cells(x, 6).NumberFormat = "#,##0.00 $"
Next x
'Aplicamos línea divisoria
Range("A" & Final + 1 & ":" & "F" & Final + 1).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'Calculamos suma total (Base Imponible)
.Cells(Final + 3, 5) = "Base Imponible:"
.Cells(Final + 3, 6) = Application.WorksheetFunction.Sum(.Range("F24:F" & Final))
.Cells(Final + 3, 6).NumberFormat = "#,##0.00 $"
'Indicamos % de impuesto
.Cells(Final + 4, 6) = .Cells(11, 8)
.Cells(Final + 4, 6).NumberFormat = "0.00%"
'Aplicamos impuesto
.Cells(Final + 5, 5) = "IVA:"
.Cells(Final + 5, 6) = .Cells(Final + 3, 6) * .Cells(Final + 4, 6)
.Cells(Final + 5, 6).NumberFormat = "#,##0.00 $"
'Aplicamos línea divisoria
Range("E" & Final + 5 & ":" & "F" & Final + 1).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'Obtenemos resultado final
.Cells(Final + 7, 5) = "Total Factura:"
.Cells(Final + 7, 6) = .Cells(Final + 3, 6) + .Cells(Final + 5, 6)
.Cells(Final + 7, 6).NumberFormat = "#,##0.00 $"
.Range("H5").Select
End With
Application.ScreenUpdating = True
End Sub

Está bastante bien comentada, pero básicamente lo que hacemos es borrar toda la información de la factura anterior (si la hubiese) y comprobar que hemos seleccionado datos en los dos ComboBox, si no hay selección, salimos del proceso.

Luego grabamos la información de la hoja FACTURA utilizando varios loop for-next y usamos también un sencillo proceso matricial para colocar todos los elementos de la factura en los apartados de CANTIDAD, DESCRIPCIÓN y PRECIO POR UNIDAD.

A continuación incluimos una serie de instrucciones para calcular el total del precio (multiplicando la cantidad por el precio por unidad). La suma y el porcentaje del impuesto (que debemos indicar en la parte marcada en azul de la hoja).

Formateamos los importes a moneda – euros e incluimos una barra separadora entre los elementos de la factura y los totales.

Por último, he programado un botón para limpiar los datos de la factura, BORRAR DATOS:

Sub BORRAR_FACTURA()
'Declaramos variables
Dim Final As Long
'Borramos todos los datos de la factura
With Sheets("FACTURA")
.ComboBox1 = ""
.ComboBox2 = ""
.Range("B8").ClearContents
.Range("B12:B16").ClearContents
.Range("E12:E16").ClearContents
.Range("A21:C21").ClearContents
Final = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A24:F" & Final + 7).Clear
End With
End Sub

Con este proceso dejamos plantilla totalmente libre de datos.

Os dejo el resultado tras generar la factura 1 de la Empresa A, quedaría así:

Por último comentar que si queréis cambiar el formato de la moneda, solo tenéis entrar en la macro GENERAR FACTURA y donde está especificado el formato del euro: .NumberFormat = “#,##0.00 $” realizar el cambio por la moneda que necesitéis.

Como podéis observar, la aplicación funciona perfectamente. He intentado que los códigos no sean demasiado complejos (pero que sean eficientes) y he realizado comentarios para una mejor comprensión.

Por lo tanto, una vez descargado el programa o al abrirlo

Es importante indicar, que la programación hace referencia a celdas específicas y de la hoja FACTURA y también a los nombres y columnas de la hoja DATOS_FACTURA, es decir, que si realizáis modificaciones debéis adaptar el código, de otra forma se producirán errores.

La versión funciona correctamente en archivos .xlsm y en versiones 2010, 2013 y 2016. En versiones anteriores o con formato .xls algunos comandos no son compatibles y podrían ocasiones pérdida de funcionalidad.

Y eso es todo por hoy, espero que os haya gustado este pequeño ejemplo de cómo podemos usar Excel para generar facturas.

Os dejo el archivo de prueba en Google Drive, dado que en WordPress no puedo alojar archivos .xslm

Descarga el archivo de ejemplo pulsando en: GENERAR FACTURAS USANDO VBA, CREAR UN PROGRAMA DE FACTURACIÓN

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!