A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Importing data into a MEMO field



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2004, 09:45 AM
RoMi
external usenet poster
 
Posts: n/a
Default Importing data into a MEMO field



How do you import an arranged text into a memo/field where `arranged text`
means the text with the several paragraphs, i.e. the text split into several
rows by the ENTER command.



Assume that we have such arranged text in 2-3 columns and 100+ row table
made by MS Word (2002). Why Word? Word allows simple text editing in one
single cell (field) so one can write down a text like poems.

Unfortunately, Access imports the data from Excel but not from the Word
tables. On the other side, when one copy/paste the table from Word to Excel,
Excel splits the paragraphs into separated cells which means more records
than in an original table.

Furthermore, if one save Word or Excel table into a web format (*.htm),
importing these data into Access goes smoothly although with the different
results. Access makes more records like Excel does or concatenates the
paragraphs into one long sentence. Editing of these data into paragraphs is
impossible.



Finally, if one copy/paste a cell by a cell directly from a Word to Access
it works fine and just as I need it.



Is it possible to automate such data transfer? How to hide the ENTER
command?



Thank you all.


  #2  
Old April 28th, 2004, 05:07 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default Importing data into a MEMO field

"RoMi" wrote in
:

How do you import an arranged text into a memo/field where `arranged
text` means the text with the several paragraphs, i.e. the text split
into several rows by the ENTER command.



Assume that we have such arranged text in 2-3 columns and 100+ row
table made by MS Word (2002).


I would probably do all this in Word VBA rather than Access, because the
database stuff is the easy part and getting the text out of the table cells
will be harder. I suggest you check the m.p.word.vba.general for good Word
vba web sites and help.

In short it would be something like this: I haven't really explored
programming with Tables in Word, so make sure you check out the object
model properly!


' database access: this is all that Access
' does for you
Set dbEngine = CreateObject("DAO.DBEngine")
Set db = dbEngine.OpenDatabase("d:\MyDatabase.mdb")

' now go through the table
For each cel in ThisDocument.Tables(1).Cells

' make a command. You will have to be responsible
' for handling any problematic characters in the text: -
' the most likely one will be a double-quote
strSQL = "INSERT INTO MyTable (MemoField) " & _
"VALUES (""" & cel.Range.Text & """)"

' carry out the command. You should really catch any
' errors with an On Error .... line
db.Execute strSQL, dbFailOnError

' if all was okay, get the next table cell
Next cel

' tidy up afterwards
db.Close


Hope that helps


Tim F


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.