Need help modifying the below code to meet my requirement. Any help would be greatly appreciated. I have 100's of files to convert.
Here is the layout of data in excel. In this example there are only 2 rows of "catalog data" (rows 13&14), however other catalogs will have more
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CIF_I_V3.0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CHARSET:
[/TD]
[TD]UTF-8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]LOADMODE:
[/TD]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CODEFORMAT:
[/TD]
[TD]UNSPSC_V13.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CURRENCY:
[/TD]
[TD]USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]SUPPLIERID_DOMAIN:
[/TD]
[TD]NetworkID
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ITEMCOUNT:
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]TIMESTAMP:
[/TD]
[TD]8/4/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]UNUOM:
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]COMMENTS:
[/TD]
[TD]992966_8552_VIBRATION ANALYSTS INC_060217
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]FIELDNAMES: Supplier ID
[/TD]
[TD]Supplier Part ID
[/TD]
[TD]Manufacturer Part ID
[/TD]
[TD]Item Description
[/TD]
[TD]SPSC Code
[/TD]
[TD]Unit Price
[/TD]
[TD]Unit of Measure
[/TD]
[TD]Lead Time
[/TD]
[TD]Manufacturer Name
[/TD]
[TD]Supplier URL
[/TD]
[TD]Manufacturer URL
[/TD]
[TD]Market Price
[/TD]
[TD]Supplier Part Auxiliary ID
[/TD]
[TD]Language
[/TD]
[TD]Currency
[/TD]
[TD]Short Name
[/TD]
[TD]Image
[/TD]
[TD]Thumbnail
[/TD]
[TD]UNSPSC
[/TD]
[TD]MinimumQuantity
[/TD]
[TD]QuantityInterval
[/TD]
[TD]SimilarItems
[/TD]
[TD] Effective Date
[/TD]
[TD] Expiration Date
[/TD]
[TD]Keywords
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]DATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]VAI001
[/TD]
[TD]VAI001
[/TD]
[TD]Vibration Analysis
[/TD]
[TD]41113320
[/TD]
[TD]10
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Analysis
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]VAI002
[/TD]
[TD]VAI002
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD]41113320
[/TD]
[TD]81
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]ENDOFDATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here is the current output results of code.
Here is the EXPECTED results
Need code to also remove the leading comma right after each of the header fields
CHARSET:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]UT[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]F-8
LOADMODE:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]F
CODEFORMAT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]U[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]NSPSC_V13.5
CURRENCY:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]USD
SUPPLIERID_DOMAIN:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]N[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]etworkID
ITEMCOUNT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]2
TIMESTAMP:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]8[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]/4/2017
UNUOM:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]True
COMMENTS:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]92966_8552_VIBRATION ANALYSTS INC_060217
Code is also removing the all data below "DATA" (cell A12 on excel table). Need this to stay.
ENDOFDATA should be the last line of text with no trailing commas.
Code:
Option Explicit
Sub CreateCIF()
Dim ws As Worksheet
Dim rng As Range
Dim strFileName As String
Dim FF As Long
Dim NoVals As Long
Dim arrVals
Set ws = Worksheets("Sheet1")
Set rng = Range("A1")
strFileName = ws.Name & ".cif"
FF = FreeFile()
Open "C:\Users\skeebaug\Desktop\" & strFileName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL]
While rng.Value <> ""
NoVals = Application.WorksheetFunction.CountA(rng.EntireRow)
If NoVals = 1 Then
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , rng.Value
Else
arrVals = rng.Resize(, NoVals).Value
arrVals = Application.WorksheetFunction.Transpose(arrVals)
arrVals = Application.WorksheetFunction.Transpose(arrVals)
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , Join(arrVals, ",")
End If
Set rng = rng.Offset(1)
Wend
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL]
End Sub
Here is the layout of data in excel. In this example there are only 2 rows of "catalog data" (rows 13&14), however other catalogs will have more
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CIF_I_V3.0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CHARSET:
[/TD]
[TD]UTF-8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]LOADMODE:
[/TD]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CODEFORMAT:
[/TD]
[TD]UNSPSC_V13.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CURRENCY:
[/TD]
[TD]USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]SUPPLIERID_DOMAIN:
[/TD]
[TD]NetworkID
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ITEMCOUNT:
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]TIMESTAMP:
[/TD]
[TD]8/4/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]UNUOM:
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]COMMENTS:
[/TD]
[TD]992966_8552_VIBRATION ANALYSTS INC_060217
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]FIELDNAMES: Supplier ID
[/TD]
[TD]Supplier Part ID
[/TD]
[TD]Manufacturer Part ID
[/TD]
[TD]Item Description
[/TD]
[TD]SPSC Code
[/TD]
[TD]Unit Price
[/TD]
[TD]Unit of Measure
[/TD]
[TD]Lead Time
[/TD]
[TD]Manufacturer Name
[/TD]
[TD]Supplier URL
[/TD]
[TD]Manufacturer URL
[/TD]
[TD]Market Price
[/TD]
[TD]Supplier Part Auxiliary ID
[/TD]
[TD]Language
[/TD]
[TD]Currency
[/TD]
[TD]Short Name
[/TD]
[TD]Image
[/TD]
[TD]Thumbnail
[/TD]
[TD]UNSPSC
[/TD]
[TD]MinimumQuantity
[/TD]
[TD]QuantityInterval
[/TD]
[TD]SimilarItems
[/TD]
[TD] Effective Date
[/TD]
[TD] Expiration Date
[/TD]
[TD]Keywords
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]DATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]VAI001
[/TD]
[TD]VAI001
[/TD]
[TD]Vibration Analysis
[/TD]
[TD]41113320
[/TD]
[TD]10
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Analysis
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]VAI002
[/TD]
[TD]VAI002
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD]41113320
[/TD]
[TD]81
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]ENDOFDATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here is the current output results of code.
Code:
CIF_I_V3.0
CHARSET:,UTF-8
LOADMODE:,F
CODEFORMAT:,UNSPSC_V13.5
CURRENCY:,USD
SUPPLIERID_DOMAIN:,NetworkID
ITEMCOUNT:,2
TIMESTAMP:,8/4/2017
UNUOM:,True
COMMENTS:,992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA
Here is the EXPECTED results
Code:
CIF_I_V3.0
CHARSET:UTF-8
LOADMODE:F
CODEFORMAT:UNSPSC_V13.5
CURRENCY:USD
SUPPLIERID_DOMAIN:NetworkID
ITEMCOUNT:2
TIMESTAMP:8/4/2017
UNUOM:TRUE
COMMENTS:992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA
,VAI001,VAI001,Vibration Analysis,41113320,29,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Analysis,Image Url,,41113320,1,1,,,,Vibration
,VAI002,VAI002,Vibration Equipment Fee,41113320,81.91,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Equipment Fee,,,41113320,1,1,,,,Vibration
ENDOFDATA
Need code to also remove the leading comma right after each of the header fields
CHARSET:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]UT[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]F-8
LOADMODE:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]F
CODEFORMAT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]U[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]NSPSC_V13.5
CURRENCY:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]USD
SUPPLIERID_DOMAIN:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]N[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]etworkID
ITEMCOUNT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]2
TIMESTAMP:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]8[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]/4/2017
UNUOM:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]True
COMMENTS:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]92966_8552_VIBRATION ANALYSTS INC_060217
Code is also removing the all data below "DATA" (cell A12 on excel table). Need this to stay.
ENDOFDATA should be the last line of text with no trailing commas.