Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Import from Word Table (Access 2000, WXP)

    Is there anyway to import a table from MS Word into a table in Access? Word is not one of the file types that Import seems to expect.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from Word Table (Access 2000, WXP)

    No, Access does not import Word tables directly. You can copy and paste the table into an Excel workbook - Access does support importing from Excel.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Import from Word Table (Access 2000, WXP)

    Another option: Use the Word Convert Table to Text command (Table menu) to convert table to a delimited text file, then import text file into Access. Simple Word sub that does this:

    Sub ConvertTableToText()

    Dim doc As Word.Document
    Set doc = ActiveDocument

    ' Save table as comma-delimited text file:
    doc.Tables(1).ConvertToText wdSeparateByCommas
    doc.SaveAs "Document1.txt", wdFormatText
    Set doc = Nothing

    End Sub

    This will create a comma-delimited text file that can be imported into Access. Example assumes Word doc contains only a single table. You could automate this process from Access. Text file can be imported using TransferText method:

    Public Sub ImportTextFile()

    Dim strFile As String
    strFile = "Cocuments and SettingsMarkDMy DocumentsDocument1.txt"
    ' Import spec not required for delimited text:
    DoCmd.TransferText acImportDelim, , "Table2", strFile, True

    End Sub

    In this example "Table2" did not previously exist; new table created. Sample data used as test text:

    Field1, Field2, Field3, Field4
    ABC, 123, 1/15/2004, SMITH
    XYZ, 456, 12/31/1999, JONES
    PDQ, 789, 6/06/2006, JOHNSON

    In this case, Access correctly interpreted Field 1 & 4 as Text, Field 2 as number, and Field 3 as Date. You'd have to test to ensure correct results when table imported.

    HTH

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import from Word Table (Access 2000, WXP)

    Thanks for the hint -- I did find out that importing from Excel works OK.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import from Word Table (Access 2000, WXP)

    Thanks for the suggestion. Actually, it looks like importing from Excel will work the best. What I'm trying to do is automate a process of copying a table from a web site (I have no control over the format of the table, but it looks like the format is consistent). Then I need to extract one column (field) from the table and, preferably, import that into a table in Access. The data is simply a list of names shown as Last name followed by a comma followed by first name. I then want to create mailing labels in First/Last name order plus add a common address to each name. The latter part should be relatively easy to do in Access. For example I can have the user type in the common address and then add that to each label.

    The difficulty is in exactly how to access the list of names from Excel. I can get the user to copy the data base into Excel, but I really need to automate everything after that. Thus, I'll need code to import the Excel db. Once in an Access table things should be relatively easy to handle.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Import from Word Table (Access 2000, WXP)

    As far as accessing the web site's table from Excel, one thing you might try is an Excel Web Query (Data menu, Get External Data, New Web Query.... command). This would be simplest method, if the format of the web site is compatible with the Excel Web Query functionality. Attached is an illustration of the Edit Web Query dialog box (query already created), using the Lounge's main page as example. Note that you can specify which table or tables to query. When you click "OK" after creating (or editing) Web Query, you will see a little green globe icon spinning in the Excel Status Bar, which indicates that the data is being retrieved from the web site. You then will see the data displayed in the spreadsheet. You can test this using the example illustrated; your spreadsheet will display the current Louge statisitics (just what you always wanted...) To refresh data, click the Data menu or External Data toolbar "Refresh Data" command (exclamation point icon). - the little globe icon will spin again while data is refreshed. When I tried this just now, the test spreadsheet was updated with current Lounge data, with new posts in parentheses since am now logged in (of course these cells are now displayed as text rather than numbers; the "Last Post" column is correctly displayed as Date/Time value).

    It will probably require some trial and error to get set up correctly, but if the web site in question doesn't change its format too often, once set up should work OK, just use Refresh Data command to update spreadsheet.

    HTH
    Attached Images Attached Images

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import from Word Table (Access 2000, WXP)

    Thanks Mark, I'll give it a try. I had never heard of this facility, but it may turn out to be very useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •