Text in Excel from SQL

[BobBob10]

Hi there I am using Excel 2002 VBA and I am using the function CopyFromRecordset to import data from my SQL database to my Excel spreadsheet. However, when fields are longer than 255 charaters the text gets cut off at 254.

Is there any way to sort this problem out.

Cheers

Bob

 

[CautionMP]

Most automation functions in Excel clip strings at 255. I suspect that this comes from the fact that if you have more than 255 characters you are typing a Word document, not crunching numbers in a spreadsheet.

With that said, you can do it the 'old' way:
1) Create a connection to the SQL database
2) Open a recordset that has the data you want to import.
3) Simultaneously enumerate through the recorset and worksheet to 'copy' the data.

I assume that you have 1 & 2 covered if you are using CopyFromRecordset (since you have to supply a recordset to the function) so you could try something like this:

'Assume rs is you SQL recordset
Dim rowIndex as Long, colIndex as Long
'Set the intial row offset here
'rowIndex = x

'Enumerate through the rs rows
For rowIndex = 1 to rs.RecordCount
'Variation: While Not rs.EOF

  'Enumerate through the rs columns (10 total)
  For colIndex = 0 to 9
    Worksheet.Cells(rowIndex,colIndex) = rsFields(colIndex)
  Next colIndex

  rs.MoveNext

Next rowIndex
'Variation: Wend

This should start dumping data in Cell A1, you can adjust this by changing the starting number for rowIndex.
Note: colIndex is zero based, since the first field in a recordset is usually 0.

Hope this helps,
CMP

 

[VICKEL1]

This doesn't work due to

For rowIndex = 1 To rs.RecordCount, is equal to 1 to -1.

Any ideas?

 

[fredericofonseca]

As per the example supplied by CautionMP

'For rowIndex = 1 to rs.RecordCount
Variation: While Not rs.EOF

'Enumerate through the rs columns (10 total)
For colIndex = 0 to 9
Worksheet.Cells(rowIndex,colIndex) = rsFields(colIndex)
Next colIndex

rs.MoveNext

rowindex = rowindex + 1

'Next rowIndex
Variation: Wend

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Sorry it should have been
As per the example supplied by CautionMP

'For rowIndex = 1 to rs.RecordCount
While Not rs.EOF

'Enumerate through the rs columns (10 total)
For colIndex = 0 to 9
Worksheet.Cells(rowIndex,colIndex) = rsFields(colIndex)
Next colIndex

rs.MoveNext

rowindex = rowindex + 1

'Next rowIndex
Wend

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com