Renamed invalid sheet name
 

I have a routine that opens a workbook to access data and then closes it. It
has worked fine for years with XP and Excel 2000. Since changing to Win 7
and Excel 2003 it will no longer automatically open. A warning box appears
"Repairs to 'Workbook'" and the message "Renamed invalid sheet name". After
closing the message box it renames the worksheet to "Recovered_Sheet 1" and
opens the workbook. However, this is a manual process and I need it to be
automated as before.

What I have found (I believe) is that if the file name includes "/" in 2003,
it will not automatically open as it does in 2000.

I have tried "DisplayAlets = False" but that does not work and have look
here and Googled the problem but have not come up with a solution.

Any help would be appreciated.

Ronbo

Renamed invalid sheet name
 

Post your code.
--
Cheers,
Ryan


"Ronbo" wrote:

I have a routine that opens a workbook to access data and then closes it. It
has worked fine for years with XP and Excel 2000. Since changing to Win 7
and Excel 2003 it will no longer automatically open. A warning box appears
"Repairs to 'Workbook'" and the message "Renamed invalid sheet name". After
closing the message box it renames the worksheet to "Recovered_Sheet 1" and
opens the workbook. However, this is a manual process and I need it to be
automated as before.

What I have found (I believe) is that if the file name includes "/" in 2003,
it will not automatically open as it does in 2000.

I have tried "DisplayAlets = False" but that does not work and have look
here and Googled the problem but have not come up with a solution.

Any help would be appreciated.

Ronbo

Renamed invalid sheet name
 

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

Renamed invalid sheet name
 

Here is a shorter function that does the same thing your AlphaNumOnly
function does...

Function AlphaNumOnly(ByVal ConStr As String) As String
Dim x As Long
For x = 1 To Len(ConStr)
If Mid(ConStr, x, 1) Like "[!0-9A-Za-z]" Then Mid(ConStr, x, 1) = " "
Next
AlphaNumOnly = Replace(WorksheetFunction.Trim(ConStr), " ", "_")
End Function

--
Rick (MVP - Excel)


"XLjedi" wrote in message
...
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

Renamed invalid sheet name
 

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