' Currently this VBScript is set to replace-commas-with-pipes for every file that is 'in the folder where this VBScript sits: ' Before run it do the following: ' 1. Open VBScript in text editor ' 2. Set the parameters: ' cFolder_Name = "." ' current directory ' cOnlyOneFile = False ' set to True if I want to process only one file with name set below ' cOnlyFileName = "thisfile.csv" ' 3. Place the VBScript into the target folder ' 4. Run it by double-click ' Notes: http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/79ddd204ebb88287/be5d195da9241df9%23be5d195da9241df9 ' ------------------------------------------------------- On Error Resume Next const cFolder_Name = "." ' current directory const cOnlyOneFile = False ' False, So all files in the folder. const cOnlyFileName = "thisfile.csv" ' Uses this if the above was True (only wanted to process 1 file). Set oFSO = CreateObject("Scripting.FileSystemObject") ' Get folder: Set oFolder = oFSO.GetFolder(cFolder_Name) If oFolder Is Nothing Or Err.Number <> 0 Then MsgBox "Can't get a folder for search files. " + vbCRLF + "Please check the folder name." + vbCRLF + Err.Description, vbCritical, "Fatal error!" ' I will probably comment this out when this VBScript runs automatically as a scheduled job. End If Set oFiles = oFolder.Files If Err.Number <> 0 Or oFiles Is Nothing Then MsgBox "Can't get a list of files of folder." + vbCRLF + "" + vbCRLF + Err.Description, vbCritical, "Fatal error!" ' I will probably comment this out when this VBScript runs automatically as a scheduled job. End If Cnt = CInt(0) For Each oFile In oFiles If oFSO.GetExtensionName(oFile.Name) = "csv" Then ' Checks for "csv" file extension. If (cOnlyOneFile And (oFile.Name = cOnlyFileName)) Or (Not cOnlyOneFile) Then Call ReplaceSymbols(oFile.Name) Cnt = Cnt + 1 End If End If Next Senmail() MsgBox "Replacing is done. Total number of files had been processed: " + CStr(Cnt), vbExclamation, "Message..." ' I will probably comment this out when this VBScript runs automatically as a scheduled job. ' cleanup Set oFiles = Nothing Set oFSO = Nothing ' Find-and-replace: Sub ReplaceSymbols(oFileName) ' AKA "File.Name". If oFSO.FileExists(oFileName) Then Set oTextFile = oFSO.OpenTextFile(oFileName, 1, False) sFileContents = oTextFile.ReadAll oTextFile.Close Set oRegEx = CreateObject("VBScript.RegExp") With oRegEx .Global = True .IgnoreCase = False .Pattern = "," ' Or vbTab or "|" etc... sFileContents = .Replace(sFileContents, "|") ' .Pattern = "\|" ' reverse replacing ' sFileContents = .Replace(sFileContents, ",") End With Set oNewFile = oFSO.CreateTextFile(oFileName, True) ' Maybe oNewFile is not needed--it just could have been called oTextFile. oNewFile.Write sFileContents oNewFile.Close End If End Sub ' NOTE: This first way would only work on my PC, not on the other user's PCs; so I must use the second snippet, Senmail(), at the bottom. NOTE: If I made the changes marked "future testing" this might work. ' Send email to Jon Doe. --If the PC this runs on is not set right, this email will not get sent. 'Set oMessage = CreateObject("CDO.Message") 'oMessage.From = "Generated automatically for AWARE
" ' NOTE: If future testing: Comment this out. 'oMessage.To = "firstname.lastname@example.org,email@example.com" ' NOTE: If future testing: Should be ";". 'oMessage.Subject = "AWARE: Extracts done." 'oMessage.Sender = "firstname.lastname@example.org" ' NOTE: If future testing: Comment this out. 'oMessage.TextBody = "AWARE: Commas replaced with pipes." 'oMessage.Send ' NOTE : Is using MS Outlook API to send e-mails. So check If MS Outlook installed on a workstation before a using of this script. Sub Senmail() Set objOutlook = CreateObject("Outlook.Application") Set objOutlookMsg = objOutlook.CreateItem(0) With objOutlookMsg ' This line caused it not to even popup warning, so leave it off: .From = "email@example.com " ' "Generated automatically for AWARE " .To = "firstname.lastname@example.org; email@example.com; firstname.lastname@example.org; email@example.com" .Subject = "AWARE: Extracts done." ' This line caused it not to even popup warning, so leave it off: .Sender = "firstname.lastname@example.org" ' NOTE: jon.doe2's email is automatically inserted here because it is sent from here Outlook. .Body = "AWARE: Commas replaced with pipes. [Note: This message was generated and sent automatically.]" .Send End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub
- Right-click on toolbar and "Choose Items"
- Then browse and go to your /Controls/ folder
- Then click on it and open
- Then click "OK"
Then it will be on your toolbar and there will be a Reference to it in your /Bin/