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


0
Utf
2/9/2010 3:10:04 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1674 Views

Similar Articles

[PageSpeed] 5

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
> 
> 
0
Utf
2/9/2010 3:31:02 PM
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

  
0
Utf
2/9/2010 4:22:12 PM
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" <XLjedi@discussions.microsoft.com> wrote in message 
news:D25DDE9D-16AD-437D-9832-5AEEA47BA0C6@microsoft.com...
>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
>
> 

0
Rick
2/9/2010 7:44:03 PM
Reply:

Similar Artilces:

Rename Server
I was would like to know if it is possible to rename a server on which Exchange 2000 has been install onto, would this have an effect on exchange wayne The Exchange services won't start after a server rename. Build a new server with the new name and move the mailboxes to it. -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ "WB" <wmb2003@zoom.co.uk> wrote in message news:u5TGyDCpGHA.2360@TK2MSFTNGP05.phx.gbl... >I was would like to know if it is possible to rename a server on which >Exchange 2000 has been install onto, would this have ...

running a macro on a protected sheet
I've got a scenario where I've got several option buttons in groups to make choices. I've got the sheet protected so the buttons and/or groups can't be accidentally moved by the user. However, I've created a "clear all" button so the user can reset the form to redo their option button choices, if they need to. But, when protected, the macro attached to the "clear all" button won't work (due to the protection). How do I get around this? Thanks! Kriss, here is one way Sub test1() ActiveSheet.Unprotect password:="123" 'you cle...

how to rename a file #2
What MFC class/function would I use to rename a file. Assume the file is not being accessed by any application. I have typically used the C run time library function 'rename' but his there a comparable MFC or SDK function? Thanks Ian Hi, You could use CFile::Rename. Regards Andy Mortimer [MS] Please do not send email directly to this alias. This alias is for newsgroup purposes only This posting is provided "AS IS" with no warranties, and confers no rights. OR if you wish to include a script sample in your post please add "Use of included script samples a...

CDialog/Invalidate/UpdateWindow question
I have a dialog based application with the main dialog calling DoModal on another dialog. The second dialog is involved in some synchronous stored procedure calls, and things that take time to complete. I can't change the processing to threads at this point, but have learned my lesson. My question is, when the user clicks on a second application in the task bar, which is then maximized, or brought to the front in the z-order on the desktop...when the dialog based app taskbar button is pressed, nothing happens until it's completed. Is there something I can do in OnPaint to bring it ...

Tab name is grey! I want it black!
Hi, thanks for viewing my post. I am using 2002 on a mac if that matters but my worksheet tabs have changed to light grey making it difficult to see the worksheet number or name. How would I change it back to black? Thanks! There's some information in the following thread, http://groups.google.com/groups?&threadm=fc7dfa0c.0305202337.4bbf46ff%40posting.google.com and you may want to post your question in the Mac newsgroup: microsoft.public.mac.office.excel crazyaboutgus wrote: > Hi, thanks for viewing my post. I am using 2002 on a mac if that > matters but my workshe...

How do you enbed a marcos or VB in a sheet?
The subject is the question Thanks Mike Hi Mike, Right click on the sheet tab with the sheet name on it. Select View Code. This opens the VBA editor where you can insert code for the specific sheet. Normally you place event driven code here. When you first open the vba editor before any code is placed here, at the top of the large white area you will see 2 drop down boxes. One with the default General and the other with default Declarations. If you click the arrow of the one with General, you can select worksheet. You can then click the arrow of the other box and you will get a list o...

how do i rename a sheet if the rename tab is inactive?
I tried to rename a sheet in a workbook but discovered that the rename option is inactive when i right-clicked on the sheet. How do i make it active ? Hi, This would happen if the 'workbook' was protected. Tools|Protection|Unprotect workbook -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Nelson" wrote: > I tried to rename a sheet in a workbook but discovered that the rename option > is inactive when i right-clicked on the ...

Same Y Axis name for multiple charts
I have 3 charts per worksheet which need to use the same Y value on another sheet - Range A1 through A14 on worksheet ChartNames. Every day I bring in new data for 3 different charts. I'd like all 3 to use the same Y name but without having to manually edit the Series information in the forula bar. Is there a quick and easy way to do this? Currently I copy the same chart from page to page and select the new data range and then manually edit the Series Name, 1 at a time for 14 Series. Hope you guys can help, Mike B dvt wrote: > Do you mean the same thing when you say "Y value&q...

