por En nuestros trabajos muchas veces dependemos de diferentes tecnologías para obtener un determinado resultado, Excel nos es la única herramienta con la que tenemos que trabajar, un ejemplo es el uso de SQL o de Access a la hora de extraer, tratar y mostrar la información.
De hecho, en el ejercicio que voy a exponer como ejemplo, trataré de explicar como se puede vincular una tabla de Access a Excel y que, mediante una macro, la podamos actualizar en nuestra hoja desde los datos que tenemos en Access.
Vamos allá 🙂
Vamos a suponer que tenemos en una tabla de Access los datos por poblaciones del número de hombre y mujeres que hay en España. (los datos que muestro los he tomado del INE, están publicados y disponibles en su web). Vamos a suponer que esos datos se actualizan cado año de forma automática y dado que tenemos una aplicación que hemos desarrollado en Excel y que necesita disponer de los datos que se recogen en esa base de datos de Access (ACCDB), resultaría conveniente que pulsando un solo botón, nuestra información se actualizase al momento, sin necesidad de importar o copiar y pegar la información de forma manual.
Para ello he creado una base de datos a la que llamaré «DATABASE» y que recoge la tabla con la información de las provincias, de esta forma:
Una vez que tenemos la base de datos bien definida y completa, procedemos a abrir la hoja Excel donde vamos a escribir la macro que nos importará y actualizará la información desde Access. Antes de entrar en el programador vamos a colocar un botón para ejecutar la macro. Así:
Como siempre, debéis ir a la pestaña Programador > Insertar> Controles de formulario y pulsáis en «Botón (Control de formulario)». Aprovechando que estoy en la hoja y dado que la macro lo que va a traer es solo la información y no el formato, con la herramienta de relleno coloreo de gris y pongo en formato de letra «negrita» el cabecero de la tabla.
Ahora vamos a escribir la macro, para ello abrimos el programador de VBA y creamos un módulo nuevo:
Y entonces escribimos el siguiente código:
Sub actualizar_datos()
'ACTUALIZAMOS BASE DE DATOS
Dim path_Bd As String
Dim cnn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim strDB, strSQL As String
Dim strTabla As String
Dim lngCampos As Long
Dim i As Long
Dim bBien As Boolean
On Error GoTo ControlError
bBien = True
'CONECTAMOS CON LA BASE DE DATOS DE ACCESS Y ABRIMOS CONSULTA
path_Bd = "G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb"
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Properties("Data Source") = path_Bd
'EN CASO QUE LA BASE DE DATOS TENGA UNA CONTRASEÑA, SE DEBE PONER AQUÍ
cnn.Properties("Jet OLEDB:Database Password") = ""
cnn.Open
strTabla = "BASE"
strSQL = "SELECT * FROM " & strTabla & " "
recSet.Open strSQL, cnn
'COPIAR LOS DATOS A LA HOJA
Worksheets("Hoja1").Select
'LIPIAMOS DATOS DE EXCEL ANTES DE ACTUALIZAR
limpiardatos = Sheets("Hoja1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Hoja1").Range("A2:z" & limpiardatos).ClearContents
'GRABAMOS NUEVA BASE DE DATOS DE ACCESS
Sheets("Hoja1").Cells(2, 1).CopyFromRecordset recSet
'COPIAMOS RÓTULOS
lngCampos = recSet.Fields.Count
For i = 0 To lngCampos - 1
Sheets("Hoja1").Cells(1, i + 1).Value = recSet.Fields(i).Name
Next
'DESCONECTAMOS
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
Sheets("Hoja1").Select
MsgBox "LA BASE DE DATOS HA SIDO ACTUALIZADA."
Salir:
On Error Resume Next
If Not bBien Then
MsgBox "NO SE HA PODIDO ACTUALIZAR LA BASE DE DATOS, INTÉNTALO MÁS TARDE."
End If
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
ControlError:
bBien = False
Resume Salir
End Sub
En cada uno de los pasos os he ido comentando (en verde) qué es lo que hace la macro en cada momento. Antes de asignar la macro al botón de la hoja, debemos hacer los siguientes cambios en las referencias del proyecto. Como muestro en la siguiente imagen, pulsamos en Herramientas y luego en Referencias:
Una vez que pulsamos en Referencias, se nos abre un cuadro de opciones y debemos marcar las mismas que os muestro, de lo contrario, la macro no funcionará:
Una vez que tenemos hemos hecho esto la macro ya está totalmente operativa y la hoja funcionará perfectamente.
Un dato importante, de la macro que os acabo de mostrar es la ruta de acceso a la base de datos, veamos que es lo que aparece:
'CONECTAMOS CON LA BASE DE DATOS DE ACCESS Y ABRIMOS CONSULTA
path_Bd = "G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb"
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Properties("Data Source") = path_Bd
cnn.Properties("Jet OLEDB:Database Password") = ""
cnn.Open
strTabla = "BASE"
strSQL = "SELECT * FROM " & strTabla & " "
recSet.Open strSQL, cnn
La ruta que aparece en rojo la debéis modificar antes de probar la macro, en mi PC dispongo de unidad G (como disco externo), y el ejemplo lo tengo en una carpeta llamada PRUEBAS ACCESS y a su vez en otra carpeta denominada CONEXIÓN ACCESS Y EXCEL, dentro está la base de datos «DATABASE.accdb»
Es importante que lo recordéis, debéis modificar la ruta y especificando donde se encuentra la base de datos de Access en vuestro equipo, de lo contrario, no funcionará.
Tan solo queda asignar la macro al botón y listo, ya tenéis el proceso de actualización de datos totalmente preparado. Así es como funcionaría:
Los ejemplos de este ejercicio están basados en Excel 2010 y Access 2010, en versiones anteriores puede dar problemas, para Access 2003 (archivos .mdb) abría que modificar la macro donde pone:
cnn.Provider = «Microsoft.ACE.OLEDB.12.0» se debe poner: cnn.Provider = «Microsoft.Jet.OLEDB.4.0»
Os dejo el archivo de ejemplo, lo subo con un archivo .RAR con la carpeta entera y desde Google Drive dada la extensión de la Access que no está permitida en WordPress.
Descarga el archivo pulsando en: CONECTAR BASE DATOS CON OLEDB
Hola estimado, me ha funcionado perfecto con tablas.
Pero que se puede hacer si necesito rescatar los datos desde una «Consulta» (Query)??
es Posible?
Hola leonbrave :
Me alegro te funcionase correctamente la conexión con las tablas. Supongo que tu pregunta se refiere a si en vez de ser una tabla, se puede conectar y actualizar una consulta de Access. La respuesta es sí y además muy sencillo, si utilizas el código del ejemplo del blog, que a continuación reproduzco, tan solo tienes que sustituir en la parte «strTabla = «BASE» que es el nombre con el que denominaba la tabla de access por el nombre de la consulta. Imagina que a la consulta le llamas, «CONSOLIDADO», pues quedaría así:
path_Bd = «G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb»
cnn.Provider = «Microsoft.ACE.OLEDB.12.0»
cnn.Properties(«Data Source») = path_Bd
cnn.Properties(«Jet OLEDB:Database Password») = «»
cnn.Open
strTabla = «CONSOLIDADO»
strSQL = «SELECT * FROM » & strTabla & » »
recSet.Open strSQL, cnn
Espero haberte ayudado.
Un saludo y gracias por participar.
Hola estimado segu
de antemano muchas gracias por tus aportes, pero la verdad no he logrado realizar la conexión con una consulta de unión (strTabla = «ConsultaNitConciliaciones»), he seguido todos los paso pero no realiza nada, se sale por el manejador de errores y no ejecuta la consulta, le agradecería mucho de su ayuda. Muchas Gracias
Buenas noches , quiero ponerlo en un listbox dentro de un userform, com puedo hacerlo?
Hola José Luis,
Para poder analizar mejor tu consulta puedes enviar un ejemplo visual en una hoja Excel explicando cómo tienes la información y que quieres que se muestre en el listbox a excelsignum@yahoo.es De todas formas, lo ideal es que el listbox cargo los datos desde una hoja en la que previamente has importado los datos con la herramienta. Pero necesito ver la info que tienes. Saludos
Buenas noches, tengo una gran inquietud de la cual quisiera que me ayudaran por favor… Tengo un excel en la cual muestra las capacitaciones de personas (identificadas con un RUT) las cuales las realizan en un mes determinado y queda registrado en la tabla de datos… Ocurre un problema, la duración de una capacitación es de 12 meses y al mes 12 se debe volver a capacitar al personal, hay casos que se deben identificar cuando las capacitaciones se realizan antes de los 12 meses, por favor alguien me ayude, no entiendo mucho de programación y es un registro enorme de datos… Gracias
Hola Gonzalo,
Necesito que me envíes un ejemplo de cual es tu necesidad. Puedes enviar un archivo a excelsignum@yahoo.es indicando de forma clara que datos son los que tienes y como te gustaría que quedasen.
A la espera de tus comentarios.
Saludos
Buenas noches. Quisiera recobir su apoyo en lo siguiente: manejo una hoja de excel en la que requiero que de la celdas h12 hasta las celdas k62 no puedan ser modificadas por ningun usuario. Las restantes si, ya que semanal se introducen datos en la que las celdas que no se requieren que modifiquen arrojan resultados. ( aclaro que estas celdas que menciono que se requieren ser bloqueadas tienen formulas para que arrojen resultados) espero pronto su reapuesta, gracias
Hola Alejandro,
Puedes realizar lo que indicar accediendo al formato de la hoja seleccionarla toda y con botón derecho desproteger todas las celdas. Luego seleccionas el rango que quieres proteger y haces lo mismo, botón derecho y marcar «proteger».
Una vez que hayas realizado estos pasos, debes ir a la cinta de opciones en la pestaña «revisar» y pulsar en proteger hoja.
De esta forma solo se podrán modificar las celdas que previamente has desprotegido.
Si vas a trabajar con macros, deberás tenerlo en cuenta, dado que hay que considerar la protección de la hoja. Si solo son fórmulas, no hay problema, así puedes trabajar perfectamente. Si quieres enviar el archivo puedes hacerlo a excelsignum@yahoo.es
Saludos.
Buenos dias mi estimado.
Gracias por su respuesta, pero efectivamente si manejo macro y la opcion que me dio funciona pero al accionar la funcion del macro me arroja un error, agradeceria mucho si me ayudara con esto. Gracias
Hola Alejandro, entonces te comparto el siguiente post del blog donde se trata precisamente de eso,
https://excelsignum.wordpress.com/2013/07/21/ejecutar-macros-en-libros-protegidos-con-contrasena/
En este ejemplo se trata de proteger el libro, para el ejemplo de proteger la hoja, solo tienes que cambiar en la macro: ActiveWorkbook. por ActiveSheet.
Es decir, sería esto:
Sub Macro_hoja_protegida()
‘Desprotegemos el libro introduciendo la contraseña.
ActiveSheet.Unprotect «contraseña»
‘ Pon aquí la macro de tu proyecto
‘una vez que acabe el proceso volvemos a seleccionar la hoja.
Sheets(«Hoja1»).Select
‘volvemos a aplicar la contraseña.
ActiveSheet.Protect «contraseña»
End Sub
Échale un vistazo al post.
Saludos
Hola, Buenos dias, tengo una situacion, la cual no se la mejor forma de hacerlo, es la siguiente:
En una intranet hay cientos de computadoras conectadas, cada una de ellas tiene un archivo .mdb, cada archivo tiene varias tablas (la que me interesa es la primera de ellas que se llama «Error», en una carpeta logre juntar cada archivo .mdb, ahora bien lo que quiero hacer es conectar cada una de las tablas «Error» a un archivo denominado «Reporte» en Excel en el cual tengo un formato, lo que quiero es hacer una conexion de cada tabla hacia excel y que al modificar un dato en excel se modifique en el archivo .mdb y viceversa, Gracias de antemano.
Hola Irving,
Leyendo lo que comentas, no me queda claro cúal es la disposición de los datos en el archivo Excel denominado «Reporte». Supongo que en cada pestaña iría cada una de las tablas «Error». De ser así, puedes utilizar la macro del post para importar cada una de las tablas mdb al archivo Excel.
Para realizar el otro trabajo, es decir modificar cada uno de esos archivos y grabar los datos en Access de nuevo, tendrías que utilizar una macro que conecte Excel con Access y exporte las tablas de nuevo con los cambios realizados. Sobre este punto no tengo desarrollos realizados, pero sí te puedo orientar a otro blog donde se hace un buen e interesante planteamiento, se trata de JLD Excel en Castellano de Jorge L. Dunkelman, y en concreto el post de referencia sería este: http://jldexcelsp.blogspot.com.es/2009/06/transferir-datos-de-excel-access_11.html
Otra alternativa, puede ser, realizar una consulta con todos los archivos Error, y disponer los datos de forma que los puedas importar a Excel en una sola hoja «reporte» (con la macro de mi ejemplo puedes importar consultas). Una vez que lo tengas en Excel, y con una estructura adecuada, realizar una macro que pueda leer los datos de la consulta y trasladarlos a cada mdb.
Sería necesario, ver la estructura de la información, qué consulta en Access puede dejar la información mejor para posteriormente modificarla en excel e importarla …
También se podría valorar realizar todo en Access, vinculando tablas y consultas que se actualicen automáticamente.
Es un trabajo que requiere un análisis importante y tiempo para contrastar datos 🙂
Hola, gracias por el tutorial, sin embargo no me abre la base de datos, tengo todos los componentes que indicas tikeados, sin embargo aún así no lo hace. Al depurar el código se cae en la línea cnn.Open y automáticamente envía el mensaje «no se ha podido actualizar…..» Puedes ayudarme por favor??? Desde ya muchas gracias,
Hola César:
Has comprobado lo siguiente?
– La ruta al archivo está correctamente escrita?
– El archivo tiene clave? si la tiene debes especificarla en la macro.
– El nombre de la tabla es la misma que está en la macro?
– Has codificado las referencias en el archivo de Excel en caso de estar utilizando uno diferente al de la descarga?
– La base de datos es mdb o accbd. si es mdb debes utilizar cnn.Provider = “Microsoft.Jet.OLEDB.4.0
Verifica esto, si ves que sigue el error, y para descartar problemas, puedes enviarme la base de datos (una muestra o ejemplo) en access y el excel con la macro que estás implementando.
excelsignum@yahoo.es
Saludos 🙂
Muchas gracias por responder tan pronto.
Bueno te comento que lo que hice en realidad fue tomar tu ejemplo, copiarlo y ejecutarlo tal cual (archivo excel y access), solo cambié la ruta del archivo donde reside mi db Access. Incluso también la guardé con la antigua extensión MDB cambiando en el código el objeto OLE pero aún así no abre.
Muchas gracias espero que puedas sacarme de esta!!!
Hola, necesitaría ver la hoja excel, doy por supuesto que el access es una tabla o consulta en accdb. Qué versión tienes de Excel?. En la hoja no hace falta que tenga datos, tan solo la macro. Lo ideal seria contar con e access y el Excel para verificar que todo funciona.
Trato de responderte por la noche!
Saludos.
Tengo la versión 2010. Te enviaré ambos archivos por correo, muchísimas gracias. Te pasaste.
Amigo mío, muchísimas gracias por el tiempo y el apoyo entregado, todo salió de maravillas, creo que tendré que volver a lo mío que es el desarrollo….hace ya más de 10 años que no programaba….así que de cabeza nuevamente a los manuales para volver a ocupar mi lugar…
Un fuerte abrazo!!!
Hola César, me alegro mucho que todo funcione correctamente!! Pues claro que sí, siempre está bien volver a programar, aunque pasen años la base siempre está ahí. Un poco de teoría y práctica y estarás 100%
Un abrazo y ya sabes, si tienes alguna consulta, estoy a tu disposición 🙂
Hola buen dia, gracias por la herramienta tan util que has dejado por aca. Ahora tengo una pregunta, como puedo hacer si lo unico que quiero es una macro para que descargue una tabla de access a excel? No ocupo actualizar solo descargar para hacer reportes. Gracias
Hola Jona,
Pues la respuesta es exactamente el código del ejemplo, lo que hace es descargar desde Access una tabla o una consulta y llevarla a excel, a este procedimiento lo denomina actualizar, que es lo mismo que descargar. Si te da un error, puede ser debido a varios motivos, por experiencia puede ser lo comentado en una de las respuestas a este hilo:
– La ruta al archivo está correctamente escrita?
– El archivo tiene clave? si la tiene debes especificarla en la macro.
– El nombre de la tabla es la misma que está en la macro?
– Has codificado las referencias en el archivo de Excel en caso de estar utilizando uno diferente al de la descarga?
– La base de datos es mdb o accbd. si es mdb debes utilizar cnn.Provider = “Microsoft.Jet.OLEDB.4.0
Y por último, cómo se llama la tabla o consulta?, existen algunos caracteres que pueden provocar errores.SI puedes y quieres que lo vea detenidamente, puedes enviar un correo con los datos de ejemplo (access y el excel con la macro) al siguiente correo: excelsignum@yahoo.es
Saludos.
Hola gracias por el tiempo dedicado.
No logro hacer la conexión, primeramente intento modificar los parametros en el excel de ejemplo y me da simplemente el error de que intente más tarde, como si todo el codigo estuviese OK menos el nombre de la tabla, o la ubicación de la BD..
Cuando intento hacer el modulo desde 0, en las referencias no encuentro: Microsoft Forms …., ni Microsoft ….recordset 2.8, Y no tengo idea de porqué no están ahí, sin embaro reviso las referencias del fichero de ejemplo y ahí estan!! jaja no entiendo nada..
Espero me ayuden sludos..
Hola Dariel,
¿Cuál es el nombre de la tabla que estás intentando vincular?. Por otra parte, que versión de Access estás utilizando, es una tabla mdb o accdb?. Puedes enviar una muestra en una carpeta con los dos archivos y verifico si existe algún problema. Puede que no algunas referencias no las tengas instaladas, Forms no es relevante, pero el recorset sí.
Seguro que es un problema con el nombre de la base de datos. Envía el ejemplo que te está causando el problema, e indica versión de Excel y de access.
mail: excelsignum@yahoo.es
Saludos.
Gracias Segu por la pronta respuesta, revisé y logro hacerlo quitándole la contraseña a la base de datos, aunque la había configurado bien cnn.Properties(«Jet OLEDB:Database Password») = «***********» con la contraseña que tenía pero no se porqué no funciona… no se pueda haber algún error en el código?? saludos!
Edito el mensaje. El problema que puedes estar teniendo es el siguiente, se debe al cifrado de la contraseña: debes hacer las siguientes modificaciones en Access, entra en OPCIONES, luego vas a CONFIGURACIÓN DE CLIENTE y en donde pone METODO DE CIFRADO, debes elegir USAR CIFRADO HEREDADO. Así funcionará casi con seguridad.
Prueba y me dices.
Saludos.
Muchas gracias Segu ! cambié a cifrado heredado, volví a poner contraseña y todo OK.. saludos!!!
Buen dia, la verdad necesito de tu ayuda, espero ya no sea muy tarde, es que es sobre la macro que hiciste para realizar la actualizacion de datos en acces con excel, la verdad yo quisiera saber como hago para modificar esa base de datos desde excel, yo tengo el codigo para conusltar la informacion pero quiero saber como hago para despues de que haga la consulta modificar cualquier dato y volverle a dar exportar y que quede actualizada la informacion en acces.
De antemano muchisimas gracias.
Hola David,
Todavía no he realizado ninguna entrada relacionada con la modificación de Access desde Excel, sin embargo (tomo nota, una de las próximas entradas será esa), te adjunto un artículo de microsoft donde tienes el ejemplo de un código en DAO:
https://support.microsoft.com/es-es/kb/151511
Echa un vistazo a ver que te parece y si puedes implementarla. En cualquier caso, en breve realizaré una entrada relacionada con tu pregunta.
Saludos.
hola amigo yo necesito que sea al revés que excel envíe datos a access
Hola Armando,
Puedes encontrar información aquí: https://excelsignum.com/2016/03/31/exportar-datos-de-excel-a-access-mediante-ado/
Exportar de Excel a Access, espero que te sea de utilidad. Saludos.
Buen día, quisiera realizarte una consulta;
Quisiera extraer información de una tabla en access seleccionando una opción mediante un CUADRO COMBINADO desde excell.
Las opciones que tengo en el cuadro combinado son: despachado, anulado, pendiente, quisiera que al momento de seleccionar uno de ellos me muestre en excel toda la información con respecto a ese estado.
Muchas Gracias.
Hola William,
Siguiendo el ejemplo que he propuesto arriba, debes modificar
strTabla = "BASE"strSQL = "SELECT * FROM " & strTabla & " "
que está en el código, por esto otro:
VCombo = Sheets(1).ComboBox1.ValuestrSQL = "SELECT BASE.*" & _
"FROM Base " & _
"WHERE (BASE.CPRO)=" & "'" & VCombo & "'"
Donde VCombo captura el valor del combobox1 en la hoja 1, si es necesario cambia los valores.
En la base de datos de access, consulta si el campo CPRO tiene algún calor incluido en el comobobox. Puede modificar la base de datos del ejemplo, y en el campo provincia incluir algunos valores como despachado, anulado, pendiente y te devolverá los registros filtrados.
Saludos.
Hola no sé si el sitio, pero necesito ayuda con esta macro.
Cuando la ejecuto funciona correctamente, pero luego al actualizar datos me pide user y contraseña, pero la BBDD no tiene.
Muchas gracias.
Perdona, la macro que tengo es esta, la otra tenía una prueba.
Sub Macro1()
‘
‘ Macro1 Macro
‘
With ActiveWorkbook.Connections(«Datos6»).OLEDBConnection
.BackgroundQuery = True
.CommandType = xlCmdTableCollection
.Connection = «OLEDB;Provider=Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Notebook01\Google Drive\Varios\Analista automatico\Clientes\Primer Impacto\Móvil\Datos_6.MDB;Mode=Share Deny Write;Extended Properties=;Jet OLEDB:Database Password=»»;Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False»
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = «»
.SourceDataFile = _
«C:\Users\Notebook01\Google Drive\Varios\Analista automatico\Clientes\Primer Impacto\Móvil\Datos_6.MDB»
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
.CommandType = xlCmdTableCollection
.CommandText = «CABECERA,CARGOS_FACTURA,CARGOS_PREFACTURA,CENTRO_COSTE,CLIENTE,CUOTAS,DESCUENTOS_FACTURA,DESCUENTOS_PREFACTURA,DESCUENTOS_TELEFONO,DETALLE,FACTURA,PREFACTURA,RESUMEN,SERVICIO,SUB_CCOSTE,TELEFONO,TOTALES_CLIENTE,TOTALES_SERVICIO,TOTALES_SOPORTE,VARIOS»
End With
With ActiveWorkbook.Connections(«Datos6»)
.Name = «Datos6»
.Description = «»
End With
‘
End Sub
Hola Kiko, tal y como te comentaba, la mejor solución sería adaptar la macro que has enviado a la que propongo en este artículo. En el caso de realizar la conexión con access desde excel y por la cinta de opciones utilizando el grabador, lo mejor es que crees la conexión (o varias conexiones) y realizar precisamente ahí la macro, es decir, ejecutar un refresh de la conexión.
Para analizar cual puede ser problema que afecta a tu proyecto necesitaría realizar pruebas sobre la base de datos (o similar). Piensa en realizar la adaptación, merece la pena.
Saludos.
Hola, tengo 2 preguntas: 1.- ¿Cómo abro directamente un formulario de Access desde una macro en Excel? y 2.- ¿Como actualizo un campo en una tabla desde una celda de Excel usando la llave de la tabla? Gracias y hasta luego.
Hola Francisco:
Respondo a tus preguntas:
Respuesta a la pregunta1:
Sub Abrir_form()Dim cn As Object
Dim str As String
On Error Resume Next
Set cn = GetObject(, "Access.Application")
If cn Is Nothing Then
Set cn = GetObject("", "Access.Application")
'indicar la ruta a la base de datos
cn.OpenCurrentDatabase "C:\Ruta a la base de datos"
'indicar la base base de datos
str = cn.CurrentDb.Properties("nombre de la base de datos")
cn.DoCmd.Close cnForm, str
'indicar el nombre del formulario
cn.DoCmd.OpenForm "nombre del formulario"
cn.UserControl = True
Set cn = Nothing
End If
AppActivate "Microsoft Access"
End Sub
Solo debes indicar la ruta en la que se encuentra tu base de datos, el nombre de la base de datos y el nombre del formulario que quieres abrir.
Pregunta2:
Sub UpdateAccess()Dim conAccess As ADODB.Connection
Dim Origen As String, Destino As String
Dim conExcel As String, obSQL As String
'especificar nombre de tablas en access y excel
Destino = "[nombre base de datos access]"
Origen = "[hoja de excel con los datos a introducir, ejemplo: Hoja1$]"
conExcel = "[Excel 8.0;HDR=Yes;DATABASE=C:\cadena de conexion excel.xls]." & Origen
'intruccion sqlpara actualizar dato
obSQL = "INSERT INTO " & Destino & " SELECT * FROM " & conExcel
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\cadena de conexion archivo access.accdb;"
.Open
.Execute obSQL
.Close
End With
End Sub
Al igual que la macro anterior, solo debes indicar el nombre de la base de datos de Access, la hoja (o nombre de la hoja, donde están los datos a actualizar en Excel), la ruta al archivo de Access y al de Excel.
Puede que sea de tu interés esta entrada:
https://excelsignum.com/2016/03/31/exportar-datos-de-excel-a-access-mediante-ado/
Por cierto, acuérdate de habilitar las referencias en el editor de VBA!
Saludos!
Hola, muchas gracias por tu respuesta, no tengo experiencia y he intentado hacerlo, los resultados son:
Para la opción 1 solo me abre la aplicación de Access pero no abre la base de datos indicada y mucho menos el formulario, no se que me faltará:
Sub Abrir_Form()
Formato Macro
Dim cn As Object
Dim str As String
On Error Resume Next
Set cn = GetObject(, "Access.Application")
If cn Is Nothing Then
Set cn = GetObject("", "Access.Application")
'indicar la ruta a la base de datos
cn.OpenCurrentDatabase "C:\Pruebas\"
'indicar la base base de datos
str = cn.CurrentDb.Properties("Pruebas.accdb")
cn.DoCmd.Close cnForm, str
'indicar el nombre del formulario
cn.DoCmd.OpenForm "Maestro"
cn.UserControl = True
Set cn = Nothing
End If
End Sub
En el segundo caso Al llegar a la sentencia .Open me envía un error ‘-2147467259 (800004005)’ en tiempo de ejecución: Error de Automatización.
Puede ocurrir que no tiene toda la información sobre la base de datos y la tabla a actualizar:
Sub UpdateAccess()Dim Origen As String, Destino As String
Dim conExcel As String, obSQL As String
Dim conAccess As ADODB.Connection
'especificar nombre de tablas en access y excel
' cómo le indico que es la tabla maestro de la base datos Pruebas.accdb?
Destino = "[Maestro]"
Origen = "[Proceso Hoja2$]"
conExcel = "[Excel 8.0;HDR=Yes;DATABASE=C:\cadena de conexion excel.xls]." & Origen
'intruccion sqlpara actualizar dato
obSQL = "INSERT INTO " & Destino & " SELECT * FROM " & conExcel
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\cadena de conexion archivo access.accdb;"
.Open
.Execute obSQL
.Close
End With
End Sub
Gracias por tu ayuda.
Hola Francisco:
Respecto a las consultas que me indicas, la primera macro debería ser así:
En la ruta se indica la extensión del archivo, .accdb esto lo puedes encontrar haciendo clic con el botón derecho encima de tu archivo de Access y en la pestaña SEGURIDAD copias lo que detalla el nombre de objeto.
La base de datos sin la extensión «.accdb»
Los nombres deben ser iguales a los que tiene la base de datos de Access (respetando si están en mayúsculas o minúsculas).
Así debería de funcionarte:
Sub Abrir_Form()
Dim cn As Object
Dim str As String
On Error Resume Next
Set cn = GetObject(, "Access.Application")
If cn Is Nothing Then
Set cn = GetObject("", "Access.Application")
'indicar la ruta a la base de datos
cn.OpenCurrentDatabase "C:\Pruebas.accdb"
'indicar la base base de datos
str = cn.CurrentDb.Properties("Pruebas")
cn.DoCmd.Close cnForm, str
'indicar el nombre del formulario
cn.DoCmd.OpenForm "Maestro"
cn.UserControl = True
Set cn = Nothing
End If
End Sub
Respecto a la segunda macro
Te falta la cadena de conexión a la base de datos de Access: C:\Pruebas.accdb (verifica que esta es la conexión)
Y al archivo de Excel: C:\cadena de conexion excel.xls
Si es el mismo archivo de donde tienes el formulario, el nombre de la base de datos es «Pruebas» y no «Maestro»
Verifica que has introducido las referencias en el editor VBA
Sub UpdateAccess()Dim Origen As String, Destino As String
Dim conExcel As String, obSQL As String
Dim conAccess As ADODB.Connection
‘especificar nombre de tablas en access y excel
‘ cómo le indico que es la tabla maestro de la base datos Pruebas.accdb?
Destino = "[Maestro]"
Origen = "[Proceso Hoja2$]"
conExcel = “[Excel 8.0;HDR=Yes;DATABASE=C:\cadena de conexion excel.xls].” & Origen
‘intruccion sqlpara actualizar dato
obSQL = “INSERT INTO ” & Destino & ” SELECT * FROM ” & conExcel
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=C:\Pruebas.accdb;”
.Open
.Execute obSQL
.Close
End With
End Sub
De todas formas, a raíz de tu consulta he incluido en la web un ejemplo de cómo hacerlo, puedes bajártelo y comparar. Aquí ya he incluido las referencias en el editor VBA
https://excelsignum.com/2016/07/08/anadir-registros-en-access-desde-excel-con-vba/
Hola Segu,
Funcionaron muy bien ¡GRACIAS!
Una pregunta adicional: ¿Al abrir el formulario es posible que solo se presente la pantalla emergente sin la base de datos?
Hasta luego.
Hola Francisco,
Respecto a la pregunta que realizas, existen algunos métodos para ocultar objetos de Access mediante macros, aunque personalmente no lo recomiendo, puedes correr el riesgo (altamente probable) de cargarte una base de datos. Mi consejo es que entres en la base de datos de access y desde ahí ocultes el panel de navegación directamente: Archivo > Opciones > Base de datos Actual > Navegación y desmarcas «Mostrar Panel de Navegación.
De esta forma te aseguras que la información está oculta a los demás usuarios.
Hola Segu.
Entendido, muchas gracias por tu ayuda.
Hasta luego.
Hola buenas tardes.
Quiero realizar una busqueda en una tabla de Access con un formulario de Excel, este es el código que estoy usando pero no puedo hacerlo.
Private Sub Buscar_Click()
Set cnn = New ADODB.Connection
With cnn
.Provider = «microsoft.jet.oledb.4.0»
.ConnectionString = «data source=\\servermad\Sistemas\Gabriel\Base de datos21.mdb»
.Open
End With
Set rst = New ADODB.Recordset
Sql = «select * from Directorio»
With rst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
‘.Open Sql, cnn, , , adCmdText
‘.Open
End With
Dim sSQLQuery As String
sSQLQuery = «SELECT *» & vbCr
sSQLQuery = sSQLQuery & «FROM Servicio «» & vbcr»
sSQLQuery = sSQLQuery & «WHERE Folio = ‘» & Text_folioILE.Text & «‘» & vbCr
Dim adoRegistros As New ADODB.Recordset
adoRegistros.Open sFolio, adoConexion, adOpenStatic, adLockReadOnly
If adoRegistros.BOF = adoRegistros.EOF And adoRegistros.EOF = False Then
Text_folioILE.Text = adoconsulta!Folio
Text_NombILEBus.Text = adoconsulta!Nombre
End If
adoConexion.Close
Set adoConexion = Nothing
End Sub
Espero me puedas ayudar
Hola Gabriel,
Cuál es el error que te muestra?, Has probado a realizar el ejercicio con la macro que pongo en este ejemplo. Aquí importa la tabla completa, solo tendrías que modificar la consulta sql para que te trajese los datos según consulta.
Intento verificar si suceda algo con el código que me indicas, pero ya llevo unos años que no toco archivos mdb, casi todo lo hago con accdb.
Esa búsqueda la puedes hacer perfectamente con el código del ejemplo, si me envías un ejemplo con los dos archivos (el de access y el de excel) podría analizar la mejor alternativa.
excelsignum@yahoo.es
estos días estoy de vacaciones, en cuanto tenga un momento te responderé.
Saludos.
Gracias te he mandado un correo espero poderme dar a entender, de hecho tu código me sirvió pero ya sabes uno siempre quiere hacer más cosas jeje, un sludo
hola buena tarde.. tengo una duda, es posible hacer una busqueda con un formulario de excel, pero en mas de una db de access, son practicamente los mismo campos, pero son alimentadas por diferentes sistemas, y requiero hacer una busqueda pero es complicada hacerlo en cada una de las db y se facilitaria mucho tener una solo busqueda
Hola Edgard,
Esto normalmente se realiza directamente en Access, con una macro que realice la consulta.
https://support.microsoft.com/es-es/kb/113701
Todavía no lo he probado, con los resultados de esta consulta en Access, la clave sería importar estos resultados con la macro actual, en una sola consulta.
Durante estos días realizaré una prueba y te lo confirmo.
Saludos.
Hola Edgard,
Después de investigar las posibles alternativas a tu comentario, lo ideal es que realices un trabajo previo en Access, es decir, que desde la base de datos principal utilices la herramienta de importación de datos externos e importes la base (o bases) de datos que necesites y ahí selecciones las tablas o consultas que te interesen. De esta forma, tendrás todos los datos en la misma base de datos (y vinculados al resto de tablas) luego solo tienes que importarlos a Excel o iniciar una consulta SQL pero dentro de la misma base.
Es muchísimo más sencillo y cómodo.
Saludos
Ok, lo voy a intertar de esta forma, si lo habia pensado asi, solo que buscaba una alternativa que fuera mas practica, pero al parecer esta es la mas rapida y comodo
Tengo un problema, la tabla contiene más de 65000 registros, con lo que no deja copiarlo y pegarlo al portapapeles. Habría que hacerlo exportando la consulta a excel..
Se puede?
Gracias
Hola Álvaro.
La solución es sencilla. Solo tienes que guardar a hoja de Excel en la que tienes la macro como xlsx donde el límite de filas es de 1.048.576
La macro funciona igual para xls como para xlsx. Eso sí, deberás guardarla como libro de Excel habilitado para macros.
Saludos
Gracias, pero al hacer la Macro me salta este error «NO SE HA PODIDO ACTUALIZAR LA BASE DE DATOS, INTÉNTALO MÁS TARDE»
Es que te dije 65.000 registros, y en realidad son 425.000…
Hola Álvaro.
No debería ser problema. Si quieres te lo puedo revisar. Envíame un ejemplo de la base de datos en access y el Excel que vas a utilizar para importar. En un fichero comprimido rar.
Solo necesito un ejemplo de la estructura de los datos.
Saludos
Buenas Segu, fabulosa la aportación. Funciona perfectamente.Pero una pregunta como seria para que la ejecución sea desde access, bien con un botón o un evento. Estoy haciendo una aplicación en access en las que unas tablas (resultados1 y resultados2 )alimentadas desde un formulario las envío a excel con el evento al salir del formulario,porque tengo que hacer unos cálculos y lo hago de la siguiente manera.
Sub exportaexcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, «resultados1», «C:\Users\Joaquin\Desktop\autonomo.xlsm», True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, «resultados2», «C:\Users\Joaquin\Desktop\autonomo.xlsm», True
End Sub
Me crea las dos hojas en autonomo.xlsm y pasa los datos de access.
Porque la idea es que una vez excel recibe los datos realizar unos cálculos y automáticamente recupere esos cálculos en access para realizar un informe.
Gracias Joaquín;
Me alegro que te resulte de interés el código 🙂
Sobre lo que comentas, creo (si entiendo correctamente lo que explicas) que lo tienes fácil, dato que estás utilzando el objeto DoCmd para exportar con el método acExport, puede hacer exactamente lo mismo, pero con el método acImport, siguiendo tu ejemplo:
Sub importaexcel()DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados1”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados2”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
End Sub
Sin embargo, a la hora de importa tendrás el siguiente problema, y es que los registros se irán duplicando a medida que vayas importando, dado no estás actualizando, sino que simplemente importas. Una posible solución a este problema puede ser, eliminando la tabla que tienes en Access antes de importar la de Excel,
On error resume nextDoCmd.DeleteObject acTable, "resultados1"
DoCmd.DeleteObject acTable, "resultados2"
On error goto 0
Es decir: para exportar sería la macro que indicas, y para importar sería esta (completa):
Sub importaexcel()DoCmd.DeleteObject acTable, "resultados1"
DoCmd.DeleteObject acTable, "resultados2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados1”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados2”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
End Sub
Ojo!! sé cuidadoso y haz algunas pruebas, estas trabajando con instrucciones que borran tablas.
Otra forma, menos agresiva es eliminar solo los registros de la tabla de access y luego actualizar con los datos de la hoja de Excel. La instrucción para borrar los registros es:
DoCmd.RunSQL » delete * from resultados1 list»
y la macro sería:
Sub importaexcel()DoCmd.RunSQL " delete * from resultados1 list"
DoCmd.RunSQL " delete * from resultados2 list"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados1”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “resultados2”, “C:\Users\Joaquin\Desktop\autonomo.xlsm”, True
End Sub
Aquí, será Access quien te advierta que se van a borrar x registros antes de importar.
Ya me contarás si te sirve o necesitas otro tipo de desarrollo.
PD: doy por supuesto que en Access tienes dos tablas que se llaman resultados1 y resultados2
Saludos.
Gracias, por la respuesta, voy a probar todo lo que comentas. Ya estas es mis favoritos por lo que bien que lo explicas todos.
un saludo desde Murcia
Hola Segu, nuevamente gracias por tu dedicación. Te detallo lo que estoy haciendo.
Estoy intentando hacer un pequeño programa de contabilidad de autonomos, (registro de ventas, compras y gastos).
El registro de ventas se alimenta de un formulario factura, con productos en plan sencillo.
Al final hay un registro de facturas (fecha,numero,cliente, cif, base, %iva, iva y total factura.
En compras y gastos también hay un registro que se crea desde un formulario directamente de entrada de apuntes, con los campos fecha, factura, proveedor, cif, base, % iva, iva, total factura, y aparte campos con el importe de la base para determinar el tipo de gastos que (compras, alquiler, suministros, reparaciones, etc). Todo esto ya lo tengo montado y a falta de pequeños detalles me funciona como yo quiero. Hasta aquí perfecto, dentro de las limitaciones de un principiante.
Lo que quiero conseguir es hacer un informe de resultados (aquí detalle simplificado)
1 Trim. 2º Trim. 3º Trim. 4º Trim. Total Anual
Ventas
Compras
alquiler
Asesorias,
Suministros
ETC
Beneficio o perdida
Es decir quiero totalizar por trimestres todas ventas y gastos y no lo consigo hacer con consultas.He probado con consultas de tablas cruzadas pero no consigo los resultados.
Entonces pensé en volcar toda la información a excel, ingreso y ventas (resultados1) y compras y gastos (rtesultados2), en un evento al grabar los asientos. y hasta ahí bien.
En el libro de excel hay tres hojas, resultados1, resultados2, y datos que con la formula «SUMAR.SI.CONJUNTO, convienrte las dos hojas anteriores en una sola fila totalizando por trimestres cada cuenta (venta1T,venta2T,ventas3T,ventas4T,compras1T,etc)
y es esta hoja solo con dos filas, Titulos y datos la que quiero recuperar desde access en una tabla o consulta para que mi sirva de base para elaborar el informe.
Mucho rollo, creo, si quieres te paso un fichero con lo que tengo hecho.
Atentamente.
Hola Joaquín.
Ok, envíame el Access (con estructura de datos para realizar pruebas) , para ver si se puede realizar todo en Access.
También el Excel y miro cuál es la mejor solución.
En cuanto tenga un momento, reviso los archivos.
Saludos.
si yo tengo la base de datos y el macro de excel en mi computadora y quiero compartirlo con mis compañeros de trabajo, solo les creo un acceso directo del macro desde mi carpeta compartida y ellos podran actualizar los datos de la base de datos?
Hola Arturo:
La base de datos debe estar en la carpeta compartida, y cada uno de tus compañeros debería tener un archivo excel con la macro (donde se especificará la ruta a la base de datos), de forma que cuando la ejecuten obtengan la información actualizada, sea una tabla o una consulta. La idea es compartir información y para ello debes dejarla en un lugar común, la carpeta compartida, y luego que cada uno de ellos actualice esa información para sus propios procesos en su archivo.
Espero haber contestado a la pregunta. Muchas gracias por la consulta.
Un saludo!!
Hola Segu.
Tengo una pregunta si intente realizar la conexión con una consulta de anexar o de actualización y no me pega la información en el excel a que se debe.
Gracias y saludos.
Hola Alan:
Deberías poder importar la consulta directamente, la macro importa las consultas y las tablas de Access. Tendría que ver la base de datos y el archivo de Excel para poder verificar cualquier problema.
Puedes enviarme un ejemplo, con la misma estructura y nombres de los archivos (el contenido inventado) a excelsignum@yahoo.es
Saludos.
Hola SIGNUM !! Tengo una pregunta y no se si tengas ya el articulo al respecto. tengo varios libros de excel que son hojas de calculo de mis pacientes de nutrición. Llevo mas de 100 libros. Lo que hago es tenerlos en una carpeta y buscarlo por nombre y abrirlo. Hay hay alguna forma de generar alguna interface que me permita abrir un programa de base de datos y de ahí jalar mi libro de excel según el nombre ? Para que se vea mas profesional. Hay software del tipo, pero no me agradan del todo.
Hola Diego:
No comprendo qué es exactamente lo que necesitas. Es decir, tienes los libros de los clientes en una carpeta y los buscar con el nombre, luego abres el archivo y consultas. Y necesitas un programa que poniendo el nombre del clientes encuentre la información que necesitas consultando en cada uno de los libros, ¿es correcto?.
De ser así se podría realizar, pero todas las hojas de todos los libros deberían tener la misma estructura, concretar qué datos se quieren mostrar, si se quiere que sea en una hoja o en un formulario, etc, etc.
Se podría hacer, pero creo que lo realmente necesitarías es una base de datos en condiciones, en Excel o en Access, donde estén todos tus clientes registrados.
Por esta web tengo un formulario que gestiona clientes: https://excelsignum.com/2016/05/02/gestion-de-clientes-modificaciones/
Tendrías que adaptarlo a tu negocio modificando la programación.
Saludos.
Buenas tardes.
Muchas gracias por tu aporte, me ha servido de gran ayuda, pero sólo para bases de datos Access sin contraseña. Cuando tengo que trabajar con una base de datos Access con contraseña, no se realiza la conexión a la base de datos. he mirado en mil sitios, el tuyo es que que más claro lo explica, pero ni aún usando tu código de ejemplo consigo conectarme a una base de datos Access con contraseña. Mi versión de Microsoft office es la de 2010.
Por favor, cualquier luz al respecto?
Gracias por tu tiempo y tu dedicación
Hola Carmen:
Espero poder ayudarte. Doy por supuesto que has intentado introducir la contraseña en la parte de la macro:
cnn.Properties(«Jet OLEDB:Database Password») = «tucontraseña»
De todas formas intenta realizar la conexión sin la contraseña para descartar cualquier otro problema. Si es seguro que el problema es de la contraseña, puede ser por estos dos motivos:
1 – Que tengas un «-» en la contraseña, estilo «001-tabla2». Este problema se debe a que el operator «-«lo interpretará como un menos y no como un guión, `para que lo puede interpretar como un guión, debes utilizar los corchetes [ ], así:
cnn.Properties(«Jet OLEDB:Database Password») = «[001-tabla2]»
O también puede utilizar el guión bajo y no tendrás que usar los corchetes:
cnn.Properties(«Jet OLEDB:Database Password») = «001_tabla2»
2 – Si no usas los guiones en la contraseña y sigue sin funcionar, es posible que se deba al cifrado de la contraseña:
Para solucionarlo debes hacer las siguientes modificaciones en el Access que tiene la base de datos: entra en OPCIONES, luego vas a CONFIGURACIÓN DE CLIENTE y en donde pone METODO DE CIFRADO, debes elegir USAR CIFRADO HEREDADO.
Estoy seguro que con alguno de estos dos sistemas te funcionará.
No obstante, si sigues con el problema, me puedes enviar el Access, con una tabla, con un ejemplo inventado y con la contraseña introducida e inventada con el formato similar a la que tienes (indicándome cual es). Y realizaré una prueba para solucionar el problema. Pero antes de nada verifica las dos soluciones que te indiqué.
Saludos.
Excelente aporte para contribuir un poco seria mejor obtener el path directamente del libro para no asignar una ruta estatica.
«G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb»
path_Bd = ActiveWorkbook.Path + «\DATABASE.accdb»
Saludos.
Hola Rubén:
Efectivamente, siempre y cuando nuestro archivo/libro de Excel se encuentre en la misma carpeta que la base de datos de Access.
Saludos.
Hola buenas tardes, excelente post, algo asi ando buscando pero para una base de datos de Postgresql mediante consultas desde Excel. ¿Cree que se pueda actualizar la base de datos desde un formulario mediante un boton de comando?, porque excel tiene instrucciones de actualizar cada 20 minutos en egundo plano pero no lo hace. En la aplicacion que desarrolle se muestran unicamente los formularios de consulta que manipula el usuario. Lo que he pensado es poner un boton Actualizar, pero no he encontrado nada para excel y postgresql. Les agradecere si hay alguna respuesta positiva. Saludos cordiales.
Hola Victor:
Todavía no tengo nada publicado sobre este tema, pero te adjunto algunos sitios web que te pueden interesar:
http://stackoverflow.com/questions/13230456/connecting-excel-to-postgresql-via-vba
http://stackoverflow.com/questions/9117516/using-ado-in-vba-to-connect-to-postgresql
http://stackoverflow.com/questions/21895629/how-to-connect-vba-to-postgresql-and-run-query
Saludos!!
Hola, gracias por la informacion pero no es lo que busco. La conexion a la base de datos ya la tengo resuelta, lo inico que me interesa saber es el codigo en un formulario vba de excel que haga las veces de «actualizar todo» que tiene excel sin hacerlo manualmente, dicho de otro modo: dando clic en un boton de comando puesto en un formulario que haga este proceso. Un cordial saludo.
OK.
Ahora comprendo, puedes ejecutar un proceso colocando en thisworkbook el siguiente código:
Private Sub Workbook_Open()Application.OnTime Now + TimeValue("00:00:20"), "TUMACRO"
End Sub
Donde TUMACRO, es la macro en la que tienes la conexión. En el momento que abres el excel cada 20 minutos se ejecutará tu macro.
Saludos.
Estimado Segundo, se que este tema tiene mucho tiempo de publicado, y recien lo he podido desarrollar junto con tu ejemplo, está fabuloso
Buenas Segu…
He utilizado el código que indicas aquí para recuperar datos de unas Tablas de Access a un Excel.
Si la Base de Datos no tiene contraseña funciona perfectamente.
Pero si cifro la Base de Datos no hay manera de que funcione..
Me saca el siguiente error ( «Se ha producido el error ‘-2147467259 (80004005)’ en tiempo de ejecución: No es una contraseña válida.)
Evidentemente después de comentar la línea (‘On Error GoTo Fallo)
El codigo… aparte del resto… sería este…
Ruta_BD = «E:\00.-BD\BD_General.accdb»
Cnn.Provider = «Microsoft.ACE.OLEDB.12.0»
Cnn.Properties(«Data Source») = Ruta_BD
‘EN CASO QUE LA BASE DE DATOS TENGA UNA CONTRASEÑA, SE DEBE PONER AQUÍ
Cnn.Properties(«Jet OLEDB:Database Password») = «123»
Cnn.Open
Y se me queda parada dando el error en la línea de.. (Cnn.Open)
Y tengo activas las siguientes Referencias:
Todas las que indicas tu… menos la última (Microsoft Forms 2.0 Object Library)
No me aparece en el listado.
Puedes ayudarme??
Hola Pablo: Para solucionarlo debes hacer las siguientes modificaciones en el Access que tiene la base de datos: entra en OPCIONES, luego vas a CONFIGURACIÓN DE CLIENTE y en donde pone METODO DE CIFRADO, debes elegir USAR CIFRADO HEREDADO.
Es a única forma de que identifique contraseña. Así debería funcionarte. Lo de las referencias no pasa nada aunque no tengas forma marcado (es para formularios).
Prueba y luego de dices si te ha funcionado onda el cifrado heredado. Saludos
Bravoooo!!!!
Gracias… Si era eso… Pero he tenido que volver a cifrar la base de datos de nuevo.
He abierto la base de datos en Modo Exclusivo… Descifrado Base de Datos… Volver a abrir la Base de Datos en Modo Exclusivo… Y cifrado de Base de Datos con la Contraseña.
Por si le pasa lo mismo a otra gente…
Gracias Segu… Te dejaré de mano por si tengo mas dudas con lo que estoy haciendo.
Excelente muy pero muy agradecido por el aporte me funciono bastante bien gracias a esa excelente explicación, saludos
Hola, Solo me funciona con una tabla Local, habría forma de que pueda funcionar con una tabla vinculada a una lista de sharepoint?
Hola David:
Efectivamente, en local funciona correctamente. No ha probado a realizar la programación con SharePoint, pero en cualquier caso, lo investigaré.
Saludos.
HOLA, COMO REALIZO PARA QUE SOLO UN CAMPO DE LA BD ME APAREZCA EN UN COMBOBOX DE UN FORMULARIO EN EXCEL, GRACIAS
Puedes echar un vistazo a esta entrada, fíjate como cargo la información en el combo. Pasas el recordset al combobox y ya lo tienes,.
https://excelsignum.com/2016/05/01/combobox-dependientes-en-userform-con-sql/
Saludos.
Gracias por el aporte de la conexión y copia a Excel de una tabla. Necesito copiar dos tablas, por lo que copie la misma Sub donde una le dije que lo ponga en la hoja 2 y otra en la hoja3. En la primer corrida, me copia la primer tabla, en la segunda corrida, de repente envia el error que pusiste por default ó a veces, solo me copia un numero pequeño de columnas, no todas. A que se deberá?
Gracias por el aporte.
Hola Homero:
Asegúrate de cerrar la conexión al finalizar cada consulta:
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
No debería de generar errores.
Hola, espero tengas unos minutito, resulta que tengo un archivo de excel que consume una base de access, ambos estan alojados en dropbox para que varias personas puedan usar el excel, pero no quiero que los usuarios puedan abrir la base de access, entonces se me ocurrio protegerla pero al hacerlo el excel deja de funcionar porque detecta la base protegida… como hago para que al proteger la base de access el excel siga funcionando? desde ya te agradezo los minutos dedicados… saludos 🙂
Tienes que indicar en el proceso en el que te conectas a la base de datos de Access que desproteja el archivo. En los comentarios hay varios ejemplos de usuarios que han protegido y desprotegido sus bases de datos.
Saludos