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,082,171
Messages
5,363,543
Members
400,748
Latest member
mtpt

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top