FIX: ADO Inserts Data into Wrong Columns in Excel (314763)

MORE INFORMATION

Steps to Reproduce Behavior

1. Open Microsoft Excel, and then create a new workbook.
2. In Sheet1, type the following sample data, beginning with cell A1 in the upper left corner:

   ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF
   1	1	testing	1	1	testing
   2	2	testing	2	2	testing

   
   
3. Save the workbook as Test.xls. You can leave the Excel application open, but you must close the new workbook.
4. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.
5. On the Project menu, click References. From the list of available references, select Microsoft ActiveX Data Objects 2.x Library.
6. Place a CommandButton control on Form1, and then paste the following code in the Click event procedure for the button. Note that this code inserts an empty string into the middle column, Column 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. Save your Visual Basic test project in the same folder as your workbook, Test.xls.
8. Run the project, and then click the button. The first time you run the project in the Visual Basic Integrated Development Environment (IDE), you may receive the following error message:
   Runtime error '-2147467259 (80004005)': Selected collating sequence not supported by the operating system
   This is a known issue.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

   246167 Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS

9. In the error message dialog box, click Debug, and then press the F5 key to continue to run the project. Note that this inserts two new rows of data instead of one because Update is run twice.
10. Close the form to end the project. Reopen Test.xls in Excel, and then examine the data in Sheet1. You expect the following results:
    
    

    ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF
    1	1	testing	1	1	testing
    2	2	testing	2	2	testing
    3	3		3	3	testing

    
    However, you see the following data:
    
    

    ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF
    1	1	testing	1	1	testing
    2	2	testing	2	2	testing
    3	3	3	3		testing

    
    It appears as if the empty string that is inserted into ColumnC has been ignored and has disappeared. Therefore, any subsequent numeric values are inserted one column to the left of their intended destinations. The subsequent string column is not affected.

Variation 1

   '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
				

Variation 2

   '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