Exporting Acces to Word VBA

[Neilster]

Hi Guy's

In I have a table/form which I need to export on to a Word Doc. so far I have this code which works fine but I need to export 'ContactName', 'ContactTitle' 'Address','postcode' and so on.

Dim objWord As Word.Application
Dim doc As Word.Document
Dim WordHeaderFooter As HeaderFooter
Dim rs As Recordset
Dim ContactName As String
Dim path As String

Set objWord = CreateObject("Word.Application")

With objWord
.Visible = True

Set doc = .Documents.Add
doc.SaveAs CurrentProject.path & "\TestDoc.doc"
End With

With objWord.Selection

.Font.Name = "Arial MS"
.Font.Size = 10


.TypeText "Dear Customer,"
.TypeParagraph

End With

doc.Save
doc.Activate

I've tried this bit of code.

.forms ("fldContactName").Result = Me.ContactName

 

[arnelgp]

use mailmerge feature of word

 

[Neilster]

I'd rather use a command button for this project.

 

[arnelgp]

create word document first with Bookmarks, then later on you can replace these bookmarks with field values from your table through vba.

 

[Neilster]

Does anyone know how to set this to 'Environ' I want to set the path to any desktop.

And also set it to edit and not read only?

Help much appreciated

Dim appword As Word.Application
Dim doc As Word.Document
Dim path As String
Dim User As String

On Error Resume Next
Err.Clear
path = Environ("user") & "\Documents\Template.Docx"
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
ReadOnly = False
End If
Set doc = appword.Documents.Open(path, , True)
With doc

.FormFields("txtCompanyName").Result = Me.txtCompanyName
.FormFields("txtContactName").Result = Me.txtContactName
.FormFields("txt1stLineOfAddress").Result = Me.txt1stLineOfAddress
.FormFields("cboCity").Result = Me.cboCity1
.FormFields("txtPostCode").Result = Me.txtPostCode

End With
appword.Visible = True
appword.Activate

Set doc = Nothing
Set appword = Nothing

 

[arnelgp]

    Public Function SpecialFolderPath(strFolder As String) As String
        ' Find out the path to the passed special folder. User on of the following arguments:
        ' Options For specical folders
    '        AllUsersDesktop
    '        AllUsersStartMenu
    '        AllUsersPrograms
    '        AllUsersStartup
    '        Desktop
    '        Favorites
    '        Fonts
    '        MyDocuments
    '        NetHood
    '        PrintHood
    '        Programs
    '        Recent
    '        SendTo
    '        StartMenu
    '        Startup
    '        Templates
     
       On Error GoTo ErrorHandler
     
       'Create a Windows Script Host Object
          Dim objWSHShell As Object
          Set objWSHShell = CreateObject("WScript.Shell")
     
       'Retrieve path
          SpecialFolderPath = objWSHShell.SpecialFolders(strFolder & "")
     
CleanUp:
       ' Clean up
          Set objWSHShell = Nothing
          Exit Function
     
    '**************************************
    '*      Error Handler
    '**************************************
ErrorHandler:
        MsgBox "Error finding " & strFolder, vbCritical + vbOKOnly, "Error"
        Resume CleanUp
    End Function

to get desktop path:

specialfolderpath("Desktop")

 

[arnelgp]

ReadOnly propety applies to Document object.

doc.ReadOnly = False

 

[Neilster]

Do I call that from a modal or does it sit under click command?

doc.ReadOnly = False 'compile error' can't assign read-only property?

Thanks for your help.

 

[arnelgp]

im sorry just remove that (doc.Readonly=False) from your code. instead, you already opened the word document in readonly mode:

Set doc = appword.Documents.Open(path, , True)

should be:

Set doc = appword.Documents.Open(path, , False)

 

[Neilster]

It now opens the word application but not to the 'Template.docx'

Dim appword As Word.Application
Dim doc As Word.Document
Dim path As String
Dim User As String

On Error Resume Next
Err.Clear
path = "C:\Users\User\Documents\template.doc"
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
End If
Set doc = appword.Documents.Open(path, , False)
With doc

.FormFields("txtCompanyName").Result = Me.txtCompanyName
.FormFields("txtContactName").Result = Me.txtContactName
.FormFields("txt1stLineOfAddress").Result = Me.txt1stLineOfAddress
.FormFields("cboCity").Result = Me.cboCity1
.FormFields("txtPostCode").Result = Me.txtPostCode

End With
appword.Visible = True
appword.Activate

Set doc = Nothing
Set appword = Nothing

 

[Neilster]

Sorted it out, it now opens up in edit mode, still need to VBA 'Environ' ?

 

[Neilster]

And how do I set the font text and size? with also 'Dear' before txtContactName?

 

[arnelgp]

which Path are you looking for?

 

[Neilster]

ideally I want the path set to any desktop, any 'User' and set the font text and size? with also 'Dear' before txtContactName?

Thanks for all your help so far

 

[arnelgp]

With appword.ActiveDocument.Content.Find
.Text = "Dear"
.Forward = True
.Execute
If .Found = True Then
'.Parent.Italic = True
appword.ActiveDocument.Content.Font.Name = "Arial"
appword.ActiveDocument.Content.Font.Bold = True
appword.ActiveDocument.Content.Font.Size = 14
End If
End With

 

[Neilster]

Is this correct because it's not working?

Or am I missing something

On Error Resume Next
Err.Clear
path = "C:\Users\User\Documents\template.docx"
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
End If
Set doc = appword.Documents.Open(path, , False)
With doc

.FormFields("txtCompanyName").Result = Me.txtCompanyName
.FormFields("txtContactName").Result = Me.txtContactName
.FormFields("txt1stLineOfAddress").Result = Me.txt1stLineOfAddress
.FormFields("cboCity").Result = Me.cboCity1
.FormFields("txtPostCode").Result = Me.txtPostCode

With appword.ActiveDocument.Content.Find
.Text = "Dear"
.Forward = True
.Execute
If .Found = True Then
'.Parent.Italic = True
appword.ActiveDocument.Content.Font.Name = "Kalinga"
appword.ActiveDocument.Content.Font.Bold = True
appword.ActiveDocument.Content.Font.Size = 11
End If
End With

 

[arnelgp]

With appword.ActiveDocument.Content.Find
.Wrap = wdFindContinue
.MatchCase = False
.Text = "Dear"
.Forward = True
.Execute
If .Found = True Then
with .Parent.Font
.Name = "Kalinga"
.Bold = True
.Size = 11
end with
End If
End With

 

[Neilster]

works fine thank you.

I just need to set the path to any user I've tried

Dim path as String

path = "C:\Users\"&User&"\Douments\template.docx

just not woking

 

[arnelgp]

you can use the function i posted here earlier.

path = SpecialFolderPath("MyDocuments") & "\template.docx"

 

[Neilster]

It comes up with 'compiler error' label not defined on the error handler line.

Public Function SpecialFolderPath(strFolder As String) As String

MyDocuments

On Error GoTo ErrorHandler

'Create a Windows Script Host Object
Dim objWSHShell As Object
Set objWSHShell = CreateObject("WScript.Shell")

'Retrieve path
SpecialFolderPath = objWSHShell.SpecialFolders(strFolder & "")

CleanUp:
' Clean up
Set objWSHShell = Nothing

End Function