VBA Code For Appending to the text file

[Farman]

Hi There,

I have this code that creates 2 one is Detail and the other is Header text file from excel, everything works just fine, it also appends it to the existing text file but with the repetitive Header information every time I append to the file, is there a way that I could have just only one header information.

All the help with this is highly appreciated

Below is the Code.

Private Sub CmdCreateFile_Click()
' Code To Generate the Detail File
Const DELIMITER As String = vbTab
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
Dim sSKU As String
Dim Detail As String
Dim Header As String
Dim Path As String
Dim LDate As Date

nFileNum = FreeFile
LDate = Date
sSKU = "GPUpload"
Detail = "_Detail"
Header = "_Header"


Path = ThisWorkbook.Path

sOut = "Item Number" & DELIMITER & "Description" & DELIMITER & "ShortDesc" & DELIMITER & "GenericDesc" & DELIMITER & "VendorID" & DELIMITER & "VendorDesc" & DELIMITER & "VendorItem" & _
DELIMITER & "CurrentCost" & DELIMITER & "Class ID" & DELIMITER & "HTS Code" & DELIMITER & "Planning Code" & DELIMITER & "Consum Code" & DELIMITER & "WMS Code" & _
DELIMITER & "Landed Cost ID" & vbNewLine

Open Path & "\" & sSKU & Detail & ".txt" For Append As #nFileNum
For Each myRecord In Range("A32:A" & _
Range("A" & Rows.Count).End(xlUp).Row)

With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
If sOut <> "" Then
sOut = sOut & DELIMITER & myField.Text
Else
sOut = sOut & myField.Text
End If
Next myField
Print #nFileNum, Mid(sOut, 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum

'Code To Generate the Header File
Path = ThisWorkbook.Path
Open Path & "\" & sSKU & Header & ".txt" For Append As #nFileNum
' Adding Header columns
sOut = "Item Number" & DELIMITER & "SiteID"
Print #nFileNum, Mid(sOut, 1)
sOut = Empty

For Each myRecord In Range("A32:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
If sOut <> "" Then
sOut = DELIMITER & myRecord.Text & DELIMITER & "SDW001" 'sOut
Else
sOut = myRecord.Text & DELIMITER & "SDW001" 'sOut &
End If
Print #nFileNum, Mid(sOut, 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

[westconn1]

try like

Open Path & "\" & sSKU & Detail & ".txt" For Append As #nFileNum
if lof(nfilenum) = 0 then print #nfilenum, sOut
sout = vbnullstring

[Farman]

Thanks you so much for the line of Code, that really worked

Thanks,

Farman

try like

Open Path & "\" & sSKU & Detail & ".txt" For Append As #nFileNum
if lof(nfilenum) = 0 then print #nfilenum, sOut
sout = vbnullstring

[Farman]

Also please let me know what is the best method to check if the file exists, if not exist then create it and then append the records to it, all the help highly appreciated.

Thanks,

Farman

[westconn1]

opening a file for append will create the file if it does not exist, so no need to do anything extra in this case
the code as i posted will put headers if it is a new file (lof = 0)

to check if a file exists, you can use DIR

if len(dir(fullpath\filename)) > 0 then   ' file exists

you can just check if the return from dir is "", but checking if the len is not 0 is more efficient

[Farman]

Again, Thanks a lot for all the help, appreciate it.

Thanks,