Hi,

I have 4 Excel files that are created on a regular basis. Each file has one
worksheet. I need to find an automated method (VB Script?) that will simply
rename the worksheets within the files to the name of the file. For Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

Hello,

The code below should do the job assuming you have Excel installed:
--------------------------------------------------------------------
yourXlsFile = "c:\yourFolder\yourFile.xls"

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(yourXlsFile)
xlBook.Worksheets(1).Name = xlBook.Name
xlBook.Close 1
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
--------------------------------------------------------------------
Regards,
Ato

"Dragon" wrote in message ...
Hi,

I have 4 Excel files that are created on a regular basis. Each file has one
worksheet. I need to find an automated method (VB Script?) that will simply
rename the worksheets within the files to the name of the file. For Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

Sub NameSheet()
ActiveSheet.Name = ActiveWorkbook.Name
End Sub
--
tj


"Dragon" wrote:

Hi,

I have 4 Excel files that are created on a regular basis. Each file has one
worksheet. I need to find an automated method (VB Script?) that will simply
rename the worksheets within the files to the name of the file. For Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

I don't know if this is exactly what you are looking for (as it is not
outside Excel), but one possibility is you could put two event handlers in
the Thisworkbook modules of your workbooks to change the sheet name whenever
the WB is opened or closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Sheets(1).Name < ThisWorkbook.Name Then
ThisWorkbook.Sheets(1).Name = ThisWorkbook.Name
ThisWorkbook.Save
End If
End Sub

Private Sub Workbook_Open()
If ThisWorkbook.Sheets(1).Name < ThisWorkbook.Name Then _
ThisWorkbook.Sheets(1).Name = ThisWorkbook.Name
End Sub


"Dragon" wrote:

Hi,

I have 4 Excel files that are created on a regular basis. Each file has one
worksheet. I need to find an automated method (VB Script?) that will simply
rename the worksheets within the files to the name of the file. For Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

Thank you Ato. Works perfect. :-)


"Ato Bisda" wrote in message
...
Hello,

The code below should do the job assuming you have Excel installed:
--------------------------------------------------------------------
yourXlsFile = "c:\yourFolder\yourFile.xls"

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(yourXlsFile)
xlBook.Worksheets(1).Name = xlBook.Name
xlBook.Close 1
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
--------------------------------------------------------------------
Regards,
Ato

"Dragon" wrote in message
...
Hi,

I have 4 Excel files that are created on a regular basis. Each file has
one
worksheet. I need to find an automated method (VB Script?) that will
simply
rename the worksheets within the files to the name of the file. For
Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

Thank you TJ for the reply.
I believe the script you mentioned works only within Excel and I needed a
script that I could use outside Excel.

"tjtjjtjt" wrote in message
...
Sub NameSheet()
ActiveSheet.Name = ActiveWorkbook.Name
End Sub
--
tj


"Dragon" wrote:

Hi,

I have 4 Excel files that are created on a regular basis. Each file has
one
worksheet. I need to find an automated method (VB Script?) that will
simply
rename the worksheets within the files to the name of the file. For
Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.

Thank you JMB.

I do not believe it will work in my situation as workbooks are created
automatically via a third party application that overwrites existing books
and will kill my in-file code. Also, another applications reads these files
so I am not sure if it actually opens it to ready or just reads it via code.

Thanks.

"JMB" wrote in message
...
I don't know if this is exactly what you are looking for (as it is not
outside Excel), but one possibility is you could put two event handlers in
the Thisworkbook modules of your workbooks to change the sheet name
whenever
the WB is opened or closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Sheets(1).Name < ThisWorkbook.Name Then
ThisWorkbook.Sheets(1).Name = ThisWorkbook.Name
ThisWorkbook.Save
End If
End Sub

Private Sub Workbook_Open()
If ThisWorkbook.Sheets(1).Name < ThisWorkbook.Name Then _
ThisWorkbook.Sheets(1).Name = ThisWorkbook.Name
End Sub


"Dragon" wrote:

Hi,

I have 4 Excel files that are created on a regular basis. Each file has
one
worksheet. I need to find an automated method (VB Script?) that will
simply
rename the worksheets within the files to the name of the file. For
Example,
if I have a File1.xls with Sheet1 in it, script will simply rename the
Sheet1 to File1 or File1.xls. I need to do this to all my 4 files on a
regular basis so I will simply schedule this script. I need this method
outside excel so that it is portable. If I need to have either Excel or
Excel viewer, it is ok as well.

Thank you.