I notice that if you click the .vbs icon without dropping a file on it, then you get a MS script error popup, as expected. Is it possible to replace that with a Warning popup instead so if she does click it, it will remind her the icon will only work when you drop a .csv file on it ?
Updated version below. If you double click or in some other way start the script with no file supplied you get this:
If you drop a file (it doesn't have to have any particular name or extension but it
should conform to the format of your source.txt that you posted) you get this:
Requirements for dragged file:
* It must reside on a writeable medium such as a hard drive (e.g. NOT a write-protected pen drive or CD-ROM) because the utility is hardwired to create the download.csv in the same folder.
* It must be a CSV file in format, the name can have spaces, and the extension can be anything you like (or absent).
* It must have a header line as per your source.txt with at least the same number of fields.
* It must have at least one data line, with at least the same number of fields, and they must have quotes around them.
* It doesn't matter if the data fields have commas in them.
Until you click OK in this dialog, any previous file called "download.csv" in the same folder as the dragged file can be renamed or moved out of the way. Once you click OK, the script will obliterate it and over write it with the new one.
Click OK and the processing starts.
When it is done (this should be more or less instantly) you get this
Note: if the dragged file is wrong in some way, e.g. it is a csv but has the wrong number of fields or lines, or if it is a wrong type of file altogether, (a Word document or a jpg or whatever!) then you would get a standard MS error message like this at the processing stage (It would be quite complex to make a custom message for this so you will have to rely on guiding the user to only drag the right file, unless you want to enforce a definite file name policy?)
Obviously, details in the messages such as the script name and the file paths are going to be different on your machine.
As I said above, the output file name "download.csv" is hard-coded, as is the folder where it will go, (the same one as the input file) and it will permanently obliterate and replace any file called download.csv that happens to be in there already.
All testing and writing has been done on Windows 7 so you should be OK if you upgrade your OS at some later date.
Let me know how you get on.
Const ForReading = 1
Const ForWriting = 2
MsgTitle = "CSV Transformer Utility"
If wscript.arguments.Count = 0 Then
dummy = MsgBox("There was an error:" & vbcrlf & "You need to drop a file!" , vbOKOnly + vbCritical, MsgTitle)
wscript.Quit
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ReadFile = objFSO.OpenTextFile (wscript.arguments(0), ForReading)
Set objFile = objFSO.GetFile(wscript.arguments(0))
InputFileName=objFile.Path
dummy = MsgBox("Input file: " & vbcrlf & InputFileName & vbcrlf & vbcrlf & "Ready to create file: "& VBCRLF & objFile.ParentFolder & "\Download.csv" , vbInformation, MsgTitle)
Set Writefile = objFSO.CreateTextFile (objFile.ParentFolder & "\Download.csv", ForWriting)
' Read input file all at once
strText = ReadFile.ReadAll
Readfile.Close
' Split input file into lines and store in array
arrFileLines = Split(strText, vbCrLf)
' Process first (headers) Line
StrHeaderLine = arrFileLines(0)
arrHeaderFields = Split(StrHeaderline, ",", -1, 1)
strNewHeaderLine=""
For k=0 To 16
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'Rem modified Header 17
strNewHeaderLine = strNewHeaderLine & " Postage and Packing" & ","
For k=18 To 27
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' New Header 28
strNewHeaderLine = strNewHeaderLine & " Escrow ID" & ","
' New Header 29
strNewHeaderLine = strNewHeaderLine & " Invoice ID" & ","
For k=28 To 32
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' Modified Header 33
strNewHeaderLine = strNewHeaderLine & " Address Line 2/District" & ","
For k=34 To 37
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'
' AS PROMISED HEADER NOW ENDS WITH A COMMA
'
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(38) & ","
Writefile.writeline strNewHeaderLine
For j = 1 To (UBound(arrFileLines)-1)
ReadLine = arrFileLines (j)
arrFields = Split(Readline, Chr(34), -1, 1)
OutputLine=""
' Split data lines at quote marks
' Even numbered lines starting at 0 will be commas
' odd numbered lines will be the fields
' commas in fields are OK now
' Process up To & including Invoice Number
For k=1 To 63 step 2
outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
Next
' Add the new blank fields
' Escrow ID
outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
' Invoice ID
outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
' Add all remaining fields except last
For k= 65 To 75 step 2
outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
Next
'
' Add last field *** with *** trailing comma
'
outPutLine=OutputLine & Chr(34) & arrFields(77) & Chr(34) & ","
Writefile.writeline outPutLine
Next
Writefile.Close
dummy = MsgBox("Processing completed" , vbInformation, MsgTitle)
