Word 97 Won't Allow File Replacement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Ciccone - 28 Feb 2008 18:24 GMT

Though this has worked well for years, it isn't anymore.

- I export an ASCII delimited text file from another database program;

- DDE commands are sent to Word to run a macro. The macro opens the exported
file and performs mail merge ;

- Suddenly newly exported text files from the database are not replacing the
old ones (by the same name);

- I have to shut down Word and restart. Then it's fine until the first merge;

- I'm not getting error messages.

If there's no way ensure that Word automatically 'releases' the file, can I
build something into the macro?

Thank you for any help.

Reply to this Message

John Ciccone - 28 Feb 2008 18:43 GMT

A new variation of this problem has manifested itself... the file exported
from the database DOES overwrite the old one by the same name.

However, Word keeps doing the Mail Merge using the older file now deleted.
If I quit Word and restart, it performs the Mail Merge properly.

> Though this has worked well for years, it isn't anymore.
>
[quoted text clipped - 14 lines]
>
> Thank you for any help.

Reply to this Message

Jean-Guy Marcil - 28 Feb 2008 18:53 GMT

> A new variation of this problem has manifested itself... the file exported
> from the database DOES overwrite the old one by the same name.
[quoted text clipped - 20 lines]
> >
> > Thank you for any help.

Looks like it is time for a Debugging session... There seems to be something
off in your code... and without seeing it, we cannot help!

Reply to this Message

John Ciccone - 28 Feb 2008 23:55 GMT

Thank you, Guy.

There's not much to the code... once the database app exports to a delimited
file, it sends a command to Word via DDE to run a Macro.

This macro performs a mail merge using the aforementioned exported file as
the Data source. Essentially as follows:

================

Dim FirstWin$
Starter:
On Error GoTo Trouble

PastDlg:
WordBasic.FileOpen Name:="c:\windows\winword\merge\Bank Deposit Form.doc",
ReadOnly:=0, PasswordDoc:="", PasswordDot:=""
WordBasic.ViewDraft 1
FirstWin$ = WordBasic.[WindowName$]() 'get the title of the current
window

WordBasic.WW2_PrintMergeToDoc Suppression:=0

================

It goes through the motions and does the mail merge to new document, but
after it's done it once, it keeps using the same data somehow... even though
the data doc has been overwritten with newly exported files.

If I quit Word, and re-run it, it correctly uses the most recently exported
file.

> > A new variation of this problem has manifested itself... the file exported
> > from the database DOES overwrite the old one by the same name.
[quoted text clipped - 23 lines]
> Looks like it is time for a Debugging session... There seems to be something
> off in your code... and without seeing it, we cannot help!

Reply to this Message

Jean-Guy Marcil - 03 Mar 2008 14:28 GMT

> Thank you, Guy.
>
[quoted text clipped - 27 lines]
> If I quit Word, and re-run it, it correctly uses the most recently exported
> file.

I am not sure I undesrstand all that is going on, but from looking at your
code I notice two things: It is using WordBasic commands which are deprecated
for the most part (We only use some of them becasue they often offer a
shortcut or a way of doing things that the newer object model does not
offer), and I do not see the link between the Access-created file and the
merge operation, other than the line:
FirstWin$ = WordBasic.[WindowName$]()
Relying on "current" windows or documents when automating Word is usually
not a good idea.

I would declare actual objects and made sure that I use those objects in the
code.
Here, I would declare two document objects:
Dim docSource As Document
Dim docMerge As Document
Then I would set the two objects and use them in the code to make sure I
was using the right documents.

Reply to this Message

John Ciccone - 04 Mar 2008 04:29 GMT

Thank you, Jean-Guy.

I'm afraid I don't understand the term "deprecated" in the context of VB
programming, or the last five lines of your note. You can see how little I
know.

But my macro is quite simple in that all it really does is open a mail merge
template file and perform a mail merge output with it. I've done a poor job
of explaining it. Here it is:

1. From a dlg box, you select the type of merge. eg: "Invoice PDF"; "Invoice
Printer"; "Bank Deposit";

