gmaxey
03-03-2014, 05:35 PM
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
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