Worksheet renaming

[jwilliam33]

I have several worksheets within an Excel file, however, when I renamed them,
they all saved with a .xls] extension and now I am unable to rename them and
cannot include them in a formula without an error. How do I rename the
worksheets without the extensions as the normal renaming function will not
allow me to do so. Thanks.

 

[Dave Peterson]

Close excel
Open windows explorer
Rename the files and remove any square brackets ([]) in the file name.

Open the files in excel and try renaming the worksheets again.

I have several worksheets within an Excel file, however, when I renamed them,
they all saved with a .xls] extension and now I am unable to rename them and
cannot include them in a formula without an error. How do I rename the
worksheets without the extensions as the normal renaming function will not
allow me to do so. Thanks.

 

[Mike H]

Hi,

I don't understand why you can't simply rename them manually unless the
workbook is protected and if it is this macro won't help but in case you have
a lot of sheets here's a macro that wil remove the .xls from the sheet name.

Right click any sheet tab, view code and paste this in and run it

Sub Atomic_Particles()
For x = 1 To Worksheets.Count
If Right(UCase(Sheets(x).Name), 4) = ".XLS" Then
Sheets(x).Name = Left(Sheets(x).Name, Len(Sheets(x).Name) - 4)
End If
Next
End Sub

Mike

 

[Mike H]

Hi Dave,

I wasn't aware of that can you show me a sample filename that prevents this
worksheet renaming.

Mike

Close excel
Open windows explorer
Rename the files and remove any square brackets ([]) in the file name.

Open the files in excel and try renaming the worksheets again.

I have several worksheets within an Excel file, however, when I renamed them,
they all saved with a .xls] extension and now I am unable to rename them and
cannot include them in a formula without an error. How do I rename the
worksheets without the extensions as the normal renaming function will not
allow me to do so. Thanks.

 

[Dave Peterson]

It'll be easier to see if you create a .CSV file.

Then close excel and use windows explorer to include the []'s in the name.

Then reopen excel and open the .CSV file.

Hi Dave,

I wasn't aware of that can you show me a sample filename that prevents this
worksheet renaming.

Mike

Close excel
Open windows explorer
Rename the files and remove any square brackets ([]) in the file name.

Open the files in excel and try renaming the worksheets again.

I have several worksheets within an Excel file, however, when I renamed them,
they all saved with a .xls] extension and now I am unable to rename them and
cannot include them in a formula without an error. How do I rename the
worksheets without the extensions as the normal renaming function will not
allow me to do so. Thanks.

 

[Mike H]

Well that's a new one, thanks Dave

It'll be easier to see if you create a .CSV file.

Then close excel and use windows explorer to include the []'s in the name.

Then reopen excel and open the .CSV file.

Hi Dave,

I wasn't aware of that can you show me a sample filename that prevents this
worksheet renaming.

Mike

Close excel
Open windows explorer
Rename the files and remove any square brackets ([]) in the file name.

Open the files in excel and try renaming the worksheets again.

jwilliam33 wrote:

I have several worksheets within an Excel file, however, when I renamed them,
they all saved with a .xls] extension and now I am unable to rename them and
cannot include them in a formula without an error. How do I rename the
worksheets without the extensions as the normal renaming function will not
allow me to do so. Thanks.