2. That determines which mail merge template is opened (because each of the
above selection is also a document name);

3. That document is opened:

WordBasic.FileOpen Name:="c:\windows\winword\merge\" + FilName$,
ReadOnly:=0, PasswordDoc:="", PasswordDot:=""

4. No matter which of the above merge templates is selected, it always uses
the same 'Data File'. Let's call it "Export.txt". This is what my database
exports. Always to the same location so the Word merge template can find it;

5. The macro triggers the mail merge:

WordBasic.WW2_PrintMergeToDoc Suppression:=0

That's all.

The first time it runs, it's fine. The merge output document is perfect. But
then I run it again... each time I begin with the export from the database to
create a new Export.txt. But now the merge output document is the same as the
first merge. IOW, it's not using the newly created Export.txt.

If I quit Word, restart it, then re-run the process it's fine. It uses the
newest Export.txt.

It's as though Word won't let go of Export.txt being used as the Data File,
or the merge template, until I quit the program. (I have noticed file names
beginning with "~". These temp files do not disappear after the macro closes
the relevant document)

The line:

FirstWin$ = WordBasic.[WindowName$]()

isn't terribly important. It's been so long I barely remember it's purpose.
But I believe it was to make sure that the merge template was closed. It
screws up if I try running this macro with the merge template already open.
So I later use:

WordBasic.Activate FirstWin$
WordBasic.FileClose

Best regards,
John

> > Thank you, Guy.
> >
[quoted text clipped - 45 lines]
> Then I would set the two objects and use them in the code to make sure I
> was using the right documents.

Thank you, Jean

Reply to this Message

Jean-Guy Marcil - 05 Mar 2008 20:47 GMT

> Thank you, Jean-Guy.
>
> I'm afraid I don't understand the term "deprecated" in the context of VB

Meaning that it is still compiled if you use it, but not the prefered way of
doing things (not officially supported anymore).

> programming, or the last five lines of your note. You can see how little I
> know.
>
> But my macro is quite simple in that all it really does is open a mail merge
> template file and perform a mail merge output with it. I've done a poor job
> of explaining it. Here it is:

You keep showing us code snippets, we must see the whole thing (you may not
show us the appropriate snippets...).
Also, I get the feeling that there are two separate procedures, is that right?

It looks as though this code was written in a pre-Word 97 version. Would you
consider re-writing it without using wordbasic? This way we could use
objects, which is the strength of Visual Basic or VBA.
This would solve the problem you have. If Word uses the wrong data set
starting with the second document in each run, it means the code is not
letting go, but from the code snippet you have posted, I am afraid I cannot
help. If you post the whole code, I might be able to help some more. I write
"might" becasue I have never written wordbasic code, but I know some of the
people that hang around here have done so.

Reply to this Message

John Ciccone - 10 Mar 2008 20:20 GMT

Thank you, Jean-Guy.

Could've sworn I posted this response... anyway...

> You keep showing us code snippets, we must see the whole thing (you may not
> show us the appropriate snippets...).
> Also, I get the feeling that there are two separate procedures, is that right?

Yes. Two procedures. The second procedure is in another post below. Account
numbers/details are fake:

***********

