PDA

View Full Version : Solved: File converts randomly as Japanese


Chief Gopher
03-23-2010, 09:00 AM
I have a file which runs from a macro. The code includes ConfirmConversion=False to suppress the file conversion box, but I need it to specify that the file conversion should be the windows default.

For some strange reason, I am unable to ascertain why, it takes 1 in every 1000 documents and converts them as Japanese (Shift JIS) rather than the required Western European (Windows). This results in all the £ in the document (an invoice) to show as a backwards L

Is there a code in vba where I can still suppress the file conversion box, but select the windows default.

The original sub is

Sub sinv()
WordBasic.AppMinimize
Selection.InsertFile FileName:=Environ("temp") & "\sinv.txt", Range:="", ConfirmConversions:= _
False, Link:=False, Attachment:=False
WordBasic.EditSelectAll
WordBasic.Style "sinvoice"
Call check_ams
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "#"
.Replacement.Text = "£"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
WordBasic.EndofDocument
WordBasic.EditClear -1
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=False, PrintToFile:=False
Application.Quit (wdDoNotSaveChanges)
End Sub

this sub sinv calls a multiple macros (I have only shown one) to show which item in the extract file corresponds to which field on the document ie....

Sub check_ams()
If Selection.Find.Execute("@@@@@AMS") = True Then
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.HeaderFooter.Shapes("Text Box 18").Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "ADD1"
.Replacement.Text = "Corporation"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD2"
.Replacement.Text = "Cornwall"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD3"
.Replacement.Text = "Cornwall. "
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD4"
.Replacement.Text = "Tel : () Fax : ()"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD5"
.Replacement.Text = "VAT Number : nn nnnn nnn"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "ADD6"
.Replacement.Text = "Payment Details: You can pay by debit/credit card "
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD7"
.Replacement.Text = "Alternatively send a cheque made payable to ...."
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "@@@@@AMS"
.Replacement.Text = ""
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End If
End Sub
SamT
03-26-2010, 07:37 AM
Sub sinv()
WordBasic.AppMinimize
Selection.InsertFile FileName:=Environ("temp") & "\sinv.txt", Range:="", ConfirmConversions:= _
False, Link:=False, Attachment:=False
WordBasic.EditSelectAll
WordBasic.Style "sinvoice"
'
Call check_ams
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "#"
.Replacement.Text = "£"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
WordBasic.EndofDocument
WordBasic.EditClear -1
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=False, PrintToFile:=False
Application.Quit (wdDoNotSaveChanges)
End Sub


this sub sinv calls a multiple macros (I have only shown one) to show which item in the extract file corresponds to which field on the document ie....

Sub check_ams()
'
If Selection.Find.Execute("@@@@@AMS") = True Then
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
'
Selection.HeaderFooter.Shapes("Text Box 18").Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "ADD1"
.Replacement.Text = "Corporation"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD2"
.Replacement.Text = "Cornwall"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD3"
.Replacement.Text = "Cornwall. "
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD4"
.Replacement.Text = "Tel : () Fax : ()"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD5"
.Replacement.Text = "VAT Number : nn nnnn nnn"
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "ADD6"
.Replacement.Text = "Payment Details: You can pay by debit/credit card "
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ADD7"
.Replacement.Text = "Alternatively send a cheque made payable to ...."
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "@@@@@AMS"
.Replacement.Text = ""
.Forward = False
.Wrap = wdFindContinue
.Format = False
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'
Selection.Find.Execute Replace:=wdReplaceAll
End If
End Sub
Chief Gopher
03-26-2010, 09:22 AM
:think: Didn't make any difference, still all Japanese to me
Paul_Hossler
03-27-2010, 05:32 PM
Can you sanitize and post one of the troublesome documents?

Paul
Chief Gopher
03-29-2010, 02:16 AM
: pray2: This is the file contents which is a .txt format




@@@@@AMS
Somebody College
A N Other


Dorchester
Dorset

DT7 2PZ


SBSI05999
15/02/10
JOHNSTONE

99992


Goods Training 9,999.00
Delivery by Paul Brush

10/11/24/25 February 2010

£200 per day - £2000.00

Travel 4 x 140 miles - £216.00










0.00 9,999.00 0.00 9,999.00
----- -----.-- -----.--
0.00
9,999.00



99992 SBSI05999 9,999.00
Paul_Hossler
03-29-2010, 04:16 PM
Opens and converts OK for me using Word2007. I see the Pound L's. Windows Default was OK, and if I said 'Other Encoding' it picked Western European as default. Both OK. MS-DOS encoding has problems.

I wonder it it's a Word or Windows resource problem? If you start Word fresh, does the same document fail each time, or is it the 100th document in a long series of conversions? Same question for Windows

I've had instances where Fonts starts mis-behaving when I had not restarted Windows for a long while (thank you Bill Gates) or have been processing a large number of documents in a single Word session

Paul
Chief Gopher
03-30-2010, 02:35 AM
If I open the txt file in Word it uses the default. If I open it using the template it converts as Japanese.

It doesn't matter how many documents the conversion is random, but it will always convert as Japanese using the template

All applications are closed at end of day and restarted the next day so it can't be that.

The template is used by multiple users on multiple sites so needs the conversion box suppressed, but I need to be able to code into it the conversion should be western european.
Chief Gopher
03-30-2010, 03:45 AM
I have just managed to resolve the issue by changing the sinv sub

removing
Selection.InsertFile FileName:=Environ("temp") & "\sinv.txt", Range:="", ConfirmConversions:= _
False, Link:=False, Attachment:=False

and replacing it with
Documents.Open FileName:=Environ("temp") & "\sinv.txt", _
ConfirmConversions:=False, ReadOnly:=False, _
AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatText, Encoding:=1252

Whilst reading someone else's post I saw they had in their coding
Format:=wdOpenFormatAuto
I searched the internet and found a site that showed formatting other than Auto http://www.contactez.net/support/macros.html
and did a direct copy from there

I've been trying to resolve this for about 6 months so this is a time to celebrate :clap: