Word-Excel-Access

[TommyF]

I don't know if what I am tring to do is possible.

At the moment we do our delivery notes in word (We have about 100 seperate word docs) which are just spaced out to print on pre-printed forms. We have our production system in access which I have just added our delivery notes into. What I am trying to do is import the delivery address out of these docs in to a access table.

The only way I can think to do it is to import the word docs in to excel which will put them in a cell and then I can import it into access from there, but I am not to sure how to do it.

Or is there a better way?

Thanks

 

[MarkWright]

Hello,I useally copy my items from word and paste them in an Excel sheet. From there I add the list to Access
here is a small way to help you get on the right track.
Good Luck. Its not that hard
-Mark-

list in word
copy and add into Excel

open Access , under tables ,right click and choose Import.
In the next form under Datatyp choose —Microsoft Excel and choose your excel list.Extract the excel form, with the Assistant in Access you just follow the directions and you become a Table in Access with the information from Excel. You can change the Access table to meet your needs.

 

[TonyJollans]

Hi,

Going via Excel can help if you have to do it manually but dealing with a hundred or more docs manually might be a bit of a chore. Personally I would prefer to do it in VBA in Word and write new rows direct to my Access Table - the basic code is simple enough (I can let you have a sample if you want) - but automation would depend on how easy it was to identify the desired text in the Word doc(s) via VBA code.

Tony

 

[TommyF]

Thnaks Tony

I would love a sample of your code if that is ok.

Thanks

 

[TonyJollans]

OK, try this - I think it'll work ..

Dim Access As Object
Dim Table As Object
Dim RecordSet As Object
Dim TableName As String
        
' Select desired text in doc - this just takes first 2 lines
Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=2, Extend:=wdExtend
    
' Open Access Application - then Open Database
    
Set Access = CreateObject("Access.Application")
Access.OpenCurrentDatabase "C:\MyPath\MyDatabase.mdb"
    
TableName = "MyTable"
    
With Access.CurrentDb
    
    ' Create Table (if needed) - with a Single Text Column
    ' .. and add it to the TableDefs Collection
        
    Set Table = .CreateTableDef(TableName)
    Table.Fields.Append Table.CreateField("Address", 10, 255)
    .TableDefs.Append Table
        
    ' Open the table just created
    Set RecordSet = .OpenRecordset(TableName)
        
    ' Add a new row - populate it - update table - and close
    RecordSet.AddNew
    RecordSet![Address] = Selection.Text
    RecordSet.Update
    RecordSet.Close

End With
    
' Tidy up

Access.Quit
Set Access = Nothing

A couple of points
[ul][li]you may find you need to replace paragraph marks with Control/Linefeed combinations - ask if you need help with this[/li]
[li]this creates a table - which you'll only want to do once - and just processes a single "address" in a single doc - you'll need to put it in your own loop[/li][/ul]

Enjoy,
Tony

 

[TommyF]

Thanks Tony I will try it out.