help with existing vb code

steve2115

Board Regular
Joined
Mar 17, 2014
Messages
79
Need help modifying the code below to meet my requirements.

Instead of having the InputBox prompt for Vendor No & Vendor Name and then saving to C:/. I would like to code to automatically use the file name I am converting into .CIF and store to a specified folder.

Also, need the bottom part of code to remove any commas in range A1:B10 when converting to the .CIF. Below is an example of the .CIF output file.

This is the data in Range A1:B10. Need to remove all commas from this range.
CIF_I_V3.0
CHARSET:,UTF-8
LOADMODE:,F
CODEFORMAT:,UNSPSC_V13.5
CURRENCY:,USD
SUPPLIERID_DOMAIN:,NetworkID
ITEMCOUNT:,2
TIMESTAMP:,8/5/2017
UNUOM:,True
COMMENTS:,992966_8552_VIBRATION ANALYSTS INC_060217
Example .CIF output
CIF_I_V3.0
CHARSET:,UTF-8
LOADMODE:,F
CODEFORMAT:,UNSPSC_V13.5
CURRENCY:,USD
SUPPLIERID_DOMAIN:,NetworkID
ITEMCOUNT:,2
TIMESTAMP:,8/5/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
Code:
Option Explicit
Sub CIFfile()
'
' Create CIF File for Ariba
'
    Dim VendorNo
    Dim VendorName
   
    VendorNo = InputBox("Please enter Vendor No")
    If VendorNo = "" Then Exit Sub
    VendorName = InputBox("Please enter Vendor Name")
    If VendorName = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\" & VendorNo & "_" & VendorName & "_" & Format(Date, "YYYYMMDD") & ".cif", True)
    
    Dim r As Long
    Dim c As Long
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    For r = 1 To ws.UsedRange.Rows.Count
        s = ""
        For c = 1 To ws.UsedRange.Columns.Count
            If InStr(ws.Cells(r, c), ",") > 0 Then
                s = s & """" & Replace(ws.Cells(r, c), """", """""") & ""","
            Else
                s = s & ws.Cells(r, c) & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub
 

Forum statistics

Threads
1,085,992
Messages
5,387,141
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top