Jack58
08-11-2004, 10:08 AM
I have a complex Access 2000 database that basically takes information from text files and converts the information into a Microsoft Word document.
The problem is this, in my Module I have the code written to format the document in Microsoft Word. The computer at work only has Microsoft Word Object Library 9.0. The database will not format the document as I need it to, however when I run the database with Microsoft Word Object Library 10, I get the results I am looking for.
I have tryed just about everything to make this work on the computer with the 9.0 Object Library, with no luck.
The Database is larger that what I am allowed to post on this site, so if anyone could help me, I would be more than happy to send a copy of the database to them to review.
Sure hope someone can help me with this complex problem (the boss is driving me nuts!!).
Thanks in advance.
Jack J.
I have attached the code of the database below in hoping that someone might have a better idea of what I am attempting to do here,
Option Compare Database
Option Explicit
' Define a couple of constants that define the location of the files to work with
Public Const strDivs As String = "X:\Purchasing\Unfilled Report\DivSum.txt"
Public Const strReport As String = "X:\Purchasing\Unfilled Report\Report.txt"
'Public Const strDivs As String = "C:\Divisions\DivSum.txt"
'Public Const strReport As String = "C:\Divisions\Report.txt"
Public Const strPrint As String = "C:\Divisions\PrintOut.txt"
Public Const strTemp As String = "C:\Divisions\temp.txt"
Public Const strRootPath As String = "C:\Divisions\"
Public Sub CheckDivisions(dblDivPerMark As Double, dblBuyPerMark As Double)
' Open the Progress form
DoCmd.OpenForm "frmProgressBar"
' Define our variables
Dim hDivs As Long
Dim hReport As Long
Dim hPrint As Long
Dim hTemp As Long
Dim strLine As String
Dim i As Integer
Dim j As Integer
Dim strDivision As String
Dim strBuyer As String
Dim dblPercent As Double
Dim strFile As String
Dim objWord As Word.Application
Dim Test As String
' First we will open the Reports.txt file and read through it. We will create some folders and files
' based on this report in order to speed up later checking
hReport = FreeFile
Open strReport For Input Access Read Shared As hReport
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Clear all the temp directories
For i = 1 To 99
' If the directory exists, loop through and delete all files
If Dir(strRootPath & CStr(Format(i, "00")), vbDirectory) = CStr(Format(i, "00")) Then
strFile = Dir(strRootPath & CStr(Format(i, "00")) & "\" & "*.*")
Do Until strFile = ""
Kill strRootPath & CStr(Format(i, "00")) & "\" & strFile
strFile = Dir
Loop
' Delete directory
RmDir (strRootPath & CStr(Format(i, "00")))
End If
Next i
' Loop through the Report.txt file
Do Until InStr(1, strLine, "END OF REPORT") > 0
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Delete the temp file if it exists
If Dir(strTemp) = "temp.txt" Then Kill (strTemp)
' Open the temp file for storage
hTemp = FreeFile
Open strTemp For Output Access Write As hTemp
' Read and capture the first 3 lines which contain the header info
j = 0
For i = 0 To 2
If Left(strLine, 7) = "1REPORT" Then
If j <> 0 Then Line Input #hReport, strLine
Else
Line Input #hReport, strLine
End If
Print #hTemp, strLine
j = j + 1
Next i
' Capture the division and the buyer numbers
strDivision = Mid(strLine, 14, 2)
strBuyer = Mid(strLine, 101, 2)
' Loop through the pages until the end of the buyer
Do Until InStr(1, strLine, "%LIN SHIP ADJUSTED") > 0 Or InStr(1, strLine, "END OF REPORT") > 0
Line Input #hReport, strLine
Print #hTemp, strLine
Loop
' Capture the value of the line ship adjusted
If InStr(1, strLine, "END OF REPORT") = 0 Then
dblPercent = CDbl(Mid(strLine, 58, 6))
Else
dblPercent = 100
End If
' Close the temp file
Close hTemp
' If line ship adjusted is under limit then save file, otherwise ignore it
If dblPercent < dblBuyPerMark Then
' Check for the directory and make it
If Dir(strRootPath & strDivision, vbDirectory) <> strDivision Then MkDir strRootPath & strDivision
' Copy the temp file to the directory
FileCopy strTemp, strRootPath & strDivision & "\" & strBuyer & "-" & CStr(dblPercent) & ".txt"
End If
' Skip the extra line
If InStr(1, strLine, "END OF REPORT") = 0 Then Line Input #hReport, strLine
Loop
' Close the Report.txt file
Close hReport
' Check to see if the PrintOut.txt file exists and delete it
If Dir(strPrint) = "PrintOut.txt" Then Call Kill(strPrint)
' Open the DivSum.txt and the PrintOut.txt files
hDivs = FreeFile
Open strDivs For Input Access Read Shared As hDivs
hPrint = FreeFile
Open strPrint For Output Access Write As hPrint
' Loop through the divsum.txt file and find divisions under limit
Do Until EOF(hDivs)
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Read a line
Line Input #hDivs, strLine
' If the line begins with a 0, capture the division number and prepare to check the percentages
If Left(strLine, 1) = "0" Then
' Capture the division number
strDivision = Mid(strLine, 13, 2)
' Skip 5 lines to get to the percentages
For i = 1 To 5
Line Input #hDivs, strLine
Next i
' Capture the value of the line ship adjusted
dblPercent = CDbl(Mid(strLine, 75, 6))
' If line ship adjusted is under limit then copy all files in the division directory to the
' PrintOut.txt file
If dblPercent < dblDivPerMark Then
' Open each file in the division dirctory and copy it to the PrintOut.txt file
strFile = Dir(strRootPath & strDivision & "\" & "*.*")
Do Until strFile = ""
hTemp = FreeFile
' Open the file
Open strRootPath & strDivision & "\" & strFile For Input Access Read Shared As hTemp
' Loop through the file and print each line to the PrintOut.doc file
Do Until EOF(hTemp)
Line Input #hTemp, strLine
Print #hPrint, strLine
Loop
Close hTemp
strFile = Dir
Loop
End If
End If
Loop
' Close any open files
Close hPrint
Close hDivs
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Check for and delete any PrintOut.doc file remaining
If Dir(strRootPath & "PrintOut.doc") = "PrintOut.doc" Then Call Kill(strRootPath & "PrintOut.doc")
' Open the PrintOut.doc file in Word, add page breaks, and save the file as PrintOut.doc"
Set objWord = New Word.Application
objWord.Documents.Open strPrint
objWord.Selection.Find.Execute "1REPORT", , , , , , , , , "^m", wdReplaceAll
With objWord.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.2)
.RightMargin = InchesToPoints(0.2)
End With
objWord.Selection.WholeStory
objWord.Selection.Font.Name = "Courier"
objWord.Selection.Font.Size = 9#
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = "*"
Forms![frmProgressBar].Refresh
DoEvents
j = 0
For i = 1 To 10000
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.MoveRight unit:=wdCharacter, Count:=1, Extend:=wdExtend
If objWord.Selection = "0" Then
objWord.Selection.Delete
objWord.Selection.TypeText Chr(32)
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.TypeText Chr(13)
objWord.Selection.MoveDown unit:=wdLine, Count:=1
j = 0
GoTo NextI
End If
If i <> "0" Then
objWord.Selection.MoveDown unit:=wdLine, Count:=1
objWord.Selection.HomeKey unit:=wdLine
j = j + 1
End If
If j = 100 Then Exit For
NextI:
If i Mod 50 = 0 Then
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
End If
Next i
objWord.Selection.WholeStory
With objWord.Selection.ParagraphFormat
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceExactly
.LineSpacing = 8
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
objWord.ActiveDocument.SaveAs strRootPath & "PrintOut.doc", wdFormatDocument
End With
objWord.Selection.HomeKey unit:=wdLine
Set objWord = Nothing
' Preview the PrintOut.doc file
'strLine = MsgBox("Do you wish to preview the report?", vbYesNo, "Preview?")
' If strLine = vbYes Then strFile = Shell("C:\Program Files\Microsoft Office\Office\winword.exe " & strRootPath & "Printout.Doc")
' Close Progress Bar form
DoCmd.Close acForm, "frmProgressBar"
End Sub
The problem is this, in my Module I have the code written to format the document in Microsoft Word. The computer at work only has Microsoft Word Object Library 9.0. The database will not format the document as I need it to, however when I run the database with Microsoft Word Object Library 10, I get the results I am looking for.
I have tryed just about everything to make this work on the computer with the 9.0 Object Library, with no luck.
The Database is larger that what I am allowed to post on this site, so if anyone could help me, I would be more than happy to send a copy of the database to them to review.
Sure hope someone can help me with this complex problem (the boss is driving me nuts!!).
Thanks in advance.
Jack J.
I have attached the code of the database below in hoping that someone might have a better idea of what I am attempting to do here,
Option Compare Database
Option Explicit
' Define a couple of constants that define the location of the files to work with
Public Const strDivs As String = "X:\Purchasing\Unfilled Report\DivSum.txt"
Public Const strReport As String = "X:\Purchasing\Unfilled Report\Report.txt"
'Public Const strDivs As String = "C:\Divisions\DivSum.txt"
'Public Const strReport As String = "C:\Divisions\Report.txt"
Public Const strPrint As String = "C:\Divisions\PrintOut.txt"
Public Const strTemp As String = "C:\Divisions\temp.txt"
Public Const strRootPath As String = "C:\Divisions\"
Public Sub CheckDivisions(dblDivPerMark As Double, dblBuyPerMark As Double)
' Open the Progress form
DoCmd.OpenForm "frmProgressBar"
' Define our variables
Dim hDivs As Long
Dim hReport As Long
Dim hPrint As Long
Dim hTemp As Long
Dim strLine As String
Dim i As Integer
Dim j As Integer
Dim strDivision As String
Dim strBuyer As String
Dim dblPercent As Double
Dim strFile As String
Dim objWord As Word.Application
Dim Test As String
' First we will open the Reports.txt file and read through it. We will create some folders and files
' based on this report in order to speed up later checking
hReport = FreeFile
Open strReport For Input Access Read Shared As hReport
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Clear all the temp directories
For i = 1 To 99
' If the directory exists, loop through and delete all files
If Dir(strRootPath & CStr(Format(i, "00")), vbDirectory) = CStr(Format(i, "00")) Then
strFile = Dir(strRootPath & CStr(Format(i, "00")) & "\" & "*.*")
Do Until strFile = ""
Kill strRootPath & CStr(Format(i, "00")) & "\" & strFile
strFile = Dir
Loop
' Delete directory
RmDir (strRootPath & CStr(Format(i, "00")))
End If
Next i
' Loop through the Report.txt file
Do Until InStr(1, strLine, "END OF REPORT") > 0
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Delete the temp file if it exists
If Dir(strTemp) = "temp.txt" Then Kill (strTemp)
' Open the temp file for storage
hTemp = FreeFile
Open strTemp For Output Access Write As hTemp
' Read and capture the first 3 lines which contain the header info
j = 0
For i = 0 To 2
If Left(strLine, 7) = "1REPORT" Then
If j <> 0 Then Line Input #hReport, strLine
Else
Line Input #hReport, strLine
End If
Print #hTemp, strLine
j = j + 1
Next i
' Capture the division and the buyer numbers
strDivision = Mid(strLine, 14, 2)
strBuyer = Mid(strLine, 101, 2)
' Loop through the pages until the end of the buyer
Do Until InStr(1, strLine, "%LIN SHIP ADJUSTED") > 0 Or InStr(1, strLine, "END OF REPORT") > 0
Line Input #hReport, strLine
Print #hTemp, strLine
Loop
' Capture the value of the line ship adjusted
If InStr(1, strLine, "END OF REPORT") = 0 Then
dblPercent = CDbl(Mid(strLine, 58, 6))
Else
dblPercent = 100
End If
' Close the temp file
Close hTemp
' If line ship adjusted is under limit then save file, otherwise ignore it
If dblPercent < dblBuyPerMark Then
' Check for the directory and make it
If Dir(strRootPath & strDivision, vbDirectory) <> strDivision Then MkDir strRootPath & strDivision
' Copy the temp file to the directory
FileCopy strTemp, strRootPath & strDivision & "\" & strBuyer & "-" & CStr(dblPercent) & ".txt"
End If
' Skip the extra line
If InStr(1, strLine, "END OF REPORT") = 0 Then Line Input #hReport, strLine
Loop
' Close the Report.txt file
Close hReport
' Check to see if the PrintOut.txt file exists and delete it
If Dir(strPrint) = "PrintOut.txt" Then Call Kill(strPrint)
' Open the DivSum.txt and the PrintOut.txt files
hDivs = FreeFile
Open strDivs For Input Access Read Shared As hDivs
hPrint = FreeFile
Open strPrint For Output Access Write As hPrint
' Loop through the divsum.txt file and find divisions under limit
Do Until EOF(hDivs)
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Read a line
Line Input #hDivs, strLine
' If the line begins with a 0, capture the division number and prepare to check the percentages
If Left(strLine, 1) = "0" Then
' Capture the division number
strDivision = Mid(strLine, 13, 2)
' Skip 5 lines to get to the percentages
For i = 1 To 5
Line Input #hDivs, strLine
Next i
' Capture the value of the line ship adjusted
dblPercent = CDbl(Mid(strLine, 75, 6))
' If line ship adjusted is under limit then copy all files in the division directory to the
' PrintOut.txt file
If dblPercent < dblDivPerMark Then
' Open each file in the division dirctory and copy it to the PrintOut.txt file
strFile = Dir(strRootPath & strDivision & "\" & "*.*")
Do Until strFile = ""
hTemp = FreeFile
' Open the file
Open strRootPath & strDivision & "\" & strFile For Input Access Read Shared As hTemp
' Loop through the file and print each line to the PrintOut.doc file
Do Until EOF(hTemp)
Line Input #hTemp, strLine
Print #hPrint, strLine
Loop
Close hTemp
strFile = Dir
Loop
End If
End If
Loop
' Close any open files
Close hPrint
Close hDivs
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
' Check for and delete any PrintOut.doc file remaining
If Dir(strRootPath & "PrintOut.doc") = "PrintOut.doc" Then Call Kill(strRootPath & "PrintOut.doc")
' Open the PrintOut.doc file in Word, add page breaks, and save the file as PrintOut.doc"
Set objWord = New Word.Application
objWord.Documents.Open strPrint
objWord.Selection.Find.Execute "1REPORT", , , , , , , , , "^m", wdReplaceAll
With objWord.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.2)
.RightMargin = InchesToPoints(0.2)
End With
objWord.Selection.WholeStory
objWord.Selection.Font.Name = "Courier"
objWord.Selection.Font.Size = 9#
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = "*"
Forms![frmProgressBar].Refresh
DoEvents
j = 0
For i = 1 To 10000
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.MoveRight unit:=wdCharacter, Count:=1, Extend:=wdExtend
If objWord.Selection = "0" Then
objWord.Selection.Delete
objWord.Selection.TypeText Chr(32)
objWord.Selection.HomeKey unit:=wdLine
objWord.Selection.TypeText Chr(13)
objWord.Selection.MoveDown unit:=wdLine, Count:=1
j = 0
GoTo NextI
End If
If i <> "0" Then
objWord.Selection.MoveDown unit:=wdLine, Count:=1
objWord.Selection.HomeKey unit:=wdLine
j = j + 1
End If
If j = 100 Then Exit For
NextI:
If i Mod 50 = 0 Then
' Add indicator to Progress Bar form
Forms![frmProgressBar]![ProgressBar] = Forms![frmProgressBar]![ProgressBar] & "*"
Forms![frmProgressBar].Refresh
DoEvents
End If
Next i
objWord.Selection.WholeStory
With objWord.Selection.ParagraphFormat
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceExactly
.LineSpacing = 8
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
objWord.ActiveDocument.SaveAs strRootPath & "PrintOut.doc", wdFormatDocument
End With
objWord.Selection.HomeKey unit:=wdLine
Set objWord = Nothing
' Preview the PrintOut.doc file
'strLine = MsgBox("Do you wish to preview the report?", vbYesNo, "Preview?")
' If strLine = vbYes Then strFile = Shell("C:\Program Files\Microsoft Office\Office\winword.exe " & strRootPath & "Printout.Doc")
' Close Progress Bar form
DoCmd.Close acForm, "frmProgressBar"
End Sub