[Joergen Bondesen]

Hi NG.

I am using below macro for mailmerge *.txt files containing specialsigns
(letter) æ, ø and å, so I must make a new tablefile (*.docc) to avoid the
special sign to be displayed with wrong characters.
My probleme is that I am not able to kill *.docc because it seams that Word
still is connected to *.docc until the macro is compleated.
Is it possible to "disconnect" Word while the macro is running?


Option Explicit
Const sep As String = ";" '*****
Const MergeStartName As String = "Merged_"
Const NewMergeFileExt As String = "docc"

'----------------------------------------------------------
' Procedure : AutoOpen
' Date : 20070224
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Auto Mailmerge
' Note : ***** = your settings
'----------------------------------------------------------
'
Sub AutoOpen()

Dim MyPath As String
Dim MyFile As String

Dim lFLen As Variant

Dim source As Document
Dim txtdotfullpath As String

Dim MyfileLen As Long
Dim MyfileName As String

Application.ScreenUpdating = False

MyPath = CurDir

'// Kill
On Error Resume Next
Kill MergeStartName & "*.*"
Kill "*." & NewMergeFileExt
On Error GoTo 0

'// Specified file
MyFile = Dir(MyPath & "\*_Replace.txt") '*****

'// Loop for all files
Do While MyFile ""