Colouring excel sheet tabs
Just wondering if anyone knows of a way to colour the bottom tabs of an excel worksheet. Is there a macro i can use to colour them? Again, any help much appreciated regards Neal -- Neal Only in XL2002 and 2003 Versions prior to these have no method(macro or otherwise) of colouring sheet tabs. In XL2002 right-click on tab and "Tab Colour" Gord Dibben Excel MVP On Fri, 19 Dec 2003 20:34:22 -0000, "NealUK" <xxx@xxx.xx> wrote: >Just wondering if anyone knows of a way to colour the bottom tabs of an >excel worksheet. Is there a macro i can use to colour...

How to update an external excel sheet
The situation is this: *All the computers have Windows XP. *All this is working under a net. *In computer A: I have a bloomberg terminal. *In computer A: I have an excel sheet open linked to bloomberg that automatically updates the information. *In computer B: I have an excel sheet and I want that everything updates in this computer too. Any help? Thanks. Hi, You could link both workbooks to bloomberg, or link the workbook on computer B to the workbook on computer A. Cheers -- macropod [MVP - Microsoft Word] <google@pichara.com> wrote in message news:1149862448.687935.133210@j...

Error message Invalid Argument when setting Regarding for email
Using Outlook Client for CRM 4.0 Online with all the latest updates installed. For some emails I'm getting error message Invalid Argument when selecting the Regarding icon, then selecting a Account. This is after selecting the Track icon. Doesn't happen on all emails. Any ideas what may be causing this? Thanks in advance Mike Hi Mike, this is a very wild guess, but looking on the web for a similar issue I found some people who solved problems promoting items from Outlook to CRM with changing the max length for E-Mail messageid to 250. The default is 100, and 250 is just somet...

Cost Sheet
I'm trying to come up with a daily cost sheet. What I've done so far is develop a tab on the spreadsheet that has all the information, that I want to retrieve. Now, what I want to do is develop a "summary page" that retrieves information by date. So, what I did was come up with a drop down menu where you are allowed to select the date on which you want to view the performance of the day. My question is how do I make the information change on the "summary page", once you select a different date? One way is illustrated in this sample: http://www.freefilehosting....

Sheet command?
Does anyone know how to automatically update a worksheet? I have an assignment whereby i have to enter the results for 10 english classes each containing 20 students. these results are then to be automatically entered into an overall english summary sheet and on overall summary sheet (containing other subjects) the guide says to do this using the 'sheet command' anybody know how to do this? Or simply how to automatically update a worksheet by transferring the results from one to another? To me,it sounds like you have "raw data" in a data sheet where you enter scores, then in...

Invalid use of property.
I have a sub showing a message box and calling another sub and after continue few code. when the sub is called it doesn't even run trough it i received a Invalid use of property and it point out my other sub this is the boggus sub and dont find anything wrong in it Public Sub PrintJob() On Error GoTo Err_PrintJob NetPrint Select Case CurrentSerie Case "CRD" CRDPrint Case "DA2" DA2Print Case "DA3" DA3Print Case "DA4" DA4Print Case "PDS"...

File name
Hi, Is it possible to show the Excel file name in a cell? Thanks Pilar Pilar =CELL("Filename") -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Pilar" <pilarblancom@terra.es> wrote in message news:zC95d.2633616$A6.8987406@telenews.teleline.es... > Hi, > Is it possible to show the Excel file name in a cell? > > Thanks > Pilar > > Hi see: http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Frank Kabel Frankfurt, Germany "Pilar" <pilarblancom@terra.es> schr...

