?
Solved

Strip out characters from a string in Excel using VBA

Posted on 2006-03-31
6
Medium Priority
?
13,721 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:markholmes24
  • 2
  • 2
  • 2
6 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 16343922
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
 
LVL 14

Assisted Solution

by:Tommy Kinard
Tommy Kinard earned 500 total points
ID: 16343999
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
 

Author Comment

by:markholmes24
ID: 16352145
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16352593
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
 

Author Comment

by:markholmes24
ID: 16353653
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 16360588
Maybe because Len(strGroupname) = 1 or 0

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

dragontooth


0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Introduction to Processes

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question