help please - exporting data from Access to Excel

  • Thread starter Thread starter Gillian
  • Start date Start date
G

Gillian

I have used the copyFromRecordset method to transfer data
from Access 2000 to an Excel 2000 spreadsheet. Data from
memo fields in Access is truncated to 1823 characters once
in Excel. Can anyone tell me how to ensure that all text
is transfered. Many thanks in advance.
 
Hi Gillian,

I don't know why this is happening (it's the same on my installation of
Office 2002, BTW) and I can't find a reference to it in the Microsoft
Knowledgebase. So I'll pass it on as an apparent bug.

As a workround, use Automation to populate the Excel cells individually:
all the data seems to come through at least up to the few kB I've tried
it with. So rather than using CopyFromRecordset you'll need to do
something along these lines:

Dim i as Long, j as Long
Dim rsR As Recordset
Dim xlR As Excel.Range

'set up rsR and xlR
...


rsR.MoveFirst
i = 1
Do Until rsR.EOF
For j = 1 To rsR.Fields.Count
xlR.Cells(i, j).Formula = rsR.Fields(j - 1).Value
Next j
rsR.MoveNext
i = i + 1
Loop

'tidy up

I have used the copyFromRecordset method to transfer data
from Access 2000 to an Excel 2000 spreadsheet. Data from
memo fields in Access is truncated to 1823 characters once
in Excel. Can anyone tell me how to ensure that all text
is transfered. Many thanks in advance.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
John
Many thanks for that, hadn't thought of doing it that
way. After posting the message I tried the
transferSpreadsheet method and that did transfer all the
data in the memo fields. So two solutions to the
problem. Thanks.

Gillian
-----Original Message-----
Hi Gillian,

I don't know why this is happening (it's the same on my installation of
Office 2002, BTW) and I can't find a reference to it in the Microsoft
Knowledgebase. So I'll pass it on as an apparent bug.

As a workround, use Automation to populate the Excel cells individually:
all the data seems to come through at least up to the few kB I've tried
it with. So rather than using CopyFromRecordset you'll need to do
something along these lines:

Dim i as Long, j as Long
Dim rsR As Recordset
Dim xlR As Excel.Range

'set up rsR and xlR
...


rsR.MoveFirst
i = 1
Do Until rsR.EOF
For j = 1 To rsR.Fields.Count
xlR.Cells(i, j).Formula = rsR.Fields(j - 1).Value
Next j
rsR.MoveNext
i = i + 1
Loop

'tidy up

I have used the copyFromRecordset method to transfer data
from Access 2000 to an Excel 2000 spreadsheet. Data from
memo fields in Access is truncated to 1823 characters once
in Excel. Can anyone tell me how to ensure that all text
is transfered. Many thanks in advance.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top