Strip out characters from a string in Excel using VBA

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.

Regards,

Mark


My code:-

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
          Exit Do
     End If
     


'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
Loop
 
  objWkb.Close
 
End Sub
markholmes24Asked:
Who is Participating?
 
Arthur_WoodCommented:
try this change:

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
          Exit Do
     End If


'objSht.Cells(iRow, 6) = strField

ActiveSheet.Cells(iRow, 1).Value = "DL_" &Replace(Replace(strCol1, "E:", ""), "\", "_") 'write's suggested group name to col1, prefixing it with DL_ <<<<<MAKE THIS CHANGE IN THIS ONE LINE

ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2


     'move down one row
     
     iRow = iRow + 1
Loop
 
  objWkb.Close
 
End Sub

AW
0
 
Tommy KinardCommented:
Hi markholmes24,
I didn't use a double replace because I wasn't sure that it would always be "E:". Anyway another alternative :)

...................
 strCol1 = FxStr(objSht.Cells(iRow, 1).Value)
 strCol2 = FxStr(objSht.Cells(iRow, 2).Value)
.....................

Public Function FxStr(iVal) As String
    Dim mTemp$, mCnt&
    mTemp = Replace(CStr(iVal), "\", "_")
    mCnt = InStr(1, mTemp, ":") - 1
    FxStr = Left(mTemp, mCnt - 1) & Mid(mTemp, mCnt + 2)
End Function

dragontooth

0
 
markholmes24Author Commented:
Thanks both, I'll split the points.

How would I trim the _ off the end of the striing? - i tried using Left,but it didn't work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Arthur_WoodCommented:
If the _ is the very last character on the right end of the string:

MyString = Left(MyString , Len(MyString) - 1)  <<< gives you everything to the left of the last character in the string

AW
0
 
markholmes24Author Commented:
I'm still getting 'invalid procedure call or arguement' when I run this sub - looks like the problem is with line strGroupname = Left(strGroupname, Len(strGroupname) - 1).  Any ideas?


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 a reference to the correct tab (I always name my worksheet tabs, rather than leave the default)
Set objSht = objWkb.Worksheets("Grouplist")


Dim iRow As Integer
Dim strCol1 As String
Dim strCol2 As String
Dim strGroupname As String
Dim strGroupnameF As String
'Start reading from row 1
iRow = 2
Do While True
     '--- read the name of the field (which is in the 1st column)
     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
          Exit Do
     End If



'objSht.Cells(iRow, 6) = strField
strGroupname = strCol1
strGroupname = Replace(Replace(strGroupname, "E:", ""), "\", "_")
strGroupname = Left(strGroupname, Len(strGroupname) - 1)
ActiveSheet.Cells(iRow, 1).Value = "DL" &strGroupname
ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2
'ActiveSheet.Cells(iRow, 4).Value = strCol3



     '--- move down one row
     
     iRow = iRow + 1
Loop
 
  objWkb.Close
 
End Sub
0
 
Tommy KinardCommented:
Maybe because Len(strGroupname) = 1 or 0

Try
if Right(strGroupname,1) = "_" Then strGroupname = Left(strGroupname, Len(strGroupname) - 1)

dragontooth


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.