none
Importar datos de Excel a Access 2007 RRS feed

  • Pregunta

  • Hola a todos:

    Estoy intentando importar y actualizar la tabla Balances a través de la importación de datos desde Excel, con lo que actualizo los datos del Ejer.01, Ejer:02, etc.

    Me encuentro que en la cláusula UPDATE hay algo que no se poner y es lo referente a la primera columna que es Cód_GC y que incluye los grupos contables cuyo campo es de texto. No se que tengo que poner en esa cláusula al final de la misma, para que actualice los registros importados que sean iguales a dicha columna, es decir, con el mismo código y si no, que me saque un mensaje indicando los registros que no ha podido importar porque no existen en el DatagridView o sea en el plan contable.

    Private Function cargar_hoja(ByVal cadena As String, ByVal area As String, ByVal rango As String, data As DataGridView) As Boolean
            Dim objHoja1 As Object
            Dim lngFila As Long
            Dim strvalores As String
            Try
                'Comprobamos si existe el archivo excel
                If System.IO.File.Exists(cadena) Then
                    Dim objetoDataset As System.Data.DataSet
                    Dim objetoDataadapter As System.Data.OleDb.OleDbDataAdapter
                    'Declaramos la conexión. Es importante saber del excel 97 al 2003 es una cadena por proveedor
                    'y para el 2017 al 2012 es otro, así que pondré las 2 cadenas.
                    ' Dim scs As String = "probider=Microsoft.jet.OLEDB.4.0; " & "DATA SOURCE = " & cadena & "; Extended Properties = 8.0;"
                    Dim scs2007 As String = "provider=Microsoft.ACE.OLEDB.12.0; " & "data source = " & cadena & "; Excel 12.0; IMEX=1; HDR=YES; "
                    Dim objetoConexion As System.Data.OleDb.OleDbConnection
                    objetoConexion = New System.Data.OleDb.OleDbConnection(scs2007)
                    'Dim consulta As String = "Select * from " & "[" & area & "$" & rango & "]"
                    'Dim consulta As String = "Select Cód_GC, Ejer_01, Ejer_02, Ejer_03 from " & "[" & area & "$" & rango & "] WHERE Cód_GC = Cód_GC"
                    Dim consulta As String = "UPDATE Balances SET " & Strings.Left(strvalores, Len(strvalores) - 2) & " WHERE IdEmpresa = '001' And PLANCONTA = 'PLAN 2007' AND [Cód_GC] = '" & objHoja1.Rango(lngFila, 1).Value & "'"
    
                    objetoDataadapter = New System.Data.OleDb.OleDbDataAdapter(consulta, objetoConexion)
                    objetoDataset = New System.Data.DataSet
                    objetoDataadapter.Fill(objetoDataset)
                    objetoConexion.Close()
                    With data
                        .DataSource = objetoDataset
                        .DataMember = objetoDataset.Tables(0).TableName
                    End With
                    Return True
                Else
                    MsgBox("No se ha encontrado el archivo" & CadenaUbicacion)
                    Return False
                End If
                Return True
            Catch ex As Exception
                Return False
            End Try
        End 
    Es en el código en negrita: AND [Cód_GC] = '" & objHoja1.Rango(lngFila, 1).Value & "'"

    que no se como lo tengo que poner.

    Muchas gracias. 

    Gemma.



    • Editado gemma_campillo viernes, 22 de mayo de 2015 6:20 error de sintaxis
    viernes, 22 de mayo de 2015 6:18

