Farman
05-30-2014, 03:18 PM
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
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