Public Sub MAIN()
Dim FilName$
Dim FirstWin$
Starter:
On Error GoTo Trouble
WordBasic.BeginDialog 268, 166, "Microsoft Word"
WordBasic.GroupBox 33, 15, 202, 110, "Type of Merge:"
WordBasic.OKButton 41, 132, 88, 21
WordBasic.CancelButton 137, 132, 88, 21
WordBasic.OptionGroup "InvType"
WordBasic.OptionButton 46, 36, 133, 16, "&Letterhead Invoice" '0
WordBasic.OptionButton 46, 56, 132, 16, "&PDF Invoice" '1
WordBasic.OptionButton 46, 76, 132, 16, "&Ringer Invoice" '2
WordBasic.OptionButton 46, 96, 132, 16, "&Bank Deposits" '3
WordBasic.EndDialog
Dim dlg As Object: Set dlg = WordBasic.CurValues.UserDialog
WordBasic.Dialog.UserDialog dlg
If dlg.InvType = 0 Then
FilName$ = "inv_merg.doc"
ElseIf dlg.InvType = 1 Then
FilName$ = "invPDFmerg.doc" ' was invfmerg.doc for VIA FAX
ElseIf dlg.InvType = 2 Then
FilName$ = "Invoice Telus Merge" '
ElseIf dlg.InvType = 3 Then
FilName$ = "Bank Deposit Form.doc" '
Application.Run "Normal.SuperbaseBankDeposits.MAIN"
GoTo EndItAll
End If
' Open the Invoice merge doc and perform merge.
WordBasic.FileOpen Name:="c:\windows\winword\merge\" + FilName$,
ReadOnly:=0, PasswordDoc:="", PasswordDot:=""
FirstWin$ = WordBasic.[WindowName$]() 'get title of current window
WordBasic.WW2_PrintMergeToDoc Suppression:=0 ' do merge
WordBasic.StartOfDocument
WordBasic.Activate FirstWin$
WordBasic.FileClose
Trouble:
If Err.Number = 509 Then 'Error 509: happens when no docs are open
WordBasic.FileNewDefault 'creates a blank doc
Err.Number = 0 'Reset error trap
Else 'If a different error occurs
GoTo EndItAll
End If
EndItAll:
' The following prints Ringer invoice to PDF file:
If FilName$ = "Ringer Invoice" Then
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Invoice #"
.Replacement.Text = "Total Owing"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Copy
ActivePrinter = "Adobe PDF"
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False
WordBasic.StartOfDocument

WordBasic.FileOpen Name:="c:\windows\winword\merge\Invoice Ringer Email"
FirstWin$ = WordBasic.[WindowName$]() 'get the title of the current window
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

WordBasic.Activate FirstWin$
' FileClose
WordBasic.DocClose 2

ActiveDocument.SaveAs FileName:="C:\Documents and Settings\All
Users\Desktop\Invoice.eml", FileFormat _

:=wdFormatText, LockComments:=False, Password:="", AddToRecentFiles:=True _

, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
RetVal = Shell("C:\Program Files\Outlook Express\msimn.exe /eml:C:\Documents
and Settings\All Users\Desktop\Invoice.eml", vbNormalFocus)
WordBasic.DocClose 2
End If
End If
End Sub

Reply to this Message

John Ciccone - 10 Mar 2008 20:27 GMT

<<<< 2nd Procedure part 1 of 3 >>>>

Sub MAIN()
' SuperbaseBankDeposits.MAIN Macro
' Macro created 08-03-04
Dim FirstWin$
Starter:
PastDlg:
WordBasic.FileOpen Name:="c:\windows\winword\merge\Bank Deposit Form.doc",
ReadOnly:=0, PasswordDoc:="", PasswordDot:=""
WordBasic.ViewDraft 1
FirstWin$ = WordBasic.[WindowName$]() 'get the title of the current window
WordBasic.WW2_PrintMergeToDoc Suppression:=0
WordBasic.Activate FirstWin$
WordBasic.DocClose 2
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Or ActiveWindow.ActivePane.View.Type _
= wdMasterView Then
ActiveWindow.ActivePane.View.Type = wdPageView
End If

ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If

WordBasic.ViewFieldCodes 1
WordBasic.WW2_EditFind Find:="\@ " + Chr(34) + "MMMM", WholeWord:=0,
MatchCase:=0, _
Direction:=1, Format:=0
WordBasic.CharRight 1
WordBasic.UpdateFields
WordBasic.ViewFieldCodes 0
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Deposit to Account #:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdWord, Count:=1
Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Copy 'get account # to paste back into header later
WordBasic.StartOfDocument
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Or ActiveWindow.ActivePane.View.Type _
= wdMasterView Then
ActiveWindow.ActivePane.View.Type = wdPageView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

WordBasic.ViewDraft 1
WordBasic.StartOfDocument
While WordBasic.EndOfDocument() <> 0
WordBasic.EditFind Find:="^b", Direction:=0, MatchCase:=0, WholeWord:=0,
PatternMatch:=0, SoundsLike:=0, Format:=0, Wrap:=1, FindAllWordForms:=0
WordBasic.EditClear
Wend
WordBasic.StartOfDocument
While WordBasic.EndOfDocument() <> 0
WordBasic.EditFind Find:="^p", Direction:=0, MatchCase:=0, WholeWord:=0,
PatternMatch:=0, SoundsLike:=0, Format:=0, Wrap:=1, FindAllWordForms:=0
WordBasic.EditClear
Wend
WordBasic.StartOfDocument
WordBasic.TableInsertRow NumRows:=""
WordBasic.TableHeadings
WordBasic.FormatBordersAndShading ApplyTo:=2, Shadow:=0, TopBorder:=2,
LeftBorder:=2, BottomBorder:=2, RightBorder:=2, HorizBorder:=0,
VertBorder:=1, TopColor:=0, LeftColor:=0, BottomColor:=0, RightColor:=0,
HorizColor:=0, VertColor:=0, FromText:="0 pt", Shading:=2, Foreground:=0,
Background:=0, Tab:="1", FineShading:=-1
WordBasic.StartOfLine
WordBasic.Insert "#"
WordBasic.NextCell
WordBasic.Insert "Account Number (Transit):"
Selection.SelectColumn

Reply to this Message

John Ciccone - 10 Mar 2008 20:28 GMT

<<<<< 2nd procedure part 2 of 3 >>>>>>

WordBasic.WW2_EditReplace Find:="Biz Savings: 123-4567 (123)", Replace:="
123-4567 (123)", WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
Selection.SelectColumn
WordBasic.WW2_EditReplace Find:="Biz Savings B: 234-5678 (123)", Replace:="
234-5678 (123)", WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
Selection.SelectColumn
WordBasic.WW2_EditReplace Find:="Biz Cheq: 345-6789 (123)", Replace:="
345-6789 (123)", WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
Selection.SelectColumn
WordBasic.WW2_EditReplace Find:="US Cheq: 456-7890 (123)", Replace:="($USD)
456-7890 (123)", WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
Selection.SelectColumn
WordBasic.WW2_EditReplace Find:="Personal Cheq: 567-8901 (1760)",
Replace:="567-8901 (1234)", WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
Selection.SelectColumn
WordBasic.WW2_EditReplace Find:="ABC Account: 6789-01", Replace:="6789-01",
WholeWord:=0, MatchCase:=0, Format:=0, _
ReplaceAll:=1
WordBasic.NextCell
WordBasic.NextCell
WordBasic.Insert "Invoice/Cheque ID:"
WordBasic.NextCell
WordBasic.Insert "Cheque Amount:"
WordBasic.TableSelectRow
WordBasic.Bold
Selection.HomeKey Unit:=wdStory
Selection.SplitTable
Selection.TypeParagraph
WordBasic.ViewHeader
WordBasic.EditSelectAll
WordBasic.UpdateFields
WordBasic.CloseViewHeaderFooter
WordBasic.ViewDraft 0
' The following just applies formatting (colour, borders, etc.)

WordBasic.ViewPage
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.Find.ClearFormatting
With Selection.Find
.Text = "«BankDepositAcc»"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.Paste 'paste back account # into header
WordBasic.CloseViewHeaderFooter
Selection.EndKey Unit:=wdStory
Selection.InsertRows 1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"= sum(above) \# ""#,##0.00""", PreserveFormatting:=True
Selection.SelectColumn

WordBasic.WW2_EditReplace Find:="$", Replace:="", WholeWord:=0,
MatchCase:=0, Format:=0, _
ReplaceAll:=1

Selection.Find.Execute Replace:=wdReplaceAll
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.MoveLeft Unit:=wdCell
Selection.TypeText Text:="GRAND TOTAL:"
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.SelectRow
Selection.Font.Bold = wdToggle
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.Range.HighlightColorIndex = wdNoHighlight
Selection.SelectRow

Reply to this Message

John Ciccone - 11 Mar 2008 14:08 GMT

This isn't letting me post part 3 of 3.

Reply to this Message