copy hidden sheet data
I have a macro to copy and past data from one sheet to another. It works fine when I have all the sheets open, but when I hide the source sheet (where my raw data is stored) macro gives me an error (400). Below is the code: Sheets("Temp").Select Range("A1") = "Gas" Sheets("Nlist").Select Range("A7").Select Selection.Copy Sheets("Temp").Select Range("A2").Select ActiveSheet.Paste I want my raw data sheet hidden ("Temp" sheet is always visible, and "Nlist&q...

Windows CE SD Card driver : IsValidFileInfo : Invalid File Info
Hi All, I am getting debug error as FATFS : IsValidFileInfo : Invalid File Info when i connect target and PB through file viewer,and copy the file to SD Card folder. It tells to search path for Volume.cpp file. c:\ymzki\private\winceos\coreos\fsd\exfat But i coundnt find it. Why It is invalid file info.What is the problem? Any file format or some.I am not getting any idea. And first time its work without any problem.When i try to load image again and try to copy files in sd card. And one more issue is the previous files which i copied get deleted or someting i cant see them in se...

Selecting / UnSelecting sheets
Hi, I have used this forum before to get help on my excel issues. Now I have come up with a situation that I cant get around (due to my limited knowledge). I will appreciate your help if someone can point out a way to select/unselect multiple sheets from a workbook using a macro.. a generic one that i can use from a custom toolbar.. thanx in advance. Cheers !!! Shuvro -- shuvro_basu How would that generic macro know which sheets to select (and which to skip)? Worksheets.select or sheets.select would get all of the worksheets (or all of the sheets), though. shuvro_basu wrote: > ...

Renaming your password
I wanted to ask if someone could tell me if it is possible to rename your password? I would like to keep my existing e-mail accounts, and all of my folders and saved information. However, I was told that if someone knows my password and e-mail address, they can access my account from any other computer, anywhere. So, is there some way to handle this without losing the small group of links and e-mail boxes, etc. without bailing out? To change your email account password, contact the account provider, or check their website for assistance. -- Dave N. MS-MVP (Mail) ...

Help with Macro in selecting sheets.
I have recorded a macro and am experiencing problems because during the recording of the macro sheet names have been hard coded. As time has progressed, extra data has been added and extra sheets have been added and therefore when I run it I get the message “Run time Error ‘9’, Subscript out of range”. When I select debug it points to the line that starts Sheets(Array("sheet 1”, “sheet 2”,….” I therefore have 2 problems:- Problem 1. Sheets("ZZZ ABC").Select Sheets("ZZZ ABC ").Copy After:=Sheets(67) Sheets("ZZZ ABC (2)").Select Sheets("...

Saving and opening a named folder using VB
Hi, In creating a workbook, I seem to have struck a dificulty in that if the workbook is transferred to another computer I need to change some of the code each time (as the other computers run different versions of windows, so the path to a specific folder is different.) The code that's affected relates to saving the file and opening the file using VB code within that file. Is there some way that excel can create a new named folder and place it in the My Documents folder so that the procedure would work in allcircumstances? AND also look for that folder (wherever it may be found) to open ...

Money 2002
Greetings, My bank often sends me transactions in the download that have the payee's address, not their name. Is there a way to have it automatically rename say "123 E. Main St" to "AM/PM GAS STATION", instead of me doing it each and everytime I download transactions? In microsoft.public.money, <kharless@qwest.net> wrote: >Greetings, > >My bank often sends me transactions in the download that >have the payee's address, not their name. Is there a way >to have it automatically rename say "123 E. Main St" >to "AM/PM GA...

How to print with a gap in the middle of a sheet
I am trying to setup a print button with code to print a range of data on a 11x17" sheet of paper, portrait layout but to have a break in the middle of the sheet always so if you fold it in half you will not be folding over text. I need something like a footer in the middle of the page. I have 2 seperate parts of the data range. One is for first shift and the other is for 2nd and 3rd shift. So if you fold the 11x17 sheet in half you get 1st shift data on the front and then the 2nd&3rd shift info on the other half but with a break in the middle where the crease is so no text ge...

when I rename outcmd.dat to .old, do I type outcmd.old or just rename to .old
I was given these directions and I am just clarifying: Close Outlook. Find and rename outcmd.dat to .old. (If using Windows 2000 or XP, you will need to enable searching hidden and system folders.) Once you have done so, reopen Outlook and it should open fine. You will need to rebuild any custom toolbars or customizations as the file outcmd.dat controls how the toolbars display. When it becomes corrupted, it will cause Outlook to hang because it cannot create the toolbars that you expect. bob <bob@donotspam> wrote: > I was given these directions and I am just clarifying: >...

Macro for Sheet Navigation (Excel 2007)
I'm looking for a macro that is compatible with Excel 2007. I've searched the forums and shareware sites, but can't find anything current enough. I have a workbook with many sheets, too many to conveniently navigate through with just the basic Excel GUI. I have seen some older shareware apps that create floating menus that allow the user to organize the sheets into groups so that the user only has to interact with a subset of worksheets at any moment, based on the group that they have selected. This allows them to more easily switch between sheets without scrolling horizontally...