RTE Error

[gmaxey]

I have a spreadsheet created in Excel 2010 (.xlxs) and a Word template created in Word 2010 (.dotm). I am using ADODB to extract values from the spreadsheet and populate a userform in the Word template.

Here is the code I'm using and on my PC when a document is created using the template in Word versions 2013, 2010 or 2007 all works with NO errors and with only ONE minor issue just detected.

Sub LoadFromExcel_ADODB(ByRef oListPassed As Object, ByRef strSource As String, strSQL As String)
'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library."
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim strConnection As String
Dim lngCount As Long
  Set oConn = CreateObject("ADODB.Connection")
  'Suppress first row.
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strSource & ";" & _
                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  oConn.Open ConnectionString:=strConnection
  Set oRS = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet.
  oRS.Open strSQL, oConn, 3, 1
    With oRS
    .MoveLast
    'Get count.
    lngCount = .RecordCount
    .MoveFirst
  End With
Err_ReEntry:
  On Error GoTo 0
  With oListPassed
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRS.Fields.Count
    .Column = oRS.GetRows(lngCount)
  End With
  'Cleanup
  If oRS.State = 1 Then oRS.Close
  Set oRS = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Sub
End Sub

The one minor issue is that one of the cells in the spreadsheet has 256 characters and the data available in the corresponding userform column is only 255.

Now on a friends remote PC, when he attempts to create a document using the template, it works equally as well in Word versions 2013 and 2010. However, when he tried with version 2007, he got a run time error '-2147467259(80004005)' The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

The error occurs on this line in the code above:
.MoveLast

When my friend reduced the number of characters in all cells below 256, the code will run without error.

Questions:
1. What do you suppose lets the code run without error on my PC, but errors on his? I do have Excel 2013, 2010 and 2007 installed on my PC and he only has Excel 2007.
2. Why would the error, since it errors, occur on that line? .MoveLast appears to be referencing the last record in the sheet and not have anything to do with the number of characters in a field.

Thanks

[westconn1]

Query Field Information

Every field (column) in an Excel data source is one of the following datatypes:

• numeric (ADO datatype 5, adDouble)
• currency (ADO datatype 6, adCurrency)
• logical or boolean (ADO datatype 11, adBoolean)
• date (ADO datatype 7, adDate, using Jet; 135, adDBTimestamp, using ODBC)
• text (an ADO ad...Char type, such as 202, adVarChar, 200, adVarWChar or similar)

The numeric_precision for a numeric column is always returned as 15 (which is the maximum precision in Excel); the character_maximum_length of a text column is always returned as 255 (which is the maximum display width, but not the maximum length, of text in an Excel column). There is not much useful field information that you can obtain beyond the data_type property. You request a list of the available fields in a table with the following code:

from
http://support.microsoft.com/kb/257819

[gmaxey]

westconn1,

Thanks for your reply. Yes, I've come to understand the 255 character limit. Actually it appears that the longest note in the data is 256 characters and I didn't even notice the "clipped" final period in the display on my system. But then I wasn't getting and error causing me to look for a reason.


What I'm really curious about is why on my system with Word & Excel 2013, 2010 and 2007 installed the code runs without error but on my friends system with just Word 2007 and Excel 2007 the RTE occurs.

[snb]

I don't know why you opt for this method.

The same result will be attained using:

Private Sub UserForm_Initialize()
    With GetObject("G:\OF\adressen.xls")
        ComboBox1.List = .sheets(1).Cells(1).currentregion.Value
        .Close False
    End With
    
    ComboBox1.ColumnCount = UBound(ComboBox1.List, 2) + 1
End Sub

Fields containing >255 characters do not produce any problem.

[Aflatoon]

I suspect your ADO library (and possibly other MDAC parts like the Provider) has been updated by one of those installations. I've had similar experiences where code would work on a PC with 2007 and 2010 installed but fail, with the same file, on a PC with just 2007, even though the version of 2007 (major and minor) was identical.

[gmaxey]

Aflatoon,

Thanks. I expected something like that, but I don't know enough about the process or terms to communicate intelligently about it :-(

[gmaxey]

snb,

Opted for that method as it was one of the few ways that I knew how. Thanks for your code snippet. I'll see what I can do with it.