Solved

Create and workbook and rename sheets name

Posted on 2014-02-23
12
326 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 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
wb_Source_01.Sheets("Sheet2").Name="Anything you want"
wb_Source_01.Sheets("Sheet1").Name="Something else you want"
0
 
LVL 14

Accepted Solution

by:
DrTribos earned 250 total points
Comment Utility
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 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
I reopen the file to copy containts from other workbook.

Thanks
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
Thanks a lot to both of you for your help.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
No worries, glad it helped.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help please. Change Picture in powerpoint is pale (not working) 6 71
Slide Show 5 69
Excel 2010 question 3 38
Excel If tests 10 53
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now