En esta ocasion, vamos a realizar un ejercicio tal que, como lo dice el titulo del post, varios libros puedan guardar informacion en un unico archivo sin duplicar la informacion.
El ejemplo que se me ocurre para illustrarlo es el siguiente:
Supongamos por un momento que tenemos una tienda y que las notas las hacemos en un formatito chingon que hemos creado en un archivo de excel, mismo que contiene el consecutivo de las notas que hemos vendido y tambien nuestro inventario, el cual se actualiza con cada venta que hacemos.
Ahora, como somos buenos empresarios, el negocio ha crecido y necesitamos abrir una caja mas con un empleado que necesita hacer sus notas y actualizar inventario; de manera que en el mostrador de nuestra tienda hay dos compus con su archivo de ventas respectivamente.
Podemos, entonces, hacer dos archivos y al final del dia juntarlos para actualizar el inventario, pero eso ocasiona varios problemas:
- cada archivo tiene su propio numero o consecutivo de nota lo cual es incorrecto, pues se puede repetir y podria darse el caso de dos clientes con el mismo numero de nota.
- cada archivo tiene su inventario, lo cual tambien es incorrecto, pues podriamos vender un producto que ya no tenga existencia.
- problemas al hacer el corte o cierre de dia, semana, mes; problemas de control de minimos y maximos, etc.
la solucion es que las notas de ambos archivos se deben guardar en un mismo archivo de datos, asi como el inventario debe estar en el mismo archivo de las notas guardadas. De esta manera, si una pc de mostrador necesita hacer una nota de venta, entonces consulta en el archivo de datos cual es el siguiente numero de folio disponible, y la otra pc trabaja de la misma manera; asi, solo tendremos un consecutivo de folios.
La misma mecanica aplica para el inventario,
Para esto vamos a crear tres archivos A, B y C.
Donde A y B son archivos que contienen el formatito chingon y que esta cada uno en una de las dos compus del mostrador de nuestra tienda.
C es un archivo que contiene el inventario y el registro de las notas de ambos archivos y puede estar en cualquier computadora de la red.
Esta de mas decir que las tres computadoras estan conectadas a la red y que el archivo de datos esta en un directorio compartido y accesible desde los otros dos equipos.
Las reglas son:
- A y B no contienen ningun dato de notas ni inventario
- C no tiene el formatito chingon de la nota
- La informacion que se muestra en A y B proviene de C
- Y la informacion que se genera en A y B se guarda en C
Este ejemplo esta hecho con las siguientes caracteristicas:
- Windows 7 home premium 64 bits
- Microsoft Office Excel 2013
Consideraciones:
- Escribir codigo en VBA 2013 es mas sencillo que en las versiones anteriores pero la compatibilidad se sigue conservando con las versiones anteriores( al menos eso creo ) .
- Para el ejemplo, vamos a crear dos libros que lean y guarden informacion en la hoja de un tercer libro que se encuentra en otra ubicacion
IMPORTANTE:
no voy a ejemplificar por completo el sistema de ventas que he mencionado arriba, sino que me voy a limitar a describir el proceso basico de acceder remotamente, leer y guardar
informacion en un libro ajeno. Eso seria suficiente para que saques tus propias conclusiones, para que mejores el codigo y tambien para que lo uses en tus necesidades o preferencias.
UNA VEZ MAS, DEBO ACLARAR que el codigo es muy basico y austero para hacerlo lo mas entendible posible a los ojos de los programadores iniciados recientemente; obviamente, se podria construir una aplicacion completa con vba pero ese no es el objetivo del post.
- para empezar, crea una carpeta de trabajo en el escritorio y nombrala EXCEL_MULTI
- dentro de esa carpeta crea dos carpetas mas: DATOS y FORMATO
Estas carpetas van a funcionar como nuestro entorno de red simulado.
bueno,
1- Abre excel, selecciona la Hoja1 y le cambias el nombre a DATOS
2- en las celdas A1, B1 y C1 escribes CONSECUTIVO, LEER, GUARDAR respectivamente. sombrea estas celdas, pues serviran de encabezados.
3- guarda el libro con el nombre Bdata en el directorio excel_multiDATOS y luego lo cierras, no es necesario que lo habilites para macros, a menos que tengas pensado escribir procedimientos para otros fines.
4- Crea otro libro y guardalo con el nombre MOSTRADOR1 en la carpeta excel_multiFORMATOS
Este archivo lo habilitas para macros, osea, extension xlsm
5- pulsa ALT + F11 para abrir el editor de VBA
6- inserta un nuevo formulario
7- establece las siguientes propiedades al form
name: frmTest
caption: tu_nombre - Prueba de MultiExcel
8- inserta 3 textbox's con sus respectivas etiquetas:
txtConsecutivo
txtLeer
txtGuardar
9- inserta un boton a la derecha de cada text y pones las propiedades siguientes:
name / caption
cmdConsecutivo Consecutivo
cmdLeer Leer
cmdGuardar Guardar
10- a continuacion pongo el codigo de los tres botones:
cmdConsecutivo:
Private Sub cmdConsecutivo_Click()
Dim libro As Workbook ' referencia al libro que vamos a abrir
Dim hoja As Worksheet ' la hoja que nos interesa
Dim valor As Integer
Application.StatusBar = " Procesando la informacion "
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Set libro = Application.Workbooks.Open( "192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx" )
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Set hoja = libro.Worksheets( "DATOS" )
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range( "A2 " ).Value = "" Then
hoja.Range( "A2" ).Value = 1
hoja.Range( "B2" ).Value = "Leer 1"
hoja.Range(" C2" ).Next.Value = "Guardar 1"
valor = 1
Else
hoja.Range( "A1" ).End( xlDown )( xlDropDown ).Value = Val(hoja.Range( "A1" ).End( xlDown ) .Value ) + 1
valor = hoja.Range( "A1" ).End( xlDown ).Value
hoja.Range( "A1" ).End( xlDown ). Next.Value = "leer " & valor
hoja.Range( "A1" ).End( xlDown ). Next.Next.Value = "Guardar " & valor
End If
libro.Save
libro.Close
Me.txtConsecutivo.Text = valor
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub cmdLeer_Click()
Dim libro As Workbook
Dim hoja As Worksheet
Dim valor, valor2 As String
Application.StatusBar = "COMPROBANDO LIBRO DE DATOS..."
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Application.StatusBar = "ABRIENDO LIBRO"
Set libro = Application.Workbooks.Open("192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx"
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Application.StatusBar = "SELECCIONANDO HOJA"
Set hoja = libro.Worksheets("DATOS"
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range( "A2" ).Value = "" Then
valor = "Sin valor"
valor2 = "Sin Valor"
Else
valor = hoja.Range( "A1" ).End(xlDown).Next.Value
valor2 = hoja.Range( "A1" ).End(xlDown).Next.Next.Value
End If
Application.StatusBar = "CERRANDO LIBRO DE DATOS"
libro.Close
Me.txtLeer.Text = valor
Me.txtGuardar.Text = valor2
Application.StatusBar = "OPERACION TERMINADA CORRECTAMENTE"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub Guardar_Click()
Dim libro As Workbook
Dim hoja As Worksheet
Dim valor As Integer
Application.StatusBar = "COMPROBANDO LIBRO DE DATOS..."
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Application.StatusBar = "ABRIENDO DATOS... ESPERE UN MOMENTO"
Set libro = Application.Workbooks.Open("192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx"
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Application.StatusBar = "SELECCIONADO HOJA DE TRABAJO"
Set hoja = libro.Worksheets("DATOS"
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range("A2".Value = "" Then
hoja.Range("A2".Next.Next.Value = Me.txtGuardar.Text
Else
hoja.Range("A1".End(xlDown).Next.Next.Value = Me.txtGuardar.Text
End If
Application.StatusBar = "GUARDANDO LA INFORMACION...."
libro.Save
Application.StatusBar = "CERRANDO LIBRO DE DATOS..."
libro.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = "LA INFORMACION SE GUARDO CORRECTAMENTE."
MsgBox "La informacion se ha guardado correctamente"
End Sub
Private Sub checarLibroAbierto()
For i = 1 To Application.Workbooks.Count
If Application.Workbooks(i).Name = "BData.xlsx" Then
Application.Workbooks(i).Close
Exit For
End If
Next
End Sub
y aqui las imagenes para que vean la identacion:
11 - copia el libro MOSTRADOR1 en la misma carpeta y nombra como MOSTRADOR2
12 - Si abres los dos archivos en simultaneo y ejecutas el form, te daras cuenta que cualquiera de los dos archivos actualizan al libro de datos sin duplicar la informacion guardada.
En mi pc excel no me permite tener varias instancias pero si pruebas a abrir los archivos en dos pc's conectadas en red, te daras cuenta de su funcionamiento.
Asi, el archivo de datos puede estar en la compu del jefe y los formatos en las compus del mostrador; ambos archivos trabajarian con los mismos datos sin temor a duplicar la informacion.
- La direccion 192.168.0.2 que aparece en la imagen corresponde a la direccion de red en donde se encuentra el archivo de datos. ESTA DIRECCION LA DEBES CAMBIAR con la ip correspondiente a tu red.
- Si tienes dudas de algunos conceptos, entonces visita mis post anteriores para salir de dudas.
En esta direccion puedes encontrar el archivo con las carpetas, archivos e imagenes usados en el post.
http://sdrv.ms/1e83NE1
El ejemplo que se me ocurre para illustrarlo es el siguiente:
Supongamos por un momento que tenemos una tienda y que las notas las hacemos en un formatito chingon que hemos creado en un archivo de excel, mismo que contiene el consecutivo de las notas que hemos vendido y tambien nuestro inventario, el cual se actualiza con cada venta que hacemos.
Ahora, como somos buenos empresarios, el negocio ha crecido y necesitamos abrir una caja mas con un empleado que necesita hacer sus notas y actualizar inventario; de manera que en el mostrador de nuestra tienda hay dos compus con su archivo de ventas respectivamente.
Podemos, entonces, hacer dos archivos y al final del dia juntarlos para actualizar el inventario, pero eso ocasiona varios problemas:
- cada archivo tiene su propio numero o consecutivo de nota lo cual es incorrecto, pues se puede repetir y podria darse el caso de dos clientes con el mismo numero de nota.
- cada archivo tiene su inventario, lo cual tambien es incorrecto, pues podriamos vender un producto que ya no tenga existencia.
- problemas al hacer el corte o cierre de dia, semana, mes; problemas de control de minimos y maximos, etc.
la solucion es que las notas de ambos archivos se deben guardar en un mismo archivo de datos, asi como el inventario debe estar en el mismo archivo de las notas guardadas. De esta manera, si una pc de mostrador necesita hacer una nota de venta, entonces consulta en el archivo de datos cual es el siguiente numero de folio disponible, y la otra pc trabaja de la misma manera; asi, solo tendremos un consecutivo de folios.
La misma mecanica aplica para el inventario,
Para esto vamos a crear tres archivos A, B y C.
Donde A y B son archivos que contienen el formatito chingon y que esta cada uno en una de las dos compus del mostrador de nuestra tienda.
C es un archivo que contiene el inventario y el registro de las notas de ambos archivos y puede estar en cualquier computadora de la red.
Esta de mas decir que las tres computadoras estan conectadas a la red y que el archivo de datos esta en un directorio compartido y accesible desde los otros dos equipos.
Las reglas son:
- A y B no contienen ningun dato de notas ni inventario
- C no tiene el formatito chingon de la nota
- La informacion que se muestra en A y B proviene de C
- Y la informacion que se genera en A y B se guarda en C
Este ejemplo esta hecho con las siguientes caracteristicas:
- Windows 7 home premium 64 bits
- Microsoft Office Excel 2013
Consideraciones:
- Escribir codigo en VBA 2013 es mas sencillo que en las versiones anteriores pero la compatibilidad se sigue conservando con las versiones anteriores( al menos eso creo ) .
- Para el ejemplo, vamos a crear dos libros que lean y guarden informacion en la hoja de un tercer libro que se encuentra en otra ubicacion
IMPORTANTE:
no voy a ejemplificar por completo el sistema de ventas que he mencionado arriba, sino que me voy a limitar a describir el proceso basico de acceder remotamente, leer y guardar
informacion en un libro ajeno. Eso seria suficiente para que saques tus propias conclusiones, para que mejores el codigo y tambien para que lo uses en tus necesidades o preferencias.
UNA VEZ MAS, DEBO ACLARAR que el codigo es muy basico y austero para hacerlo lo mas entendible posible a los ojos de los programadores iniciados recientemente; obviamente, se podria construir una aplicacion completa con vba pero ese no es el objetivo del post.
- para empezar, crea una carpeta de trabajo en el escritorio y nombrala EXCEL_MULTI
- dentro de esa carpeta crea dos carpetas mas: DATOS y FORMATO
Estas carpetas van a funcionar como nuestro entorno de red simulado.
bueno,
1- Abre excel, selecciona la Hoja1 y le cambias el nombre a DATOS
2- en las celdas A1, B1 y C1 escribes CONSECUTIVO, LEER, GUARDAR respectivamente. sombrea estas celdas, pues serviran de encabezados.
3- guarda el libro con el nombre Bdata en el directorio excel_multiDATOS y luego lo cierras, no es necesario que lo habilites para macros, a menos que tengas pensado escribir procedimientos para otros fines.
4- Crea otro libro y guardalo con el nombre MOSTRADOR1 en la carpeta excel_multiFORMATOS
Este archivo lo habilitas para macros, osea, extension xlsm
5- pulsa ALT + F11 para abrir el editor de VBA
6- inserta un nuevo formulario
7- establece las siguientes propiedades al form
name: frmTest
caption: tu_nombre - Prueba de MultiExcel
8- inserta 3 textbox's con sus respectivas etiquetas:
txtConsecutivo
txtLeer
txtGuardar
9- inserta un boton a la derecha de cada text y pones las propiedades siguientes:
name / caption
cmdConsecutivo Consecutivo
cmdLeer Leer
cmdGuardar Guardar
10- a continuacion pongo el codigo de los tres botones:
cmdConsecutivo:
Private Sub cmdConsecutivo_Click()
Dim libro As Workbook ' referencia al libro que vamos a abrir
Dim hoja As Worksheet ' la hoja que nos interesa
Dim valor As Integer
Application.StatusBar = " Procesando la informacion "
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Set libro = Application.Workbooks.Open( "192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx" )
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Set hoja = libro.Worksheets( "DATOS" )
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range( "A2 " ).Value = "" Then
hoja.Range( "A2" ).Value = 1
hoja.Range( "B2" ).Value = "Leer 1"
hoja.Range(" C2" ).Next.Value = "Guardar 1"
valor = 1
Else
hoja.Range( "A1" ).End( xlDown )( xlDropDown ).Value = Val(hoja.Range( "A1" ).End( xlDown ) .Value ) + 1
valor = hoja.Range( "A1" ).End( xlDown ).Value
hoja.Range( "A1" ).End( xlDown ). Next.Value = "leer " & valor
hoja.Range( "A1" ).End( xlDown ). Next.Next.Value = "Guardar " & valor
End If
libro.Save
libro.Close
Me.txtConsecutivo.Text = valor
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub cmdLeer_Click()
Dim libro As Workbook
Dim hoja As Worksheet
Dim valor, valor2 As String
Application.StatusBar = "COMPROBANDO LIBRO DE DATOS..."
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Application.StatusBar = "ABRIENDO LIBRO"
Set libro = Application.Workbooks.Open("192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx"
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Application.StatusBar = "SELECCIONANDO HOJA"
Set hoja = libro.Worksheets("DATOS"
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range( "A2" ).Value = "" Then
valor = "Sin valor"
valor2 = "Sin Valor"
Else
valor = hoja.Range( "A1" ).End(xlDown).Next.Value
valor2 = hoja.Range( "A1" ).End(xlDown).Next.Next.Value
End If
Application.StatusBar = "CERRANDO LIBRO DE DATOS"
libro.Close
Me.txtLeer.Text = valor
Me.txtGuardar.Text = valor2
Application.StatusBar = "OPERACION TERMINADA CORRECTAMENTE"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub Guardar_Click()
Dim libro As Workbook
Dim hoja As Worksheet
Dim valor As Integer
Application.StatusBar = "COMPROBANDO LIBRO DE DATOS..."
Application.DisplayAlerts = False
Application.ScreenUpdating = False
checarLibroAbierto
Application.StatusBar = "ABRIENDO DATOS... ESPERE UN MOMENTO"
Set libro = Application.Workbooks.Open("192.168.0.2UsershulerotuxDesktopexcel_multiDATOSBData.xlsx"
If libro Is Nothing Then
MsgBox "No se pudo abrir el libro de datos" & vbCrLf & "verifique el nombre y la ruta"
Exit Sub
End If
Application.StatusBar = "SELECCIONADO HOJA DE TRABAJO"
Set hoja = libro.Worksheets("DATOS"
If hoja Is Nothing Then
MsgBox "No se localizo la hoja de datos" & vbCrLf & "Revisa si no se ha cambiado el nombre de la hoja"
Exit Sub
End If
If hoja.Range("A2".Value = "" Then
hoja.Range("A2".Next.Next.Value = Me.txtGuardar.Text
Else
hoja.Range("A1".End(xlDown).Next.Next.Value = Me.txtGuardar.Text
End If
Application.StatusBar = "GUARDANDO LA INFORMACION...."
libro.Save
Application.StatusBar = "CERRANDO LIBRO DE DATOS..."
libro.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = "LA INFORMACION SE GUARDO CORRECTAMENTE."
MsgBox "La informacion se ha guardado correctamente"
End Sub
Private Sub checarLibroAbierto()
For i = 1 To Application.Workbooks.Count
If Application.Workbooks(i).Name = "BData.xlsx" Then
Application.Workbooks(i).Close
Exit For
End If
Next
End Sub
y aqui las imagenes para que vean la identacion:
11 - copia el libro MOSTRADOR1 en la misma carpeta y nombra como MOSTRADOR2
12 - Si abres los dos archivos en simultaneo y ejecutas el form, te daras cuenta que cualquiera de los dos archivos actualizan al libro de datos sin duplicar la informacion guardada.
En mi pc excel no me permite tener varias instancias pero si pruebas a abrir los archivos en dos pc's conectadas en red, te daras cuenta de su funcionamiento.
Asi, el archivo de datos puede estar en la compu del jefe y los formatos en las compus del mostrador; ambos archivos trabajarian con los mismos datos sin temor a duplicar la informacion.
- La direccion 192.168.0.2 que aparece en la imagen corresponde a la direccion de red en donde se encuentra el archivo de datos. ESTA DIRECCION LA DEBES CAMBIAR con la ip correspondiente a tu red.
- Si tienes dudas de algunos conceptos, entonces visita mis post anteriores para salir de dudas.
En esta direccion puedes encontrar el archivo con las carpetas, archivos e imagenes usados en el post.
http://sdrv.ms/1e83NE1