Import word form information into Access tables

I wouldn't bother with the whole HTML thing - I think it will just make things more complicated.  You can extract info from Word documents pretty easily, for example...

    With doc.Tables(1)
        rs![File Name] = .Cell(2, 2)
        rs![Date] = .Cell(2, 4)
        rs![Description] = .Cell(3, 2)
    End With

...etc.  It would seem to make sense that you do this from a procedure in an Access module, creating an instance of word with something like...

Sub ImportCNCSheet()
    Dim wd As Word.Application '(you have to add a reference (Tools->References...) to the Word Object Library)
    Dim doc As Word.Document

    Set wd = CreateObject("Word.Application")
    Set doc = wd.Documents.Open("c:\foldername\filename.doc")

    ...

    doc.Close
    Set doc = Nothing

    wd.Quit
    Set wd = Nothing
End Sub

You could loop though all the documents in a certain location using the Dir function, something like...

    Dim strFileName As String

    strFileName = Dir("c:\foldername\*.doc")

    Do Until Len(strFileName) = 0
        Set doc = wd.Documents.Open(strFileName)        
       
        ...

        doc.Close
        Set doc = Nothing      

        strFileName = Dir
    Loop

ok, i'll have a bit of a play with that and see how i get on.

How to i activate the access module, modules is something i've never really used in access. What is the advantage???

Modules are very similar to the modules behind forms.  You could put all this code behind in the event procedure of a button on a form if you like.  The main advantages of Modules (without a form) are...

1.  Public Subs and Functions in a Module in Access can be called from other procedure.  Functions (but not subs) in Modules can be called from loads of places including SQL, macros, events, toolbar buttons etc.

2.  You can call procedures in modules anytime - you have to open a form before calling one of its procedures.

3.  They can hold bulky code, keeping your forms 'lightweight' and just dealling with user interface stuff.

4.  I often find it easier to test code in Modules because you can just press F5 (answering your other question) to try it out (as long as the procedure doesn't accept arguments - even then you can just type the call out in the Immediate window!) - with forms you often have to switch back Form View and click on a button.

The        Set doc = wd.Documents.Open(strFileName)      code line is causing error 5174, file could not be found, it displays the file name so it knows it's there, is there perhaps a restriction on file name length, or can it have spaces?????

that line works fine with the actual file name entered but does not call from strFilename properly.

Currently my code looks like this:
Private Sub Command1_Click()
   Dim wd As Word.Application '(you have to add a reference (Tools->References...) to the Word Object Library)
   Dim doc As Word.Document

   Set wd = CreateObject("Word.Application")
   Dim strFileName As String

   strFileName = Dir("c:\oasis\*.doc")

   Do Until Len(strFileName) = 0
       Set doc = wd.Documents.Open("c:\oasis\BV504-03-1OP.doc")
       
       doc.Close
       Set doc = Nothing

        strFileName = Dir
   Loop

   wd.Quit
   Set wd = Nothing
   With doc.Tables
       rs![FileName] = .Cell(2, 2)
       rs![Date] = .Cell(2, 4)
       rs![Description] = .Cell(3, 2)
   End With
End Sub

The .Cell area of the last bit gives a compile error also

I had to remove the (1) after doc.Tables, what does that mean, maybe that's the problem.

Ok, this is so close, thanks heaps for your help.

I think the reason strFileName is not working is because it's not retaining the "c:\Oasis\" part at the begining of the filename. How can i retain that?

I also have one more issue - the data is all in word forms which are locked, is there a form unlock statement in vb that i can run before accessing the table data??

Oh I am sorry, you're quite right about the "c:\oasis\" thing...

    Set doc = wd.Documents.Open("c:\oasis\" & strFileName)

...should do the trick.

Not really sure about these word forms.  You could either post an example document (in exactly the same way as the html but in .doc format) or email a copy to me at mrrobinee@yahoo.co.uk and I'll take a look.  Leave a message on here to let me know if you have done this.

Have e-mailed the doc to you, the rest works great, thankyou.

Glad I could help!

Looking at the document, it seems that all the data is in Word fields.  You could therefore use something like...

        rs![FileName] = doc.Fields(1).Result
        rs![Date] = doc.Fields(2).Result
        rs![Description] = doc.Fields(3).Result

You might want to consider the pros and cons of storing the times in Time (Date/Time) fields.  The notes fields could be concatened together (using &) and put into a large text field or a memo field.

I would strongly recommend that you store the repeating Tools records in a seperate table, linked to the other data by whatever uniquely identifies a sheet record (it's primary key field - FileName maybe?).  Something like...

    Dim rsPrograms As DAO.Recordset
    Dim rsTools As DAO.Recordset
    Dim intTool As Integer

    ...

    Set rsPrograms = CurrentDb.Open("Programs")
    Set rsTools = CurrentDb.Open("Tools")

    ...

    '(inside the loop)

        rsPrograms![FileName] = doc.Fields(1).Result
        rsPrograms![Date] = doc.Fields(2).Result
        rsPrograms![Description] = doc.Fields(3).Result & doc.Fields(4).Result

        ...

        For intTool = 1 To 12
            If Len(doc.Fields(27 + (intTool - 1) * 5).Result) > 0 Then 'check if there is a record here
                rsTools.AddNew

                rsTools!FileName = doc.Fields(1).Result
                rsTools!ToolSTN = intTool 'not sure if you need this
                rsTools!Type = doc.Fields(27 + (intTool - 1) * 5).Result
                rsTools!Insert = doc.Fields(27 + (intTool - 1) * 5 + 1).Result
                rsTools!Grade = doc.Fields(27 + (intTool - 1) * 5 + 2).Result

                ...

                rsTools.Update
            End If
        rsTools.
       
        ...

Yeah, thanks, that was the plan.

The doc.fields.result option does give me better information in my tables and enables me to format the table to date/time fields, numbers etc... (that didn't work before).

However, i still can't use documents where the form fields are protected - did you get anywhere with how to turn that off via vb?

The Word menu option to do this it Tools/Unprotect Document.

Is there a way to run certain menu commands after the set.Doc command?

Actually, beat you to it :o)

If ActiveDocument.ProtectionType <> wdNoProtection Then
    ActiveDocument.Unprotect
End If

Works a charm.

Nice one, you're rolling with it now (I had to go to sleep - it only took you a few minutes anyway!).

You can find loads of stuff by just pressing the dot and looking up and down the list.

The help can sometimes help but...

The Object Browser (F2) is often a great way to find something you know should exist but you don't where in the object strucure it lies...  

And my final tip is putting a break-point on line (F9), running the code and, when it halts, selecting an object in the code and pressing Shift+F9 and then clicking Add (to add a Watch).  You can then explore an objects properties at runtime, see their values and expand collections to see their contents - all very usefull.

Good luck with it all!