Improve company productivity with a Business Account.Sign Up

x
?
Solved

Create and workbook and rename sheets name

Posted on 2014-02-23
12
Medium Priority
?
354 Views
Last Modified: 2014-03-09
Hi Experts,

I have VBA code to create workbook:

Set wb_Source_01 = Workbooks.Add
wb_Source_01.SaveAs FileName
wb_Source_01.Close False

Set wb_Source_01 = xl.Workbooks.Open(FileName)

Set ws_Source_01 = wb_Source_01.Sheets("Sheet2")  
Set ws_Source_04 = wb_Source_01.Sheets("Sheet1")    

I want to rename the " Sheet2" as "Completed" and "Sheet1" as "InComplete"

Please advise me the VBA code to do that.


Your prompt response would be highly appreciated.

Thanks
0
Comment
Question by:alam747
  • 6
  • 4
  • 2
12 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 39881479
thisworkbook.Sheets(1).name = "myName" <-- if you want to add name directly OR


dim myName as String
myName = "someName"
thisworkbook.Sheets(1).name = myName   <-- if you assign your name to a variable
0
 

Author Comment

by:alam747
ID: 39881521
if I want to use the code I mentioned where I can add how to rename the sheet names.

Thanks
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 1000 total points
ID: 39881526
wb_Source_01.Sheets("Sheet2").Name="Anything you want"
wb_Source_01.Sheets("Sheet1").Name="Something else you want"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Accepted Solution

by:
DrTribos earned 1000 total points
ID: 39881539
I'm not sure what you code is actually doing... but here is something that is running from within MS Word and creates a new XL Workbook and names the sheet.

Sub tests()

Dim wb_Source_01 As Excel.Workbook
Dim ws_Source_01 As Excel.Worksheet
Dim filename As String

filename = ThisDocument.Path & Application.PathSeparator & "test.xls"
    
    Set wb_Source_01 = Workbooks.Add
    
    wb_Source_01.SaveAs filename
    wb_Source_01.Close False
    
    Set wb_Source_01 = Excel.Workbooks.Open(filename)
    
    Set ws_Source_01 = wb_Source_01.Sheets("Sheet2")
    wb_Source_01.Sheets(1).Name = "myName"
    wb_Source_01.SaveAs filename
    wb_Source_01.Close True
End Sub

Open in new window


You can expand to add the additional sheet.

I don't understand why you create, close then reopen the file...
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39881549
I guess to answer your question, you can rename the sheets immediately after creating the workbook.

You may also need to create a reference to MS Excel,  VBA Environment > Tools >  References > scroll down to Microsoft Excel #.# Object Library
0
 

Author Comment

by:alam747
ID: 39881551
I reopen the file to copy containts from other workbook.

Thanks
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39881553
In fact - I think you are best setting your source by the sheet index as that will (as I understand it) remain persistent independent of the sheet name.
0
 

Author Comment

by:alam747
ID: 39883311
while I do as
wb_Source_01.Sheets("Sheet2").Name="Anything you want"
wb_Source_01.Sheets("Sheet1").Name="Something else you want"

getting runtime error '424' Object required

Thanks
0
 
LVL 40
ID: 39883643
Is wb_Source_01 initialized before you run these 2 lines?

Did you Close wb_Source_01 before running these 2 lines?

Do you have 2 sheets named "Sheet2" and "Sheet1" in that Workbook?

There are the 3 reasons I see that could cause that error on these 2 lines. Please post the complete code, you might have put the lines in the wrong place.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39884838
Does the code that I posted above work, ie. does it create a new xls and rename the sheet?  

Did you set up the reference to the XL Library?
0
 

Author Closing Comment

by:alam747
ID: 39916735
Thanks a lot to both of you for your help.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39916764
No worries, glad it helped.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial summarizes the causes behind"an unknown error prevented access to the PST File”.  It also suggests the various solutions to fix the problem.
This is a comprehensive review of a bundled Toolkit designed for use by IT Professionals and End Users to help Microsoft Outlook fans manipulate Outlook files and repair some common problems. Enjoy...
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question