Copying Access fields into Excel

Jay Say · May 21st, 2002, 04:45 PM

I'm running a report in Access and then transfering the results to Excel via an Access macro. Problem: fields with large amounts of data in Access are cut off after about 120 characters in Excel. When I copy each Access field individually into Excel, however, all characters appear. Any ideas?

WayneTN · May 21st, 2002, 05:38 PM

Can't you just save the Access file as an Excell file instead of just transferring the data? Or am I missing something?

Yogi Anand · May 21st, 2002, 06:05 PM

Quote:

On 2002-05-21 16:38, WayneTN wrote:
Can't you just save the Access file as an Excell file instead of just transferring the data? Or am I missing something?

Hi WayneTN:
You mean in specific reference to an ACCESS Table, SaveAs/Export as an Excel file -- don't you!

Regards!

[ This Message was edited by: Yogi Anand on 2002-05-21 17:06 ]

WayneTN · May 22nd, 2002, 11:10 AM

Yogi,

Exactly! (I wasn't very clear was I?)

For a detailed analysis of Access table records, records that are typically filtered for special situations and shown in the Datasheet View mode, I will Export the data to Excel by selecting File - Export, give the new file an appropriate filename, and then save it as an "Excel 97-2000 (*.xls)" file. That way I can do whatever I want with the data in Excel.

Thanks,
Wayne

[ This Message was edited by: WayneTN on 2002-05-22 10:11 ]

Jay Say · May 22nd, 2002, 12:50 PM

Wayne, Thanks for your interest and recommendations. Unfortunately I exported my results following your instructions and am encountering the same problem. Interestingly this problem doesn't occur when I transfer into Word---but that's another hornet's nest. Is there anything in Excel that can be modified to prevent a long amount of text from being truncated when imported into a cell?

Tommy Bak · May 22nd, 2002, 01:37 PM

Jay Say
I cannot repeat what you are doing.
I think your problem is purely visual

I can get 254 caracters transfered, with no problem, but it seems like i'm transfering only 120.
I Format > cells choose the page where you can adjust the textdirection and Under vertical alingnment choose TOP instead of BOTTOM.
Now you should be able to see all caracters.
regards Tommy

Jay Say · May 22nd, 2002, 03:50 PM

Tommy,

Thank you for your helpful suggestions. Unfortunately I have been doing what you suggested vis-a vis formatting the cell. Just to check I often put the cursor into the cell and hit the F2 key to see where the line of text ends.

What I found really interesting in your remarks was that you can bring into Excel twice the amount of text that I can. This is making me wonder if there is not a setting somewhere in Excel that I can change that would allow me to bring more text within a cell. Anyone have any ideas?

[ This Message was edited by: Jay Say on 2002-05-22 14:59 ]

Jay Say · May 22nd, 2002, 03:55 PM

Second reply to Wayne.

Wayne I can't export an entire table of thousands of records into Excel. Besides I am running reports off detailed queries not just applying a filter.

Tommy Bak · May 23rd, 2002, 01:46 AM

Jay Say
What type of field are you triing to import?
(Memo, Text, or ?)
From a memofield I can get 248 caracters, from a textfield 255

When you paste to excel, have you then tried to paste Special (as text) ?

regards Tommy

Jay Say · May 24th, 2002, 10:21 AM

Tommy,

I'm copying from a memo field. When I copy an indivual cell from Access into Excel I am able to bring in all the text. My problem occurs when I run a report in Access and then try to transfer the report results into Excel.
Somehow in this transfer my text gets cut. In fact what I usually have to end up doing is copying each cell individually from Access into my Excel Report. This is very time consuming with a large report. Thanks for your interest.

[ This Message was edited by: Jay Say on 2002-05-24 09:22 ]

[ This Message was edited by: Jay Say on 2002-05-24 09:23 ]

[ This Message was edited by: Jay Say on 2002-05-24 09:24 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 04:45 PM	#1
Jay Say New Member Join Date: May 2002 Location: AIS Posts: 5	I'm running a report in Access and then transfering the results to Excel via an Access macro. Problem: fields with large amounts of data in Access are cut off after about 120 characters in Excel. When I copy each Access field individually into Excel, however, all characters appear. Any ideas?

May 21st, 2002, 05:38 PM	#2
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Can't you just save the Access file as an Excell file instead of just transferring the data? Or am I missing something?

May 22nd, 2002, 11:10 AM	#4
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Yogi, Exactly! (I wasn't very clear was I?) For a detailed analysis of Access table records, records that are typically filtered for special situations and shown in the Datasheet View mode, I will Export the data to Excel by selecting File - Export, give the new file an appropriate filename, and then save it as an "Excel 97-2000 (*.xls)" file. That way I can do whatever I want with the data in Excel. Thanks, Wayne [ This Message was edited by: WayneTN on 2002-05-22 10:11 ]

May 22nd, 2002, 12:50 PM	#5
Jay Say New Member Join Date: May 2002 Location: AIS Posts: 5	Wayne, Thanks for your interest and recommendations. Unfortunately I exported my results following your instructions and am encountering the same problem. Interestingly this problem doesn't occur when I transfer into Word---but that's another hornet's nest. Is there anything in Excel that can be modified to prevent a long amount of text from being truncated when imported into a cell?

May 22nd, 2002, 01:37 PM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Jay Say I cannot repeat what you are doing. I think your problem is purely visual I can get 254 caracters transfered, with no problem, but it seems like i'm transfering only 120. I Format > cells choose the page where you can adjust the textdirection and Under vertical alingnment choose TOP instead of BOTTOM. Now you should be able to see all caracters. regards Tommy

May 22nd, 2002, 03:50 PM	#7
Jay Say New Member Join Date: May 2002 Location: AIS Posts: 5	Tommy, Thank you for your helpful suggestions. Unfortunately I have been doing what you suggested vis-a vis formatting the cell. Just to check I often put the cursor into the cell and hit the F2 key to see where the line of text ends. What I found really interesting in your remarks was that you can bring into Excel twice the amount of text that I can. This is making me wonder if there is not a setting somewhere in Excel that I can change that would allow me to bring more text within a cell. Anyone have any ideas? [ This Message was edited by: Jay Say on 2002-05-22 14:59 ]

May 22nd, 2002, 03:55 PM	#8
Jay Say New Member Join Date: May 2002 Location: AIS Posts: 5	Second reply to Wayne. Wayne I can't export an entire table of thousands of records into Excel. Besides I am running reports off detailed queries not just applying a filter.

May 23rd, 2002, 01:46 AM	#9
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Jay Say What type of field are you triing to import? (Memo, Text, or ?) From a memofield I can get 248 caracters, from a textfield 255 When you paste to excel, have you then tried to paste Special (as text) ? regards Tommy

May 24th, 2002, 10:21 AM	#10
Jay Say New Member Join Date: May 2002 Location: AIS Posts: 5	Tommy, I'm copying from a memo field. When I copy an indivual cell from Access into Excel I am able to bring in all the text. My problem occurs when I run a report in Access and then try to transfer the report results into Excel. Somehow in this transfer my text gets cut. In fact what I usually have to end up doing is copying each cell individually from Access into my Excel Report. This is very time consuming with a large report. Thanks for your interest. [ This Message was edited by: Jay Say on 2002-05-24 09:22 ] [ This Message was edited by: Jay Say on 2002-05-24 09:23 ] [ This Message was edited by: Jay Say on 2002-05-24 09:24 ]