Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file replace | Excel Discussion (Misc queries) | |||
If I accidently replace a saved file can I retrieve the old file? | Excel Discussion (Misc queries) | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
the file already exists - do you want to replace the existing file? | Excel Programming | |||
Replace File | Excel Programming |