Respuestas

  • Hola:
    En el ejemplo que te expongo a continuacion, la mecanica es la siguiente:
    1.- Se lee el fichero excel y sus datos se cargan en un datatable.
    2.- Se recorre ese datatable y segun algun criterio (lo desconozco) se modifica 1 registro de la tabla de produccion con los datos de la linea del datatable leida

    Te muestro una imagen del Form que he usado como ejemplo


    dlgAbrirArchivo es un OpenFileDialog
    ErrorProviderAccess es un ErrorProvider
    cmenu es un ContextMenuStrip cuyo contenido es la siguiente imagen

    El codigo del Form, es el siguiente

    Option Explicit On
    Option Strict On
    Imports System.Data.OleDb

    Public Class FrmXXXXX
        Private msCadenaAccess As String

        Private Sub FrmXXXXX_Load(sender As Object, e As EventArgs) Handles Me.Load
            Me.ContextMenuStrip = cmenu
        End Sub

        Private Sub AceptarToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AceptarToolStripMenuItem.Click
            If Me.ValidateChildren = False Then
                MessageBox.Show("Datos erroneos", "AceptarToolStripMenuItem_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return
            End If
            Dim lsCadConexionExcel As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & Me.txtFicheroExcel.Text & ";Extended Properties = 'Excel 12.0;Xml'"
            'Iniciar una transaccion para que se modifiquen todos los registro o ninguno si algo falla
            Dim loTransaccion As OleDbTransaction = Nothing
            Try
                Dim loDataTable As New DataTable(Me.cboTablaAccess.Text)
                Using loConexionExcel As New OleDbConnection(lsCadConexionExcel)
                    Dim lsQuery As String = "Select * From [" & Me.txtLibroExcel.Text & "$]"
                    Dim loDataAdapter As New OleDbDataAdapter(lsQuery, loConexionExcel)
                    'para que se vea el tamaño de las columnas
                    loDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    'Cargar el Datatable con el fichero XLSX
                    loDataAdapter.Fill(loDataTable)
                End Using
                '
                Using loConexionAccess As New OleDbConnection(msCadenaAccess)
                    loConexionAccess.Open()
                    'Recorrer el DataTable para modificar los datos de la tabla principal
                    Dim lsQuery As String
                    For Each ldrFila As DataRow In loDataTable.Rows
                        'NO SE QUE CRITERIO SE SIGUE PARA MODIFICAR EL REGISTRO EN LA TABLA PRICIPAL, ASUMO QUE SON TODOS LOS REGISTROS QUE ESTAN EN EL DATATABLE
                        lsQuery = "Update TU_TABLA_PRINCIPAL Set Ejer_01=@Ejer01, ...... , Ejer_12=@Ejer12 Where Cod_CG=@CodCG"
                        Using loComando As New OleDbCommand(lsQuery, loConexionAccess)
                            loComando.Parameters.Add(New OleDbParameter("@Ejer01", ldrFila.Item("Ejer_01")))
                            '...............
                            '...............
                            loComando.Parameters.Add(New OleDbParameter("@Ejer12", ldrFila.Item("Ejer_12")))
                            loComando.Parameters.Add(New OleDbParameter("@CodCG", ldrFila.Item("Cod_CG")))
                            loComando.Transaction = loTransaccion
                            loComando.ExecuteNonQuery()
                        End Using
                    Next
                    'finalizamos la transaccion
                    loTransaccion.Commit()
                End Using
                MessageBox.Show("Modificacion CORRECTA", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                'Deshacemos la transaccion
                loTransaccion.Rollback()
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub SalirToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SalirToolStripMenuItem.Click
            Me.Close()
        End Sub

        Private Sub btnPathAccess_Click(sender As System.Object, e As System.EventArgs) Handles btnPathAccess.Click
            Try
                With Me.dlgAbrirArchivo
                    ' Comprobar que el archivo seleccionado existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckFileExists = True
                    ' Comprobar que la ruta de acceso seleccionada existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckPathExists = True
                    ' Obtener o establecer una extensión predeterminada. No incluye el "." inicial.
                    .DefaultExt = "accdb"
                    .Filter = "Ficheros ACCDB (*.accdb)|*.accdb"
                    .Multiselect = False
                    ' ¿Restaurar el directorio original después de seleccionar un archivo? Si False, el directorio actual cambia al directorio en el que seleccionó el archivo.
                    ' Establézcalo como True para poner la carpeta actual de nuevo donde estaba cuando comenzó.
                    .RestoreDirectory = True
                    .Title = "Seleccione el Fichero"
                    ' ¿Aceptar sólo nombres de archivo Win32 válidos?
                    .ValidateNames = True
                    If .ShowDialog = Windows.Forms.DialogResult.OK Then
                        Me.txtPathDB.Text = .FileName
                        msCadenaAccess = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Me.txtPathDB.Text
                        lP_CargaTablas()
                        If Me.txtFicheroExcel.Text.Length > 0 Then
                            Me.AceptarToolStripMenuItem.Visible = True
                        End If
                    End If
                End With
            Catch ex As Exception
                MessageBox.Show(ex.Message, "btnPathAccess_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub btnPathExcel_Click(sender As Object, e As EventArgs) Handles btnPathExcel.Click
            Try
                With dlgAbrirArchivo
                    ' Comprobar que el archivo seleccionado existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckFileExists = True
                    ' Comprobar que la ruta de acceso seleccionada existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckPathExists = True
                    ' Obtener o establecer una extensión predeterminada. No incluye el "." inicial.
                    .DefaultExt = "xlsx"
                    .Filter = "Ficheros Excell (*.xlsx)|*.xlsx| Todos|*.*"
                    .Multiselect = False
                    ' ¿Restaurar el directorio original después de seleccionar un archivo? Si False, el directorio actual cambia al directorio en el que seleccionó el archivo.
                    ' Establézcalo como True para poner la carpeta actual de nuevo donde estaba cuando comenzó.
                    .RestoreDirectory = True
                    .Title = "Seleccione el Fichero"
                    ' ¿Aceptar sólo nombres de archivo Win32 válidos?
                    .ValidateNames = True
                    If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                        Me.txtFicheroExcel.Text = .FileName
                        If Me.txtPathDB.Text.Length > 0 Then
                            Me.AceptarToolStripMenuItem.Visible = True
                        End If
                    End If
                End With
            Catch ex As Exception
                MessageBox.Show(ex.Message, "btnPathExcel_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub lP_CargaTablas()
            'Cargar las tablas Access en el Combo
            Dim ltDataTable As DataTable
            Dim laRestricciones() As Object = {Nothing, Nothing, Nothing, "TABLE"}
            Try
                Using loConexionAccess As New OleDbConnection(msCadenaAccess)
                    loConexionAccess.Open()
                    ltDataTable = loConexionAccess.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, laRestricciones)
                    For liFilas As Integer = 0 To ltDataTable.Rows.Count - 1
                        Me.cboTablaAccess.Items.Add(ltDataTable.Rows(liFilas).Item("TABLE_NAME").ToString())
                    Next
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "lP_CargaTablas", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub txtLibroExcel_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles txtLibroExcel.Validating
            If Me.txtLibroExcel.Text.Length = 0 Then
                e.Cancel = True
                Me.ErrorProviderAccess.SetError(Me.txtLibroExcel, "Teclee el Nombre del Libro Excel")
            Else
                Me.ErrorProviderAccess.SetError(Me.txtLibroExcel, Nothing)
            End If
        End Sub
    End Class

    P.D.
    Espero que te sirva

    Un saludo desde Bilbo
    Carlos

    • Marcado como respuesta gemma_campillo viernes, 22 de mayo de 2015 10:59
    viernes, 22 de mayo de 2015 10:44

Todas las respuestas

  • Hola:
    Porque no pasas los datos de la hoja de excel a una tabla temporal de acces, modificas los valores de la tabla temporal y desde esta tabla temporal los pasas a la definitiva.

    P.D.
    Si quieres mas ayuda expon las estructuras de la hoja de excel y de la tabla definitiva

    Un saludo desde Bilbo
    Carlos

    viernes, 22 de mayo de 2015 7:22
  • Hola J.Carlos:

    No he tocado la historia del Excel con VB,net nunca y no me acabo de aclarar. La estructura de los campos de la tabla que necesitan importar los datos es esta: Cód_GC campo de texto, y luego Ejer_01 a Ejer_12 campos numéricos, pero el usuario a lo mejor solamente rellena en el Excel El Cód_GC y Ejer_01 y Ejer_02, los otros campos están a 0 u por lo tanto no necesita importarlos, por lo que su hoja de Excel quedaría de la forma anterior. Esos campos son los mismos que en Access, lo que pasa es que en Access o en la tabla "Balances" hay más campos como "Descripcion", "TipoBalance", etc., que esos ni se importan ni se exportan. Solamente los que tienen un grupo contable y los dalos o valores de los ejercicios: Cóg_GC y los Ejer_01, Ejer_02, ... pudiendo llegar hasta el Ejer_12.

    No se si esto lo que me preguntas si no lo es te ruego me lo indiques. Estoy acabando el maldito programa y me salen estos gazapos finales.

    Bueno Carlos, un abrazo como siempre.

    Gemma

    viernes, 22 de mayo de 2015 8:17
  • Hola:
    En el ejemplo que te expongo a continuacion, la mecanica es la siguiente:
    1.- Se lee el fichero excel y sus datos se cargan en un datatable.
    2.- Se recorre ese datatable y segun algun criterio (lo desconozco) se modifica 1 registro de la tabla de produccion con los datos de la linea del datatable leida

    Te muestro una imagen del Form que he usado como ejemplo


    dlgAbrirArchivo es un OpenFileDialog
    ErrorProviderAccess es un ErrorProvider
    cmenu es un ContextMenuStrip cuyo contenido es la siguiente imagen

    El codigo del Form, es el siguiente

    Option Explicit On
    Option Strict On
    Imports System.Data.OleDb

    Public Class FrmXXXXX
        Private msCadenaAccess As String

        Private Sub FrmXXXXX_Load(sender As Object, e As EventArgs) Handles Me.Load
            Me.ContextMenuStrip = cmenu
        End Sub

        Private Sub AceptarToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AceptarToolStripMenuItem.Click
            If Me.ValidateChildren = False Then
                MessageBox.Show("Datos erroneos", "AceptarToolStripMenuItem_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return
            End If
            Dim lsCadConexionExcel As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & Me.txtFicheroExcel.Text & ";Extended Properties = 'Excel 12.0;Xml'"
            'Iniciar una transaccion para que se modifiquen todos los registro o ninguno si algo falla
            Dim loTransaccion As OleDbTransaction = Nothing
            Try
                Dim loDataTable As New DataTable(Me.cboTablaAccess.Text)
                Using loConexionExcel As New OleDbConnection(lsCadConexionExcel)
                    Dim lsQuery As String = "Select * From [" & Me.txtLibroExcel.Text & "$]"
                    Dim loDataAdapter As New OleDbDataAdapter(lsQuery, loConexionExcel)
                    'para que se vea el tamaño de las columnas
                    loDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    'Cargar el Datatable con el fichero XLSX
                    loDataAdapter.Fill(loDataTable)
                End Using
                '
                Using loConexionAccess As New OleDbConnection(msCadenaAccess)
                    loConexionAccess.Open()
                    'Recorrer el DataTable para modificar los datos de la tabla principal
                    Dim lsQuery As String
                    For Each ldrFila As DataRow In loDataTable.Rows
                        'NO SE QUE CRITERIO SE SIGUE PARA MODIFICAR EL REGISTRO EN LA TABLA PRICIPAL, ASUMO QUE SON TODOS LOS REGISTROS QUE ESTAN EN EL DATATABLE
                        lsQuery = "Update TU_TABLA_PRINCIPAL Set Ejer_01=@Ejer01, ...... , Ejer_12=@Ejer12 Where Cod_CG=@CodCG"
                        Using loComando As New OleDbCommand(lsQuery, loConexionAccess)
                            loComando.Parameters.Add(New OleDbParameter("@Ejer01", ldrFila.Item("Ejer_01")))
                            '...............
                            '...............
                            loComando.Parameters.Add(New OleDbParameter("@Ejer12", ldrFila.Item("Ejer_12")))
                            loComando.Parameters.Add(New OleDbParameter("@CodCG", ldrFila.Item("Cod_CG")))
                            loComando.Transaction = loTransaccion
                            loComando.ExecuteNonQuery()
                        End Using
                    Next
                    'finalizamos la transaccion
                    loTransaccion.Commit()
                End Using
                MessageBox.Show("Modificacion CORRECTA", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                'Deshacemos la transaccion
                loTransaccion.Rollback()
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub SalirToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SalirToolStripMenuItem.Click
            Me.Close()
        End Sub

        Private Sub btnPathAccess_Click(sender As System.Object, e As System.EventArgs) Handles btnPathAccess.Click
            Try
                With Me.dlgAbrirArchivo
                    ' Comprobar que el archivo seleccionado existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckFileExists = True
                    ' Comprobar que la ruta de acceso seleccionada existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckPathExists = True
                    ' Obtener o establecer una extensión predeterminada. No incluye el "." inicial.
                    .DefaultExt = "accdb"
                    .Filter = "Ficheros ACCDB (*.accdb)|*.accdb"
                    .Multiselect = False
                    ' ¿Restaurar el directorio original después de seleccionar un archivo? Si False, el directorio actual cambia al directorio en el que seleccionó el archivo.
                    ' Establézcalo como True para poner la carpeta actual de nuevo donde estaba cuando comenzó.
                    .RestoreDirectory = True
                    .Title = "Seleccione el Fichero"
                    ' ¿Aceptar sólo nombres de archivo Win32 válidos?
                    .ValidateNames = True
                    If .ShowDialog = Windows.Forms.DialogResult.OK Then
                        Me.txtPathDB.Text = .FileName
                        msCadenaAccess = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Me.txtPathDB.Text
                        lP_CargaTablas()
                        If Me.txtFicheroExcel.Text.Length > 0 Then
                            Me.AceptarToolStripMenuItem.Visible = True
                        End If
                    End If
                End With
            Catch ex As Exception
                MessageBox.Show(ex.Message, "btnPathAccess_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub btnPathExcel_Click(sender As Object, e As EventArgs) Handles btnPathExcel.Click
            Try
                With dlgAbrirArchivo
                    ' Comprobar que el archivo seleccionado existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckFileExists = True
                    ' Comprobar que la ruta de acceso seleccionada existe. El cuadro de diálogo muestra una advertencia en caso contrario.
                    .CheckPathExists = True
                    ' Obtener o establecer una extensión predeterminada. No incluye el "." inicial.
                    .DefaultExt = "xlsx"
                    .Filter = "Ficheros Excell (*.xlsx)|*.xlsx| Todos|*.*"
                    .Multiselect = False
                    ' ¿Restaurar el directorio original después de seleccionar un archivo? Si False, el directorio actual cambia al directorio en el que seleccionó el archivo.
                    ' Establézcalo como True para poner la carpeta actual de nuevo donde estaba cuando comenzó.
                    .RestoreDirectory = True
                    .Title = "Seleccione el Fichero"
                    ' ¿Aceptar sólo nombres de archivo Win32 válidos?
                    .ValidateNames = True
                    If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                        Me.txtFicheroExcel.Text = .FileName
                        If Me.txtPathDB.Text.Length > 0 Then
                            Me.AceptarToolStripMenuItem.Visible = True
                        End If
                    End If
                End With
            Catch ex As Exception
                MessageBox.Show(ex.Message, "btnPathExcel_Click", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub lP_CargaTablas()
            'Cargar las tablas Access en el Combo
            Dim ltDataTable As DataTable
            Dim laRestricciones() As Object = {Nothing, Nothing, Nothing, "TABLE"}
            Try
                Using loConexionAccess As New OleDbConnection(msCadenaAccess)
                    loConexionAccess.Open()
                    ltDataTable = loConexionAccess.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, laRestricciones)
                    For liFilas As Integer = 0 To ltDataTable.Rows.Count - 1
                        Me.cboTablaAccess.Items.Add(ltDataTable.Rows(liFilas).Item("TABLE_NAME").ToString())
                    Next
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "lP_CargaTablas", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub txtLibroExcel_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles txtLibroExcel.Validating
            If Me.txtLibroExcel.Text.Length = 0 Then
                e.Cancel = True
                Me.ErrorProviderAccess.SetError(Me.txtLibroExcel, "Teclee el Nombre del Libro Excel")
            Else
                Me.ErrorProviderAccess.SetError(Me.txtLibroExcel, Nothing)
            End If
        End Sub
    End Class

    P.D.
    Espero que te sirva

    Un saludo desde Bilbo
    Carlos

    • Marcado como respuesta gemma_campillo viernes, 22 de mayo de 2015 10:59
    viernes, 22 de mayo de 2015 10:44
  • Hola J.Carlos:

    Te estoy enormemente agradecida.

    Gracias por tu tiempo que me has regalado.

    Voy a ponerme esta tarde con ello y a ver si ya lo dejo listo. La exportación me sale perfecta, abre el filedialog, etc. y lo hace en 1/2 segundo, contando que es una tabla de 800 registros y 6 columnas.

    Ahora solo me falta lo que tu me has hecho, aunque sea en este mismo correo te comentaré como ha ido, que seguro que será bien.

    Te reitero mi agradecimiento a tu tiempo y ayuda.

    Un fuerte abrazo querido Carlos.

    Gemma.

    viernes, 22 de mayo de 2015 11:03