Results 1 to 10 of 10
  1. #1
    Runlynch is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    7

    Importing Information From Microsoft Word to Microsoft Access

    Hey guys,

    I'm new to Microsoft Access and have a question concerning its importing, exporting capabilities. My employer has an extensive word document that he would like to use to fill Microsoft access fields with. The document is as follows:
    Click image for larger version. 

Name:	Example.png 
Views:	20 
Size:	32.0 KB 
ID:	23340



    He would want to fill several fields with data such as Facility, Town, Address, etc. Is this possible?

    Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Not without a lot of complicated VBA.

    Is that a Word table in the image?

    If the Word document can first be saved as a simple text file or Excel, would be simple to import.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Runlynch is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    7
    Microsoft word does have the capacity to make a document convert into simple text format. However, its formatted in such a way that makes the simple text output awkward: (Field 1: Clover Rest Home
    Field 2: 28 Washington St
    Field 3: Columbia
    Field 4: 856-999-9999)

    As opposed to: Field 1: Clover Rest Home Field 2: 28 Washington St Field 3: Columbia Field 4: 856-999-9999

    Microsoft access seems to break down fields in a horizontal manner using vertical lines to break them up, which would be a problem if I am using the above example.

    I personally believe there is no way to accomplish this, but he swears there is.

    Sorry if this is confusing, I am still getting a hang of the program so any additional advice would be really appreciated!

  4. #4
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,406
    actually, the vertical text output is potentially easier to manage than the horizontal one for importing. Provide an example of say 3 'records' as supplied in the conversion to text - ideally as a attached text file. I note your example output does not include 'long term care facility', medicaid' etc. and you also appear to have duplicate records for House of the Good Sheppard - so you would also need to decide how these are to be treated - all imported/only first one etc

  5. #5
    Runlynch is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    7
    Quote Originally Posted by Ajax View Post
    actually, the vertical text output is potentially easier to manage than the horizontal one for importing. Provide an example of say 3 'records' as supplied in the conversion to text - ideally as a attached text file. I note your example output does not include 'long term care facility', medicaid' etc. and you also appear to have duplicate records for House of the Good Sheppard - so you would also need to decide how these are to be treated - all imported/only first one etc
    Can I import with qualifications that eliminate the text I don't want? Whenever I try the import wizard it doesn't seem to get that specific.

    Thank you so much for your help guys.

  6. #6
    Runlynch is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    7
    With some specific copying and special pasting of text, I was able to get something like this in excel. Is there a way I can convert this document (with how its currently formatted) into access? It would have to be broken up into the following fields: 1. Facility Name, 2. Address, 3. Phone Number

    Click image for larger version. 

Name:	Example 2.png 
Views:	14 
Size:	20.2 KB 
ID:	23432

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Runlynch is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    7
    So it seems like VBA is the way to go. Can I use the Visual Studio Express to do this? Or do I need to buy the full version?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    I don't know.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,406
    it seems like VBA is the way to go. Can I use the Visual Studio Express to do this? Or do I need to buy the full version?
    you have vba in 2016 so don't understand why you would want vs express, and anyway, it can be done in SQL

    You have fairly straight forward data - always 4 rows per record so do the following

    1. add a heading to your data column - call it say 'data'. Save the file - we'll call it NameList - and close

    2. Now in Access, import the file (External data tab>Excel) and follow the prompts, ensure you tick the 'has headers' box and let access choose a primary key (which will be called ID) and the table name will be sheet1 - if not substitute your name for sheet1 in the following query

    Code:
    SELECT [F].[RecID]+1 AS ContactPK, F.Data AS Facility, A1.Data AS Addr1, A2.Data AS Addr2, P.Data AS Phone INTO Contacts
    FROM (((SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
    FROM Sheet1
    WHERE ((([ID]-1) Mod 5)+1)=1)  AS F INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
    FROM Sheet1
    WHERE ((([ID]-1) Mod 5)+1)=2)  AS A1 ON F.RecID = A1.RecID) INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
    FROM Sheet1
    WHERE ((([ID]-1) Mod 5)+1)=3)  AS A2 ON A1.RecID = A2.RecID) INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
    FROM Sheet1
    WHERE ((([ID]-1) Mod 5)+1)=4)  AS P ON A2.RecID = P.RecID
    When you run this query, it will create a table called contacts ait a unique contact primary key and your data

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 10-06-2015, 09:12 AM
  2. Mail Merge in Microsoft Word
    By imintrouble in forum Misc
    Replies: 0
    Last Post: 12-02-2011, 03:48 PM
  3. Replies: 1
    Last Post: 09-13-2011, 01:52 PM
  4. Replies: 0
    Last Post: 10-13-2010, 03:28 PM
  5. Linking Microsoft Word Form to Access Table
    By jrockusa in forum Access
    Replies: 1
    Last Post: 12-03-2009, 05:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums