Replace .txt file
 

Hi everyone,
My mission (should I decide to accept it) today is to create a .txt output
file (must be a .txt file; .prn won't do) from a spreadsheet with exact
column widths that then goes into our Lawson system. After looking around a
bit, I found some great code by John McGimpsey that does exactly what I want,
except I need to delete the ckhlthin.txt file before the output gets created
again and I need the new .txt file. Any ideas or should I look into another
way of deleting the file? TIA and a special thanks to John McGimpsey for
being so smart!

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(10, 4, 3, 10, 11, 2, 11, 2, 9, 30, 2, 2, 2, 12,
1, 12, 1, 2, 3, 6, 2, 5, 3, 2, 2, 2, 2, 2, 2, 2, 2, 12)
nFileNum = FreeFile
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

End Sub

Replace .txt file
 

You can delete a file using the Kill command. The argument of the kill
command is the full file and path name.
--
HTH...

Jim Thomlinson


"cottage6" wrote:

Hi everyone,
My mission (should I decide to accept it) today is to create a .txt output
file (must be a .txt file; .prn won't do) from a spreadsheet with exact
column widths that then goes into our Lawson system. After looking around a
bit, I found some great code by John McGimpsey that does exactly what I want,
except I need to delete the ckhlthin.txt file before the output gets created
again and I need the new .txt file. Any ideas or should I look into another
way of deleting the file? TIA and a special thanks to John McGimpsey for
being so smart!

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(10, 4, 3, 10, 11, 2, 11, 2, 9, 30, 2, 2, 2, 12,
1, 12, 1, 2, 3, 6, 2, 5, 3, 2, 2, 2, 2, 2, 2, 2, 2, 12)
nFileNum = FreeFile
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

End Sub

Replace .txt file
 

On Error Resume Next
Kill "S:\Common\Eva\ckhlthin.txt"
On Error goto 0
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
Hi everyone,
My mission (should I decide to accept it) today is to create a .txt output
file (must be a .txt file; .prn won't do) from a spreadsheet with exact
column widths that then goes into our Lawson system. After looking around
a
bit, I found some great code by John McGimpsey that does exactly what I
want,
except I need to delete the ckhlthin.txt file before the output gets
created
again and I need the new .txt file. Any ideas or should I look into
another
way of deleting the file? TIA and a special thanks to John McGimpsey for
being so smart!

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field 1
to N
vFieldArray = Array(10, 4, 3, 10, 11, 2, 11, 2, 9, 30, 2, 2, 2,
12,
1, 12, 1, 2, 3, 6, 2, 5, 3, 2, 2, 2, 2, 2, 2, 2, 2, 12)
nFileNum = FreeFile
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

End Sub

Replace .txt file
 

Thanks to both Tom and Jim; exactly what I needed.

"Tom Ogilvy" wrote:

On Error Resume Next
Kill "S:\Common\Eva\ckhlthin.txt"
On Error goto 0
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
Hi everyone,
My mission (should I decide to accept it) today is to create a .txt output
file (must be a .txt file; .prn won't do) from a spreadsheet with exact
column widths that then goes into our Lawson system. After looking around
a
bit, I found some great code by John McGimpsey that does exactly what I
want,
except I need to delete the ckhlthin.txt file before the output gets
created
again and I need the new .txt file. Any ideas or should I look into
another
way of deleting the file? TIA and a special thanks to John McGimpsey for
being so smart!

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field 1
to N
vFieldArray = Array(10, 4, 3, 10, 11, 2, 11, 2, 9, 30, 2, 2, 2,
12,
1, 12, 1, 2, 3, 6, 2, 5, 3, 2, 2, 2, 2, 2, 2, 2, 2, 12)
nFileNum = FreeFile
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

End Sub

Replace .txt file
 

Thanks to both Tom and Jim for the response; just what I needed.

"Tom Ogilvy" wrote:

On Error Resume Next
Kill "S:\Common\Eva\ckhlthin.txt"
On Error goto 0
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
Hi everyone,
My mission (should I decide to accept it) today is to create a .txt output
file (must be a .txt file; .prn won't do) from a spreadsheet with exact
column widths that then goes into our Lawson system. After looking around
a
bit, I found some great code by John McGimpsey that does exactly what I
want,
except I need to delete the ckhlthin.txt file before the output gets
created
again and I need the new .txt file. Any ideas or should I look into
another
way of deleting the file? TIA and a special thanks to John McGimpsey for
being so smart!

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field 1
to N
vFieldArray = Array(10, 4, 3, 10, 11, 2, 11, 2, 9, 30, 2, 2, 2,
12,
1, 12, 1, 2, 3, 6, 2, 5, 3, 2, 2, 2, 2, 2, 2, 2, 2, 12)
nFileNum = FreeFile
Open "S:\Common\Eva\ckhlthin.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

End Sub