VBScript help to convert a column of last names to initials

[VegasPat]

I have a script that opens an Excel document and I would like to have it take the values of the last name column and replace them with just the first character. I have created a objRange for the column and tried using REPLACE, LEFT & MID with no luck. I will post my script when I get back to work, but I wanted to see if there was a quick response. Thanks.

 

[SkipVought]

Nice try.

Lets see your code and describe the results you're getting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VegasPat]

Here's my code, thanks for any assistance!

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
strWB = "D:\content\Tournament."

Set objWorkbook = objExcel.Workbooks.Open(strWB & "xls")
objExcel.ActiveSheet.Rows("1:7").Delete
objExcel.ActiveSheet.Rows("26:1000").Delete
objExcel.ActiveSheet.Columns("A").Delete
objExcel.ActiveSheet.Columns("B:C").Delete
objExcel.ActiveSheet.Columns("C").Delete
objExcel.ActiveSheet.Columns("E").Delete
objExcel.ActiveSheet.Columns("F:H").Delete

Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn


Const xlCSV = 6
objExcel.ActiveWorkbook.SaveAs strWB & "csv", xlCSV
objExcel.Quit

 

[PHV]

A starting point:

...
Set objRange = objExcel.Range("D1").EntireColumn
For Each cell In objRange
  If cell.Value > " " Then
    cell.Value = Left(cell.Value,1)
  Else
    Exit For
  End If
Next
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I have created a objRange for the column and tried using REPLACE, LEFT & MID with no luck

Where is THAT code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VegasPat]

Thanks PVH for pointing me in the right direction. I will work on it today.

Skip, that was as far as I got. I didn't know how to code the last part. I was using REPLACE, LEFT & MID on the objRange instead of the cell values. Now I know I need attack the cell values and not just the selected column.

 

[VegasPat]

Updated my script and I feel like I'm getting close. After running the script with PHV's snippet, I got a Type Mismatch error on the line: If cell.Value > " " Then

My first thought is that I'm comparing text and the > will not work. So I thought to use StrComp instead. The conditional seems to work, but now I get a Type Mismatch error on my else statement. See my revised code below. I just can't get that darn Left command to work...

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
strWB = "D:\content\SlotTournament."

Set objWorkbook = objExcel.Workbooks.Open(strWB & "xls")
objExcel.ActiveSheet.Rows("1:7").Delete
objExcel.ActiveSheet.Rows("26:65536").Delete
objExcel.ActiveSheet.Columns("A").Delete
objExcel.ActiveSheet.Columns("B:C").Delete
objExcel.ActiveSheet.Columns("C").Delete
objExcel.ActiveSheet.Columns("E").Delete
objExcel.ActiveSheet.Columns("F:H").Delete

Set objRange = objExcel.Range("D1").EntireColumn
For Each cell In objRange
str1 = cell.Value
str2 = " "
initCompare = StrComp(srt1, str2, vbTextCompare)
If initCompare = 0 Then
Exit For
Else
cell.Value = Left(cell.Value, 1)
End If
Next

Const xlCSV = 6
objExcel.ActiveWorkbook.SaveAs strWB & "csv", xlCSV
objWorkbook.Close
objExcel.Quit

 

[PHV]

And this ?
cell.Value = Left(str1 & " ", 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

maybe

If CStr(cell.Value) > " " Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VegasPat]

I tried both suggestions and I still get a Type Mismatch error. I feel that the cell.Value is not coming through as a string value for some reason thus the Left command is not working.

 

[PHV]

And this ?

For Each cell In objRange
  If (cell.Text & "") > " " Then
    cell.Value = Left(cell.Text & "",1)
  Else
    Exit For
  End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VegasPat]

After I tried this, the last names did not change. After some testing, I found that cell.Text was returning a null value. I can't figure out why the values for the last names are coming through as just text. When I try to convert the value using CStr, I get a type mismatch error. Since the cell value is not an expression,I figure that's why CStr doesn't work.