Help with import from Word/excel

[SF]

Hi,

I have a database for a Project Tracking consists of
1 - tblProjects (Pr_ObjectID (PK), ProjectName, StartDate, EndDate)
2 - tblProjectWorkplan (PWID (PK), Pr_ObjectID (FK), Workplan, Evidence...)

I need to imports 100 of workplan (each workplan contain about 20 to 25
activities) that send to me in Word (table) format mostly (a few come with
Excel). I need to import those to tblProjectWorkplan. What I have done to
import these workplan are as follow:

1 - Copy all the workplan activities of a select workplan onto a excel
Workplan template
2 - Use Append query to add these activitye to tblProjectWorkplan

I have a problem:

The workplan template did provide additional rows as use has used the Enter
key within each cell of the word table, I am looking for a better and
fassster way to replace a Enter key with a character before paste the result
into the Workplan template.

Is there a way to do that or if someone suggest a better to import word
table into Access?

SF

 

[KC-Mass]

Hi

Here is some code from Microsoft to import data from Word Form to Access
directly.
I am sure you could easily modify to read from Word Table. You should
probably post your question in a Word newsgroup.


Code comes from http://msdn2.microsoft.com/en-us/library/aa155434

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!FirstName = doc.FormFields("fldFirstName").Result
!LastName = doc.FormFields("fldLastName").Result
!Company = doc.FormFields("fldCompany").Result
!Address = doc.FormFields("fldAddress").Result
!City = doc.FormFields("fldCity").Result
!State = doc.FormFields("fldState").Result
!ZIP = doc.FormFields("fldZIP1").Result & _
"-" & doc.FormFields("fldZIP2").Result
!Phone = doc.FormFields("fldPhone").Result
!SocialSecurity = doc.FormFields("fldSocialSecurity").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!AdditionalCoverage = _
doc.FormFields("fldAdditional").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub

 

[SF]

Thank you very much for your prompt response. I think your input would shed
a basic idea on import data from Word.

Thank you once again.

SF