'// File Size
lFLen = FileLen(MyPath & "\" & MyFile)

'// Not empty file
If lFLen 0 Then

'// Merge
SendKeys "{enter}"

'// Set (new file)
Set source = Documents.Open(FileName:=MyPath & "\" _
& MyFile, Encoding:=msoEncodingWestern)

'// Convert To Table
source.Range.ConvertToTable Separator:=sep

'// New mergefile Fullpath
txtdotfullpath = MyPath & "\" & MyFile & "." _
& NewMergeFileExt

'// New mergefile save and close
With source
.SaveAs FileName:=txtdotfullpath, _
FileFormat:=wdFormatDocument
.Close
End With

'// Len
MyfileLen = Len(MyFile)

'// Filename
MyfileName = Left(MyFile, MyfileLen - 4)


'// Merge
SendKeys "{enter}"

ActiveDocument.MailMerge.OpenDataSource _
Name:=txtdotfullpath, _
ConfirmConversions:=False, ReadOnly:=False, _
LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

'// Save merged doc
ChangeFileOpenDirectory MyPath & "\"
ActiveDocument.SaveAs FileName:=MergeStartName _
& MyfileName & "_Postpaid_Replace.doc", FileFormat:= _
wdFormatDocument, AddToRecentFiles:=True

'// Close merged file
ActiveDocument.Close

' Stop
'// On Error Resume Next
'// MY PROBLEME
'''Kill txtdotfullpath

Set source = Nothing

End If

'// Next
MyFile = Dir
Loop

'// Close "template" doc without saving
'1. line ??
SendKeys "{ESC}", True
SendKeys "%{F4}"
SendKeys "%N"

Set source = Nothing

Application.ScreenUpdating = True

End Sub


--
Best regards from
Joergen Bondesen

[Peter Jamieson]

Try:
a. add the following declaration
Dim objMMMD AS Word.Document
putting the following line before the "Kill":
b. before your OpenDataSource, insert
Set objMMMD = ActiveDocument
objMMMD.MailMerge.MainDocumentType=wdFormLetters ' or whatever document type
you need
c. (for consistency, change the OpenDataSource to
objMMMD.MailMerge.OpenDataSource)
d. before the "Kill", insert
objMMMD.MailMerge.MainDocumentType=wdNotAMergeDocu ment
e. make sure you set objMMMD to Nothing at the appropriate point

In Word 2003, you may be able to do (d) using:

activedocument.MailMerge.Datasource.Close

Can't remember if that's in Word 2002, but it's certainly not in earlier
versions.

Peter Jamieson


"Joergen Bondesen" wrote in message
...
Hi NG.

I am using below macro for mailmerge *.txt files containing specialsigns
(letter) æ, ø and å, so I must make a new tablefile (*.docc) to avoid the
special sign to be displayed with wrong characters.
My probleme is that I am not able to kill *.docc because it seams that
Word still is connected to *.docc until the macro is compleated.
Is it possible to "disconnect" Word while the macro is running?


Option Explicit
Const sep As String = ";" '*****
Const MergeStartName As String = "Merged_"
Const NewMergeFileExt As String = "docc"

'----------------------------------------------------------
' Procedure : AutoOpen
' Date : 20070224
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Auto Mailmerge
' Note : ***** = your settings
'----------------------------------------------------------
'
Sub AutoOpen()

Dim MyPath As String
Dim MyFile As String

Dim lFLen As Variant

Dim source As Document
Dim txtdotfullpath As String

Dim MyfileLen As Long
Dim MyfileName As String

Application.ScreenUpdating = False

MyPath = CurDir

'// Kill
On Error Resume Next
Kill MergeStartName & "*.*"
Kill "*." & NewMergeFileExt
On Error GoTo 0

'// Specified file
MyFile = Dir(MyPath & "\*_Replace.txt") '*****

'// Loop for all files
Do While MyFile ""

'// File Size
lFLen = FileLen(MyPath & "\" & MyFile)

'// Not empty file
If lFLen 0 Then

'// Merge
SendKeys "{enter}"

'// Set (new file)
Set source = Documents.Open(FileName:=MyPath & "\" _
& MyFile, Encoding:=msoEncodingWestern)

'// Convert To Table
source.Range.ConvertToTable Separator:=sep

'// New mergefile Fullpath
txtdotfullpath = MyPath & "\" & MyFile & "." _
& NewMergeFileExt

'// New mergefile save and close
With source
.SaveAs FileName:=txtdotfullpath, _
FileFormat:=wdFormatDocument
.Close
End With

'// Len
MyfileLen = Len(MyFile)

'// Filename
MyfileName = Left(MyFile, MyfileLen - 4)


'// Merge
SendKeys "{enter}"

ActiveDocument.MailMerge.OpenDataSource _
Name:=txtdotfullpath, _
ConfirmConversions:=False, ReadOnly:=False, _
LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

'// Save merged doc
ChangeFileOpenDirectory MyPath & "\"
ActiveDocument.SaveAs FileName:=MergeStartName _
& MyfileName & "_Postpaid_Replace.doc", FileFormat:= _
wdFormatDocument, AddToRecentFiles:=True

'// Close merged file
ActiveDocument.Close

' Stop
'// On Error Resume Next
'// MY PROBLEME
'''Kill txtdotfullpath

Set source = Nothing

End If

'// Next
MyFile = Dir
Loop

'// Close "template" doc without saving
'1. line ??
SendKeys "{ESC}", True
SendKeys "%{F4}"
SendKeys "%N"

Set source = Nothing

Application.ScreenUpdating = True

End Sub


--
Best regards from
Joergen Bondesen

[Joergen Bondesen]

Hi Peter

Perfect.
Thanks very much.

--
Best regards
Joergen Bondesen


"Peter Jamieson" wrote in message
...
Try:
a. add the following declaration
Dim objMMMD AS Word.Document
putting the following line before the "Kill":
b. before your OpenDataSource, insert
Set objMMMD = ActiveDocument
objMMMD.MailMerge.MainDocumentType=wdFormLetters ' or whatever document
type you need
c. (for consistency, change the OpenDataSource to
objMMMD.MailMerge.OpenDataSource)
d. before the "Kill", insert
objMMMD.MailMerge.MainDocumentType=wdNotAMergeDocu ment
e. make sure you set objMMMD to Nothing at the appropriate point

In Word 2003, you may be able to do (d) using:

activedocument.MailMerge.Datasource.Close

Can't remember if that's in Word 2002, but it's certainly not in earlier
versions.

Peter Jamieson


"Joergen Bondesen" wrote in message
...
Hi NG.

I am using below macro for mailmerge *.txt files containing specialsigns
(letter) æ, ø and å, so I must make a new tablefile (*.docc) to avoid the
special sign to be displayed with wrong characters.
My probleme is that I am not able to kill *.docc because it seams that
Word still is connected to *.docc until the macro is compleated.
Is it possible to "disconnect" Word while the macro is running?


Option Explicit
Const sep As String = ";" '*****
Const MergeStartName As String = "Merged_"
Const NewMergeFileExt As String = "docc"

'----------------------------------------------------------
' Procedure : AutoOpen
' Date : 20070224
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Auto Mailmerge
' Note : ***** = your settings
'----------------------------------------------------------
'
Sub AutoOpen()

Dim MyPath As String
Dim MyFile As String

Dim lFLen As Variant

Dim source As Document
Dim txtdotfullpath As String

Dim MyfileLen As Long
Dim MyfileName As String

Application.ScreenUpdating = False

MyPath = CurDir

'// Kill
On Error Resume Next
Kill MergeStartName & "*.*"
Kill "*." & NewMergeFileExt
On Error GoTo 0

'// Specified file
MyFile = Dir(MyPath & "\*_Replace.txt") '*****

'// Loop for all files
Do While MyFile ""

'// File Size
lFLen = FileLen(MyPath & "\" & MyFile)

'// Not empty file
If lFLen 0 Then

'// Merge
SendKeys "{enter}"

'// Set (new file)
Set source = Documents.Open(FileName:=MyPath & "\" _
& MyFile, Encoding:=msoEncodingWestern)

'// Convert To Table
source.Range.ConvertToTable Separator:=sep

'// New mergefile Fullpath
txtdotfullpath = MyPath & "\" & MyFile & "." _
& NewMergeFileExt

'// New mergefile save and close
With source
.SaveAs FileName:=txtdotfullpath, _
FileFormat:=wdFormatDocument
.Close
End With

'// Len
MyfileLen = Len(MyFile)

'// Filename
MyfileName = Left(MyFile, MyfileLen - 4)


'// Merge
SendKeys "{enter}"

ActiveDocument.MailMerge.OpenDataSource _
Name:=txtdotfullpath, _
ConfirmConversions:=False, ReadOnly:=False, _
LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

'// Save merged doc
ChangeFileOpenDirectory MyPath & "\"
ActiveDocument.SaveAs FileName:=MergeStartName _
& MyfileName & "_Postpaid_Replace.doc", FileFormat:= _
wdFormatDocument, AddToRecentFiles:=True

'// Close merged file
ActiveDocument.Close

' Stop
'// On Error Resume Next
'// MY PROBLEME
'''Kill txtdotfullpath

Set source = Nothing

End If

'// Next
MyFile = Dir
Loop

'// Close "template" doc without saving
'1. line ??
SendKeys "{ESC}", True
SendKeys "%{F4}"
SendKeys "%N"

Set source = Nothing

Application.ScreenUpdating = True

End Sub


--
Best regards from
Joergen Bondesen