Renaming worksheets in Excel

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Renaming worksheets in Excel

Logged in as: Guest

arrSession:exec spGetSession 2,2,36842

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Renaming worksheets in Excel
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Renaming worksheets in Excel - 8/9/2006 12:48:54 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

Hey folks,

I have run into trouble with excel worksheets, I am working on a script to check for shares on servers and accounts. I want to output the results to an excel spreadsheet, with new sheets for every OU. I am able to rename the first 3 sheets (which are actually made by excel when I create a new workbook) However the ones I create manually, ie with the script, are not renamed.

Here is my script
http://rafb.net/paste/results/nFlMj128.html

Thanks in advance

Post #: 1

RE: Renaming worksheets in Excel - 8/9/2006 1:18:22 AM

Snipah

Posts: 1343
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

'*** Start Excel
Set oXL = CreateObject("Excel.Application") 'open Excel

oXL.Sheets(1).Name = "Renamed..." 'rename first sheet

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to BDIntern)

Post #: 2

RE: Renaming worksheets in Excel - 8/9/2006 1:29:08 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

Snipah,

The same thing occurs. The sheets the workbook comes with are renamed the ones I have the script create are not renamed, they stay "sheet4" ect...

If counter > 3 Then

set objWorksheet = objExcel.Sheets.Add( , objExcel.WorkSheets(objExcel.WorkSheets.Count))

End If

'Rename the sheets to the OU it is working on

objWorksheet.sheets(counter).Activate
objWorksheet.sheets(counter).Name = OuName

(in reply to Snipah)

Post #: 3

RE: Renaming worksheets in Excel - 8/9/2006 1:40:05 AM

Snipah

Posts: 1343
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

What is the exact output of OuName ?

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to BDIntern)

Post #: 4

RE: Renaming worksheets in Excel - 8/9/2006 1:43:10 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

A name

wscript.echo would output bdintern as an example. It is able to set the first 3 sheets (the ones workgroup opens automatically) to ouname, but not the others, if I wscript.echo the current ouname its on when it stops changing the name it still works as it should.

(in reply to Snipah)

Post #: 5

RE: Renaming worksheets in Excel - 8/9/2006 1:56:31 AM

Snipah

Posts: 1343
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

ok, my thoughts next is that a script usually runs quicker than the application runs...

So...if you would have a pause between the creation of another sheet and the renaming of taht sheet...how would that go...

PS. i understood that it held a name, i was more interested in an example, the OUname could have unsupported characters in it....

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to BDIntern)

Post #: 6

RE: Renaming worksheets in Excel - 8/9/2006 2:06:35 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

I've tried having it sleep between creation and naming for 2 secnods, should'nt take more then that should it?

As for the name, it only contains letters. it's strange that anything i create won't work, while the ones that come with it do. Is there a way to open a workbook with a certain number of sheets?

(in reply to Snipah)

Post #: 7

RE: Renaming worksheets in Excel - 8/9/2006 2:56:03 AM

Snipah

Posts: 1343
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

2morrow @ work i will give it another try (have more scripts there)

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to BDIntern)

Post #: 8

RE: Renaming worksheets in Excel - 8/9/2006 4:02:20 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi BDIntern,

this code

Dim aNames
   Dim sFSpec
   Dim oFS
   Dim oExcel
   Dim oWBook
   Dim nNewS
   Dim nIdx
   Dim sName
   Dim oSheet
 
   aNames = Array(   "Anna" , "AnnaPC"   _
                   , "Bob"  , "BobPC"    _
                   , "Carl" , "CarlPC"   _
                   , "Clara", "ClaraPC"  )
   sFSpec = ".\uc2.xls"
 
   Set oFS    = CreateObject( "Scripting.FileSystemObject" )
   sFSpec     = oFS.GetAbsolutePathName( sFSpec )
   Set oExcel = CreateObject( "Excel.Application" )
   If Not oFS.FileExists( sFSpec ) Then
      Set oWBook = oExcel.Workbooks.Add
      WScript.Echo "Create: ", sFSpec
      oWBook.SaveAs sFSpec
      WScript.Echo "Save:   ", sFSpec
   Else
      oExcel.Workbooks.Open sFSpec
      WScript.Echo "Open:   ", sFSpec
      Set oWBook = oExcel.Workbooks( 1 )
   End If
   WScript.Echo "WBook:  ", oWBook.Name
 
   nNewS = 0
   For nIdx = 0 To UBound( aNames ) Step 2
       sName      = aNames( nIdx + 0 ) + "_" + aNames( nIdx + 1 )
       Set oSheet = Nothing
       nNewS      = nNewS + 1
 On Error Resume Next
       Set oSheet = oWBook.Sheets( nNewS )
 On Error GoTo 0
       If oSheet Is Nothing Then
          Set oSheet = oWBook.Sheets.Add
          oSheet.Name = sName
          WScript.Echo "Sheet:  ", sName, "created"
       Else
          WScript.Echo "Sheet:  ", nNewS, "found"
          oSheet.Name = sName
          WScript.Echo "Sheet:  ", sName, "renamed"
       End If
       With oSheet
         .Cells( 1, 1 ).Value = aNames( nIdx  + 0 )
         .Cells( 2, 1 ).Value = aNames( nIdx  + 1 )
       End With
   Next
 
   oWBook.Save
   WScript.Echo "Save:   ", sFSpec
   oWBook.Close
   oExcel.Quit
 
   Set oSheet = Nothing
   Set oWBook = Nothing
   Set oExcel = Nothing
   Set oFS    = Nothing

should show, howto rename existing sheets and create new ones as needed - an look:
no sleep necessary!

Good luck!

(in reply to Snipah)

Post #: 9

RE: Renaming worksheets in Excel - 8/10/2006 11:47:32 PM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

Hey, I was down with the flu yesterday, so I wasn't around to work on the script. I've been through your script multiple times and am still unable to make mine work. This is really frustrating.

(in reply to ehvbs)

Post #: 10

RE: Renaming worksheets in Excel - 8/11/2006 12:05:34 AM

Snipah

Posts: 1343
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

is your current script updated on the above link? or else post it here....(easier) and let us use your script an try it out...and come back with 'better' alernatives

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to BDIntern)

Post #: 11

RE: Renaming worksheets in Excel - 8/11/2006 12:38:45 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

hmmm, I seem to have found the problem now just need to find out why it isnt working. I took out the on error resume next and I got the following error

object doesnt support this property or method: 'objworksheet.worksheets'

This happens on the lines

objWorksheet.worksheets(counter).Activate

objWorksheet.worksheets(counter).Name = OuName

the first 3 sheets (the ones that open with the workbook) are renamed as soon as I make one and it tries to rename I get that error. The input file is just a list of names greater then 3.

(in reply to Snipah)

Post #: 12

RE: Renaming worksheets in Excel - 8/11/2006 1:29:14 AM

BDIntern

Posts: 34
Score: 0
Joined: 5/19/2006
Status: offline

Figured it out! I was doing objWorksheet.worksheets(counter).Name = OuName and should have been doing objExcel.Worksheets(counter).name = OuName

(in reply to BDIntern)

Post #: 13

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Renaming worksheets in Excel

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts