REVISIÓN: ADO inserta datos en columnas erróneas en Excel

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Síntomas

Cuando se usa ADO para insertar nuevas filas de datos en una hoja de cálculo de Microsoft Excel, si los datos incluyen campos que contienen valores de cadena vacía, ADO puede insertar los valores de datos de los campos numéricos siguientes en columnas inapropiadas de Excel.


Este problema se produce en el proveedor Microsoft OLE DB para Jet versión 4.0 y el controlador ODBC de Microsoft para Excel. Este problema se produce si utiliza una instrucción INSERT de SQL o AddNew y los métodos de actualización del objeto Recordset de ADO.


Este problema no se produce si el libro de Excel está abierto en la aplicación Excel cuando ADO inserta los nuevos registros.


Sin embargo, Microsoft recomienda no esta práctica porque se produce una pérdida de memoria si Excel está abierto durante las operaciones de ADO.
Para obtener información adicional, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

Error de 319998 : pérdida de memoria cuando se consulta abrir en Excel la hoja de cálculo con ADO
Consulte la sección "Más información" para obtener más información sobre las circunstancias en que se produce este problema.

Solución

Para resolver este problema, obtenga el service pack más reciente para Jet 4.0 service pack. Para obtener información adicional, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
239114 Cómo: obtener el Service Pack más reciente para el motor de base de datos de Microsoft Jet 4.0

Estado

Microsoft ha confirmado que se trata de un error en los productos de Microsoft que se enumeran al principio de este artículo.

Más información

Pasos para reproducir el comportamiento

  1. Abra Microsoft Excel y, a continuación, cree un nuevo libro.
  2. En la Hoja1, escriba los siguientes datos de ejemplo, comenzando con la celda A1 en la esquina superior izquierda:
    ColumnAColumnBColumnCColumnadColumnaColumnF
    11pruebas11pruebas
    22pruebas22pruebas

  3. Guarde el libro como prueba.xls. Puede dejar abierta la aplicación Excel, pero debe cerrar el libro nuevo.
  4. En Microsoft Visual Basic, cree un nuevo proyecto EXE estándar. Se creará Form1 de forma predeterminada.
  5. En el menú proyecto , haga clic en referencias. En la lista de referencias disponibles, seleccione Microsoft ActiveX Data Objects 2. x biblioteca.
  6. Coloque un control CommandButton en Form1 y, a continuación, pegue el código siguiente en el procedimiento de evento Click para el botón. Tenga en cuenta que este código inserta una cadena vacía en la columna central, columna C.
    Private Sub Command1_Click()   Dim strCn As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim fld As ADODB.Field

    'Open connection
    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & "\Test.xls;" & _
    "Extended Properties=Excel 8.0"
    Set cn = New ADODB.Connection
    cn.Open strCn

    'Add new values.
    Set rs = New ADODB.Recordset
    With rs
    .CursorLocation = adUseClient
    .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
    .AddNew
    .Fields("ColumnA").Value = 3
    .Fields("ColumnB").Value = 3
    .Fields("ColumnC").Value = ""
    .Fields("ColumnD").Value = 3
    .Fields("ColumnE").Value = 3
    .Fields("ColumnF").Value = "testing"
    .Update
    .Close
    End With
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

  7. Guarde su proyecto de prueba de Visual Basic en la misma carpeta que el libro prueba.xls.
  8. Ejecute el proyecto y, a continuación, haga clic en el botón. La primera vez que se ejecute el proyecto en el Visual Basic integrado entorno de desarrollo (IDE), puede recibir el siguiente mensaje de error:
    Error en tiempo de ejecución '-2147467259 (80004005)': seleccionado ecuencia no compatible con el sistema operativo
    Se trata de un problema conocido. Para obtener información adicional, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    246167 de ordenación secuencia Error apertura ADODB Recordset primera vez contra un Excel XLS
  9. En el cuadro de diálogo de mensaje de error, haga clic en Depurary, a continuación, presione la tecla F5 para continuar ejecutar el proyecto. Tenga en cuenta que esto inserta dos nuevas filas de datos en lugar de uno ya que la actualización se ejecuta dos veces.
  10. Cierre el formulario para finalizar el proyecto. Vuelva a abrir prueba.xls en Excel y, a continuación, examine los datos de Sheet1. Esperar los resultados siguientes:

    ColumnAColumnBColumnCColumnadColumnaColumnF
    11pruebas11pruebas
    22pruebas22pruebas
    3333pruebas

    Sin embargo, verá los siguientes datos:

    ColumnAColumnBColumnCColumnadColumnaColumnF
    11pruebas11pruebas
    22pruebas22pruebas
    3333pruebas

    Parece como si la cadena vacía en la que se inserta en ColumnC se ha omitido y ha desaparecido. Por lo tanto, los valores numéricos siguientes son insertado una columna a la izquierda de sus destinatarios. La columna de cadena siguiente no se ve afectada.

Variación 1

Configurar los datos de prueba como sigue:

ColumnAColumnBColumnCColumnadColumna
1pruebas11pruebas
2pruebas22pruebas

En el proyecto de Visual Basic, modifique la sección para agregar nuevos valores como sigue:
   'Add new values.   Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
.AddNew
.Fields("ColumnA").Value = 3
.Fields("ColumnB").Value = ""
.Fields("ColumnC").Value = 3
.Fields("ColumnD").Value = 3
.Fields("ColumnE").Value = "testing"
.Update
.Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing

Cuando se abren prueba.xls en Excel, Sheet1 muestra los siguientes datos:

ColumnAColumnBColumnCColumnadColumna
1pruebas11pruebas
2pruebas22pruebas
333pruebas

Observe que este problema no se produce cuando el valor de cadena vacía precede a una sola columna numérica.

Variación 2

Configurar los datos de prueba como sigue:

ColumnAColumnBColumnCColumnadColumnaColumnFColumnG
11pruebaspruebas11pruebas
22pruebaspruebas22pruebas

En el proyecto de Visual Basic, modifique la sección para agregar nuevos valores como sigue:
   'Add new values.   Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
.AddNew
.Fields("ColumnA").Value = 3
.Fields("ColumnB").Value = 3
.Fields("ColumnC").Value = ""
.Fields("ColumnD").Value = ""
.Fields("ColumnE").Value = 3
.Fields("ColumnF").Value = 3
.Fields("ColumnG").Value = "testing"
.Update
.Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing

Cuando se abren prueba.xls en Excel, Sheet1 muestra los siguientes datos:

ColumnAColumnBColumnCColumnadColumnaColumnFColumnG
11pruebaspruebas11pruebas
22pruebaspruebas22pruebas
3333pruebas

Si ADO inserta dos valores de cadena vacía, parece como si las cadenas vacías que se insertan en ColumnC y Columnad se pasan por alto y han desaparecido. Por lo tanto, los valores numéricos siguientes son insertadas dos columnas a la izquierda de sus destinatarios. La columna de cadena siguiente no se ve afectada.

Referencias

Para obtener información adicional, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

294410 ACC2002: nulos son sustituidos por los datos del campo siguiente al exportar a Excel
257819 HOWTO: usar ADO con datos de Excel desde Visual Basic o VBA
Propiedades

Id. de artículo: 314763 - Última revisión: 22 ene. 2017 - Revisión: 1

Comentarios