MarkOs
asked on
Import word form information into Access tables
I have approximately 8000 word documents with screeds of data for running CNC mills - each document is in the exact same format, specific to a part number and i idealy want this information placed into access.
The forms however do not have bookmark labels set so i'm at a bit of a loss as to where to go from here.
The forms layout can be seen here: http://www.blacknight.co.nz/BV504-03-1OP.htm just to give you an idea of what i have to work with.
The only ideas i have are these:
1. Is there a way i can create a macro in word to give book mark labels to all the fields in all the documents? If so, how would i do that?
2. When i converted it to html, i noticed some bookmark labels were created, is it possible that some default ones are used that i could map to? Or is there even a way i could automatically convert each document to html and then into access?
Having such a large number of documents i need everything to be automated so if anyone can shed some light on this for me it would be greatly appreciated.
The forms however do not have bookmark labels set so i'm at a bit of a loss as to where to go from here.
The forms layout can be seen here: http://www.blacknight.co.nz/BV504-03-1OP.htm just to give you an idea of what i have to work with.
The only ideas i have are these:
1. Is there a way i can create a macro in word to give book mark labels to all the fields in all the documents? If so, how would i do that?
2. When i converted it to html, i noticed some bookmark labels were created, is it possible that some default ones are used that i could map to? Or is there even a way i could automatically convert each document to html and then into access?
Having such a large number of documents i need everything to be automated so if anyone can shed some light on this for me it would be greatly appreciated.
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(strFileN ame)
...
doc.Close
Set doc = Nothing
strFileName = Dir
Loop
Dim strFileName As String
strFileName = Dir("c:\foldername\*.doc")
Do Until Len(strFileName) = 0
Set doc = wd.Documents.Open(strFileN
...
doc.Close
Set doc = Nothing
strFileName = Dir
Loop
ASKER
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???
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.
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.
ASKER
The Set doc = wd.Documents.Open(strFileN ame) 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?????
ASKER
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.Applica tion")
Dim strFileName As String
strFileName = Dir("c:\oasis\*.doc")
Do Until Len(strFileName) = 0
Set doc = wd.Documents.Open("c:\oasi s\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.
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.Applica
Dim strFileName As String
strFileName = Dir("c:\oasis\*.doc")
Do Until Len(strFileName) = 0
Set doc = wd.Documents.Open("c:\oasi
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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??
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:\oasi s\" & 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.
Set doc = wd.Documents.Open("c:\oasi
...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.
ASKER
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.
...
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.
...
ASKER
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 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?
ASKER
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?
Is there a way to run certain menu commands after the set.Doc command?
ASKER
Actually, beat you to it :o)
If ActiveDocument.ProtectionT ype <> wdNoProtection Then
ActiveDocument.Unprotect
End If
Works a charm.
If ActiveDocument.ProtectionT
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!
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!
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.Applica
Set doc = wd.Documents.Open("c:\fold
...
doc.Close
Set doc = Nothing
wd.Quit
Set wd = Nothing
End Sub