J
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
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