Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why doesn't formula work if sheet is renamed? | Excel Discussion (Misc queries) | |||
Renamed invalid sheet name | Excel Discussion (Misc queries) | |||
invalid sheet name problems | Excel Programming | |||
ActiveX CommandButton gets renamed when Sheet is cloned running Excel97 | Excel Programming | |||
Do not want sheet renamed | Excel Programming |