Strip out characters from a string in Excel using VBA
Posted on 2006-03-31
I need some VBA code which wll remove certain characters from a string.
At the moment, my script reads values from the 2nd column, adds the prefix DL_ to the string and write's it to the frst column. The string it reads is actually a path, eg E:\Datalibrary\britain\bes565 - it then output's this to the first column as DL_E:\Datalibrary\britain\bes565.
What I basically need to do is before the string gets written to the first col, strip out the E: and replace any \ with _ .
The spreadsheet once finished is going to be used to feed a VBScript which creates AD groups and assgns permissions to folders automatically, based on the data it reads from the 3 columns of the spreadsheet.
Any help greatly appreciated.
Private Sub CommandButton1_Click()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
'--- set a reference to Excel
Set objXL = New Excel.Application
'--- open the workbook
Set objWkb = objXL.Workbooks.Open("c:\groupsunformatted.xls")
Set objSht = objWkb.Worksheets("Grouplist")
Dim iRow As Integer
Dim strCol1 As String
Dim strCol2 As String
'Start reading from row 1
iRow = 2
Do While True
strCol1 = objSht.Cells(iRow, 1).Value
strCol2 = objSht.Cells(iRow, 2).Value
'strCol3 = objSht.Cells(iRow, 3).Value
'--- if the name of the field is "end of list" then exit the loop
If strCol1 = "endoflist" Then
'objSht.Cells(iRow, 6) = strField
ActiveSheet.Cells(iRow, 1).Value = "DL_" & strCol1 'write's suggested group name to col1, prefixing it with DL_
ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2
'move down one row
iRow = iRow + 1