How did you manage to save a workbook with "/" in a worksheet tab name?

Excel doesn't typically allow that. Is this an Excel workbook format being
saved or created by some other application? If so, why not address the
problem at the source?


"Ronbo" wrote:

Your code and code I have found by others seems to refer to the
ActiveWorkbook. My problem is that I can not open (activate) the workbook
that contains a worksheet with "/" in the sheet name (other than manually). I
am trying to open the workbook with;

Workbooks.Open ("C:\ABC\XYZ\workbook.xls"), Password:="" but it gives the
error of "Method 'Open' of object 'Workbooks' failed.

Am I missing something in your code that will change the worksheet name and
then open the workbook???



"XLjedi" wrote:

I wrote a short VBA function to rename invalid worksheet names. Basically,
it converts every character that is not Alpha-Numeric-Underscore to
Underscore.

This is the code for the AlphaNumOnly formula that converts a string:

Function AlphaNumOnly(ByVal ConString As String) As String
Dim i As Integer
Dim x As Integer, n As String
Dim last As String

For i = 1 To Len(ConString)
x = Asc(Mid(ConString, i, 1))
Select Case x
Case 32 'space
If last < "" Then
n = n & "_"
last = ""
End If
Case 38 '&
If last < "" Then
n = n & "_"
last = ""
End If
Case 48 To 57 'numeric
n = n & Chr(x)
last = Chr(x)
Case 65 To 90 'uppercase
n = n & Chr(x)
last = Chr(x)
Case 95 'underscore
If last < "" Then
n = n & Chr(x)
last = ""
End If
Case 97 To 122 'lowercase
n = n & Chr(x)
last = Chr(x)
Case Else
If last < "" Then
n = n & "_"
last = ""
End If
End Select
Next i
AlphaNumOnly = n
End Function

...and you can use the function to convert every tabname in a workbook like
this:

Sub ATB_AlphaNumSheetName()
Dim Sheet As Worksheet
Dim n As String

On Error GoTo errhand
For Each Sheet In ActiveWorkbook.Sheets
n = AlphaNumOnly(Sheet.Name)
Sheet.Name = n
Next Sheet

Exit Sub
errhand:
Select Case Err.Number
Case 1004
Err.Clear
n = n & "_"
Resume
Case Else
MsgBox "Err: " & Err.Number & vbCrLf & Err.Description,
vbExclamation, "Error"
Stop
Resume
End Select
End Sub