Get statement give Bad Record Length error

[sharlfish]

I have a macro that has been working for about 3 years, but I am now occasionally getting a bad record length error. The process starts in a Visual Basic application that sends information to a text file that is opened using the Open statement in VBA and then reads the data records. The data type is defined in the Declarations area of the procedure as follows:

Private Type ChgOrd
ChgOrderNo As String * 3
CostCode As String * 6
Phs As String * 5
Phase As String * 3
ChgOrderType As String * 6
CurContractAmt As Double
ChgOrderAmt As Double
DaysExtend As String * 2
CostType As String * 1
CENum As String * 5
CEDesc As String * 50
ContractNum As String * 11
PCCType As Long
PCC As Boolean
FilePath As String * 100
OwnerRefNo As String * 6
SubPropNo As String * 6
End Type
Public COFile As ChgOrd


Later I use this statement to open the file, read the records and place the information on the document being created from the template. The problem is that when the code comes to the first Get statement, it sometimes tells me that it is a bad record length even though the record length is the same as the defined variable.



Dim intCount As Integer
Dim Count As Integer
intFN = FreeFile
strFile = Dir("c:\Temp\" + strCOFile)
intCount = 1
totCOAmt = 0
If strFile = strCOFile Then
strFile = "c:\Temp\" + strCOFile
Open strFile For Random As #intFN Len = Len(COFile)
Get #intFN, intCount, COFile
'Check to see if the document was created before and open the old document to update if it was.
Dim OldDocument As String
Dim Path As String
CurContractAmt = Trim(COFile.CurContractAmt)
tmpContractNo = Trim(COFile.ContractNum)
tmpCONo = Trim(COFile.ChgOrderNo)
Path = "o:\contracts\" + tmpJobNo + "\"
OldDocument = tmpContractNo + "CO" + tmpCONo
With Application.FileSearch
.FileName = OldDocument
.LookIn = Path
.Execute
found = .FoundFiles.Count
If found = 1 Then
Documents.Open FileName:=Path + OldDocument
OldDoc = ActiveDocument.Name
Documents(CurrentDocument).Activate
ActiveDocument.Close wdDoNotSaveChanges
Documents(OldDoc).Activate
CurrentDocument = ActiveDocument.Name
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=&quot;co&quot;
End If
' ActiveDocument.Unprotect Password:=&quot;co&quot;
tmpOldCOAmt = ActiveDocument.Variables(&quot;varChangeOrderValue&quot
With Selection
'Go to the CE bookmark and delete all the previously inserted change order info
.GoTo what:=wdGoToBookmark, Name:=&quot;CE&quot;
.Find.ClearFormatting
.MoveUp Unit:=wdLine, Count:=1
.SelectRow
.Copy
.Tables(1).Select
.Delete Unit:=wdCharacter, Count:=1
.Paste
.SelectRow
.Rows.Delete
'Go to the Phase bookmark and delete all the previously inserted phase info
.GoTo what:=wdGoToBookmark, Name:=&quot;Phase&quot;
.Find.ClearFormatting
.MoveUp Unit:=wdLine, Count:=1
.SelectRow
.Copy
.Tables(1).Select
.Delete Unit:=wdCharacter, Count:=1
.Paste
.SelectRow
.Rows.Delete
End With
End If
End With

With ActiveDocument
.Variables(&quot;varTradeItem&quot = IIf(Len(Mid(COFile.ContractNum, 6, 4)) = 0, &quot; &quot;, Mid(COFile.ContractNum, 6, 4))
.Variables(&quot;varCostCode&quot = IIf(Len(Trim(COFile.CostCode)) = 0, &quot; &quot;, Trim(COFile.CostCode))
' .Variables(&quot;varContractNo&quot = IIf(Len(Trim(COFile.ContractNum)) = 0, &quot; &quot;, Trim(COFile.ContractNum))
.Variables(&quot;varCONo&quot = IIf(Len(Trim(COFile.ChgOrderNo)) = 0, &quot; &quot;, Trim(COFile.ChgOrderNo))
.Variables(&quot;varExtension&quot = IIf(Len(Trim(COFile.DaysExtend)) = 0, &quot; &quot;, Asc(COFile.DaysExtend))
.Variables(&quot;varOldContractValue&quot = IIf(Len(Trim(COFile.CurContractAmt)) = 0, &quot; &quot;, Trim(COFile.CurContractAmt))
End With
tmpPCC = COFile.PCC
Selection.GoTo what:=wdGoToBookmark, Name:=&quot;CE&quot;
While Not EOF(intFN)
Selection.TypeText Text:=UCase(Trim(COFile.Phase))
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Trim(COFile.CENum)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Trim(COFile.OwnerRefNo)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Trim(COFile.SubPropNo)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Trim(COFile.CEDesc)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Trim(COFile.ChgOrderType)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Format(Trim(COFile.ChgOrderAmt), &quot;$#,##0.00&quot
Selection.MoveRight Unit:=wdCell
If COFile.Phase <> tmpPhase Then
tmpPhase = COFile.Phase
Count = Count + 1
End If
If Count > 1 Then
tmpPhase = &quot;MULTI&quot;
Else
tmpPhase = COFile.Phase
End If
totCOAmt = totCOAmt + Trim(COFile.ChgOrderAmt)
intCount = intCount + 1
Get #intFN, intCount, COFile
Wend
Close #intFN
Else
MsgBox &quot;Error #2, Cannot find path to original contract. Try re-running the original contract &quot; _
+ &quot;and rerunning the Change Order&quot;, vbOKOnly + vbExclamation, &quot;Error Message&quot;
End
End If

 

[RobBroekhuis]

To troubleshoot, you'll need to be able to reproduce the problem - do you have a particular file for which this ALWAYS occurs?
Rob

 

[sharlfish]

The error happens when I create a document is created using the AutoNew() sub routine in a word template. I have had the same problem happen with some other templates I use and have overcome it by adding 2 bytes to the len statement to accomodate the buffer that windows creates. But the text files being opened for these templates alway contain only 1 record where the one I continue to have problems with often contain multiple records and if I add the 2 bytes to the len statement, it sees the first record only and doesn't run through the records sequentially at the While Not EOF statement.

 

[RobBroekhuis]

Do you have a concrete example on hand of a file that causes you problems? Is the file length of that file consistent with the record length and the number of records?
Rob

 

[HughLerwill]

sharlfish

Its a long time ago but I wonder if you found a solution...
I seem to have similar problems as described in my thread707-993511 , you had them in Word VBA I'm getting them in Excel.

I make a few edits to the code and it stops working, make a few more and it works again.

regards Hugh