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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
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
Tommy KinardCommented:
Maybe because Len(strGroupname) = 1 or 0

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

dragontooth


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.