Posted by: yuvan004 | January 7, 2012

IMPORT EXCEL DATA COORDINATES IN QUANTUM GIS


This article outlines a step-by-step process to turn a spreadsheet of data associated with geographic points into a Shapefile or GeoJSON which can be imported into TileMill. Before we begin, you will need:

  • The latest version of QGIS installed.
  • A spreadsheet of data with latitude and longitude coordinates for all the rows that you wish to display as points on the map (pre-prepped sample csv is here)

Spreadsheet as comma-separated values

QuantumGIS can’t import native Excel or OpenDocument spreadsheet files, so you will need to make sure your data is exported to the plain-text comma-separated values (CSV) format.

 

However, QGIS has a bug for handling text that includes commas, so when we export we need to actually make this file TAB delimited (but we’ll still call it a csv).

Choose File > Save as with the file type of “Text CSV (.csv)” but also make sure to check the “Edit filter settings” to ensure that during the save you can choose to use TAB delimited rows in this menu:

 

Now you will see that if we view that .csv in a text editor the file uses tabs between columns and the only commas are inside of text values – so we should be safe!

Importing to QuantumGIS

In QGIS, pull up your “Plugins” menu and select “Manage Plugins”. Click on “Add Delimited Text Layer” and press OK. Going back to the “Plugins” menu, you’ll see “Delimited Text” is now an option in this pull-down (if not you’ll need to hunt for the icon which should be on the menu bar.

 

Click on the “Add Delimited Text Layer” entry in the plugins menu or in the icon menu. Then navigate to your csv and QGIS should automatically fill in a few values. Make sure to check “Comma” for the delimiter (or what ever you exported with) and the grid display at the bottom will provide instant feedback on the impact of your choice.

 

You will notice that QGIS has done some intelligent guessing to determine which columns contain your X and Y coordinates. Verify that the longitude values are in the “X-field” and the latitude values are in the “Y-field”. Click on OK and you should have a layer of points representing the latitude and longitude of all of your rows in your CSV.

Now you should have your points on the map. With this sample data a QGIS bug appears where the text still has quotes around it – hopefully that will be fixed in future QGIS versions:

From here, right click on the layer in the layers panel and click “Save As”, which brings you to this menu:

 

For format, choose either ESRI Shapefile or OGR GeoJSON. Name your file and specify its output location. Unless you’d like a different encoding or CRS, leave these alone. Click OK and your file will be saved. If you chose Shapefile, you will need to create a zip file of the component .shp, .dbf, .shx, and .prj files before bringing them into TileMill.

Shapefiles vs. Geojson

The two main vector formats TileMill supports are ESRI Shapefiles and GeoJSON files. Shapefiles tend to be smaller in file size and faster at rendering, especially if they are indexed. Shapefiles come on average with 4 to 5 different corresponding files that need to be kept together to implement the data they contain. A GeoJSON, by contrast, is always a single plain text file.

One limitation of shapefiles is that the .dbf file, which contains all of the text and numeric data associated with each object in the shapefile, can only handle cell values of limited size. So, if you have columns in your spreadsheet whose entries are sometimes very lengthy (i.e. a “Description” column containing a large amount of text), you may want to use GeoJSON, which can handle any amount of text in your cells. Otherwise, the shapefile will truncate some of your cells’ values when they reach its limit.

 

source:http://support.mapbox.com/mapping-101/using-quantumgis-converting-a-spreadsheetcoordinates-for-use-in-tilemill

Posted by: yuvan004 | December 16, 2011

INSERTING AND DELETING THE IMAGE IN EXCEL THROUGH VBA MACRO


INSERTION:

If you have image list in excel sheet you have to insert that image means you can use the following syntax

c38 = ThisWorkbook.Worksheets(“Input”).Cells(3, 69)

InsertPictureInRange “C:\images\” + c38 + “.jpg”, _
Range(“D216:F228”)

Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
‘ inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub
If Dir(PictureFileName) = “” Then Exit Sub
‘ import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
‘ determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left – .Left
h = .Offset(.Rows.Count, 0).Top – .Top
End With
‘ position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

This for deleting the image in particular range

Dim Sh As Shape
With Worksheets(“report”)
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, .Range(“b215:p296”)) Is Nothing Then
If Sh.Type = msoPicture Then Sh.Delete
End If
Next Sh
End With


முதுகுளத்தூர் பள்ளிவாசல் மேல்நிலைப்பள்ளியில் நடந்த கல்வி விழிப்புணர்வு நிகழ்ச்சியில் முன்னாள் குடியரசுத் தலைவர் அப்துல்கலாம் கலந்து கொண்டு சிறப்புரையாற்றினார்.

அவர் உரையில் இடம்பெற்ற மாணவர்க்களுக்கான பத்து உறுதிமொழிகள்:
1. நான், எனது வாழ்க்கையில் நல்லதொரு லட்சியத்தை மேற்கொள்வேன். நன்றாக உழைத்துப் படித்து என் வாழ்க்கையிலே மேற்கொண்ட லட்சியத்தை அடைய முற்படுவேன்.

2. நான், எனது விடுமுறை நாட்களில், எழுதப்படிக்கத் தெரியாத ஐந்து பேருக்காவது எழுதப்படிக்க கற்றுத்தருவேன்.

3. என் வீட்டிலோ அல்லது பள்ளியிலோ குறைந்தபட்சம் ஐந்து செடிகளையாவது நட்டு அதை பாதுகாத்து வளர்த்து மரமாக்குவேன்.

4. நான், எனது வீட்டை தூய்மையாக வைத்துக்கொள்வேன், எனது சுற்றுப்புறத்தை தூய்மையாக வைத்துக்கொள்வேன், எனது முதுகுளத்தூரை தூய்மையாக வைத்துக்கொள்வேன். எனது இந்த செயலால் என் தமிழ்நாடு தூய்மையாகும், இந்தியா தூய்மையாகும், மக்களின் மனமும் சுத்தமாகும், வாழ்வு சிறக்கும்.

5. மது, சூதாடுதல் மற்றும் போதைப்பழக்கங்களுக்கு ஆளாகித் துயருறும் ஐந்து பேரையாவது அதிலிருந்து மீட்டு நல்வழிப்படுத்த நான் முயல்வேன்.

6. நான், ஜாதியின் பெயராலோ, மதத்தின் பெயராலோ, மொழியின் பெயராலோ எந்தவித பாகுபாடும் பாராட்டாது எல்லோரிடமும் சமமாக நடந்து கொள்வேன்.

7. நான், வாழ்க்கையில் நேர்மையாக நடந்து கொண்டு ம்ற்றவர்களுக்கு ஒரு எடுத்துக்காட்டாக இருக்க முயல்வேன்.

8. நான், என் தாய் மற்றும் தாய்நாடு இரண்டையும் நேசித்து, பெண்குலத்திற்கு உரிய் மரியாதையையும், கண்ணியத்தையும் அளிப்பேன்.

9. நான், நாட்டில் அறிவு தீபத்தை ஏற்றி அணையா தீபமாக்ச் சுடர்விடச் செய்வேன்.

10. நமது தேசியக் கொடியை என் நெஞ்சத்தில் ஏந்தி நம் நாட்டிற்கு பெருமை சேர்ப்பேன்.

Posted by: yuvan004 | September 19, 2011

Creating VBA Macros to Manipulate Worksheets in Excel 2007


Office 2007

Summary: Learn how to write Visual Basic for Applications (VBA) macros in Microsoft Office Excel 2007 to automatically name and sort worksheets. In addition, learn how to create and name sets of worksheets based on data in the current workbook, and then save each new worksheet as a new workbook. (12 printed pages)

Mark Roberts, Microsoft Corporation

April 2009

Applies to: Microsoft Office Excel 2007

Contents

Macro?

This section is for Excel users with no knowledge of Excel VBA.

Excel VBA, which stands for Excel Visual Basic for Applications, is the name of the programming language of Microsoft Excel. With Excel VBA you can automate a task in Excel by writing a so called Macro. This can save you a lot of time! More importantly there are certain things you cannot do with Excel alone. Excel VBA allows you to do these things in Excel.

1 Create a Macro: To create a macro in Excel VBA, first activate Excel Visual Basic. Next, you can create a command button and assign a macro to the command button.

2 Excel Macro Recorder: The Excel Macro Recorder is a very useful tool included in Excel VBA. With the Excel Macro Recorder you can record a task you perform with Excel. Next, you can execute the task over and over with the click of a button which can save you a lot of time. This chapter explains the ins and outs of the Excel Macro Recorder.

Basics

This section explains the basics of Excel Visual Basic. It is good to know the basic terminology explained in this section before you start programming in Excel Visual Basic.

1 Macro Security: Setting up your macro security settings correctly is essential to protect yourself against potential viruses. Make sure your macro security settings are set up correctly so no harm can be done to your computer.

2 Visual Basic Editor: Learn how to launch the Visual Basic Editor and get the best configuration of the Project Explorer and the Code Window in your Excel Version. The Visual Basic Editor is the starting point for creating macros in Excel VBA, so it is important to get this configuration right.

3 Macro Comments: Add macro comments to your Excel VBA code and your code will be easier to read as program size increases.

4 MsgBox: The Message Box is a dialog box you can have appear to inform the users of your program.

5 Macro Errors: Dealing with VBA-errors can be quite a challenge. This chapter provides you with a simple tip to deal with these errors.

6 Debug Macros: Before you execute your VBA-code you can first debug your macro. This way most of the errors can be corrected before you execute your code.

7 Objects, Properties and Methods: In this chapter you will learn more about Excel VBA objects. An object has properties and methods. Excel Visual Basic is a semi-object oriented programming language. Learn more about the object hierarchy of Excel Visual Basic.

8 Workbook and Worksheet: In this chapter you will learn more about the Excel VBA Workbook and Excel VBA Worksheet object. You will see that the Worksheet and Workbook object have properties and methods as well, such as the count property which counts the number of active workbooks or worksheets. The Workbook and Worksheet object are commonly used in Excel VBA. They are very useful when your macro code has to be executed on different workbooks or worksheets.

9 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

Programming

This section is for users who want to get the most out of Excel VBA. Excel VBA Programming is not difficult, but you do need to know the keywords used in Excel VBA.

1 Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.

2 String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.

3 Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.

4 If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.

5 Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.

6 Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.

7 Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none? Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.

8 Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet. In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.

9 Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.

10 Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.

11 Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.

12 Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.

Controls

This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010, Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.

1 Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.

2 Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.

3 Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.

4 Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.

5 Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.

6 Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.

Overview

This article describes two scenarios that use VBA code to automate processes that manipulate worksheets in Microsoft Office Excel 2007.

  • In the first scenario, the developer wants each user to add a worksheet to the workbook and type a name for the worksheet in a specific cell. The developer writes code that automatically names the current worksheet by using the specified value and then sorts all of the worksheets in the workbook by name.
  • In the second scenario, the developer wants the user to type a list of region names in a column on a worksheet, plus additional information about each region. The developer writes a macro that copies that information and creates a new workbook file for each region.

Important:

To access the commands that you need to create VBA code, the Developer tab must be visible in the Excel 2007 Ribbon user interface. If the Developer tab is not visible, use the following steps to display it:

  1. Click the Office Button, and then click Excel Options.
  2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Automatically Naming and Sorting Worksheets

In this scenario, the developer wants each user to add a worksheet to the workbook, and then to have Excel automatically name and sort all of the worksheets in the workbook. If you want Excel to perform these actions automatically after a user enters the name of the new worksheet in a cell, you must write your VBA code in an event-handler subroutine. An event-handler subroutine is code that runs when the user performs a particular action in Excel, such as opening or saving a workbook, or entering values into worksheets. In the Excel VBA object model, each user action that can have code associated with it is called an event.

Tip:

If you search the Excel Developer Reference for “event,” you can view all the topics that describe the events that have associated VBA event-handler code. Use the following steps to perform the search:

  1. Click the Microsoft Office Excel Help button.
  2. Click the drop-down arrow next to the Search button.
  3. Click Developer Reference, type “event” in the search box, and then click Search.

The following sections describe how to decide which event to use for the event-handler subroutine for this scenario, and how to write the VBA code to name and sort worksheets.

Choosing the Right Event

To create the code for this scenario, you must write the VBA code directly in the Visual Basic Editor rather than record a macro. To open the Visual Basic Editor, on the Developer tab, click Visual Basic. You can start adding code by double-clicking one of the default worksheet names, such as Sheet1, or by double-clicking the ThisWorkbook module in the Project Explorer pane of the Visual Basic Editor. Figure 1 shows the Visual Basic Editor window with the Project Explorer pane in the upper-left corner.

Figure 1. Visual Basic Editor window

Initially, you might think that you should add your VBA code to one of the worksheets, and write the event handler subroutine for the Change event of the Worksheet object. If you do that, your code will run whenever the data in the specified target cell is changed, but it will run only when the user adds the name to the cell in that particular worksheet. To create an event-handler subroutine that will run for every worksheet in the workbook (including new worksheets), you must add your code to the ThisWorkbook module and use the SheetChange event of the Workbook object instead. Because the SheetChange event is associated with the Workbook object, an event handler associated with that event can be used to work with any worksheet in the entire workbook. To create an event handler for the SheetChange event, use the following procedure.

To add an event handler to the ThisWorkbook module

  1. On the Developer tab, click Visual Basic to open the Visual Basic Editor.
  2. In the Project Explorer, double-click ThisWorkbook.
  3. In the Object drop-down list (the list above the editing window on the left side that says (General)), select Workbook.

This automatically creates an event handler for the Open event; you must create an event handler for the SheetChange event instead.

  1. In the Procedure drop-down list (the list above the editing window on the right side that now says Open), select SheetChange.

The Visual Basic Editor window should now look like Figure 2.

Figure 2. Visual Basic Editor window with empty Worksheet_SheetChange event handler

The following sections describe how to write the VBA code for the SheetChange event handler.

Writing Code to Name a Worksheet

In this scenario, you want the user to type a name in a particular cell, and then use that value to automatically name the current worksheet. The property you use to set the name of a worksheet is the Name property of the Worksheet object.

If you record a macro and name a worksheet, and then open the macro code in the Visual Basic Editor, you will see something like the following code.

Sub Macro1()

Sheets(“Sheet1”).Select

Sheets(“Sheet1”).Name = “MySheet”

End Sub

This code selects and names a particular sheet (“Sheet1”) in the workbook Sheets collection, but the goal of this scenario is to name the active worksheet. To access the properties and methods associated with current worksheet from VBA code, you use the ActiveSheet property of the Workbook object. Additionally, the code that the macro generates is hard-coded to use the name that you typed while recording it (“MySheet” in the previous example), and this scenario will retrieve the name from the cell where the user typed that value. To specify a particular cell in the code, use the Range object. To retrieve the value from that cell, use the Value property of the Range object. For example, to assign the value that the user entered in cell B3 as the name of the worksheet, use the following code.

 

ActiveSheet.Name = Range(“B3”).Value

Now, you need to determine how to get the event handler for the SheetChange event to run this line of code when a user types a value into cell B3. The SheetChange event provides two parameters: the Sh parameter that provides a Worksheet object that represents the current worksheet, and the Target parameter that provides a Range object that specifies which range on the current worksheet has changed. To complete this part of the code, use the Target parameter in an If statement to conditionally run the code only when the value in cell B3 is changed, as shown in the following example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Range(“B3”) Then

ActiveSheet.Name = Range(“B3”).Value

End If

End Sub

At this point, the code will assign the value entered in cell B3 as the name of the worksheet. However, Excel will display the worksheets in the order that they were added to the workbook, so now, you must determine how to automatically sort the names of the worksheets in alphabetical order.

Writing Code to Sort the Worksheets

The code required to alphabetize the names of the worksheets consists of two nested For Each…Next statements that loop through the Worksheets collection. As it loops, the code compares the names of each worksheet, and swaps the worksheets, when necessary, by using the Move method of the Worksheet object. In computer science, this kind of sorting algorithm is called a bubble sort. It is not the most efficient way to sort values, but because this scenario does not sort a large number of items, it will work fine.

To organize the code in the VBA project, the sorting code is in its own subroutine named SortSheets that can be called from the code in the SheetChange event handler. To create a new subroutine in the Visual Basic Editor, type Public Sub followed by the name of the subroutine, and then press ENTER. The code for SortSheets subroutine looks like the following code example.

Public Sub SortSheets()

 

Dim currentUpdating As Boolean

currentUpdating = Application.ScreenUpdating

 

Application.ScreenUpdating = False

 

For Each xlSheet In ActiveWorkbook.Worksheets

For Each xlSheet2 In ActiveWorkbook.Worksheets

If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then

xlSheet2.Move before:=xlSheet

End If

Next xlSheet2

Next xlSheet

 

Application.ScreenUpdating = currentUpdating

 

End Sub

Notice that the condition for the If statement that performs the comparison of worksheet names in the inner For Each…Next loop converts both worksheet names to lowercase by using the VBA LCase function.

If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then

This is done because the comparison operation is case-sensitive. Failing to convert the names to the same case will cause strings with uppercase characters such as “Tom” or “TOM” to be sorted before “harry.”

By default, Excel VBA code updates the screen every time it makes a change. Because the sorting code makes multiple passes to sort each worksheet, the screen will flash as the code compares and moves each worksheet. This flashing can be distracting to users and slow down the code as users add more worksheets to the workbook. To eliminate the flashing and speed up execution of the code, the ScreenUpdating property of the Application object is set to False before the sorting operation is started. After the sorting operation is completed, the ScreenUpdating property is set back to the value stored in the currentUpdating variable to restore screen updating to the state it was in when the SortSheets subroutine was called.

To pull all of this code together, modify the original SheetChange event handler to call the SortSheets subroutine after the name of a new or changed worksheet is assigned. The final code for this scenario looks like the following code example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Range(“B3”) Then

ActiveSheet.Name = Range(“B3”).Value

Call SortSheets

Worksheets(Sh.Name).Activate

End If

End Sub

The new line of code Call SortSheets calls the SortSheets subroutine to automatically sort the worksheets after the name or the worksheet is assigned or changed. The line of code following that, Worksheets(Sh.Name).Activate, calls the Activate method of the Worksheet object to ensure that the worksheet that was current at the start of the sorting operation is active when sorting is completed.

Automatically Creating Workbook Files from Worksheet Data

In this scenario, the spreadsheet developer wants to copy a list of region names in a column on a master worksheet, including additional information about each region, and then run a macro that creates a new workbook file for each region. The coding strategy is to create a new worksheet in the current workbook for each region on the master worksheet, and then to save each of those worksheets as a workbook file.

The code for this solution assumes the following:

  • The master worksheet that contains the list of region names is named REGION SHEET.
  • The first three rows of the master worksheet contain boilerplate information that will be copied to each new workbook.
  • The list of region names is in column B of the master worksheet, and the first name in the list is in cell B4.
  • The row for each region contains additional information that will be copied to the new workbook for that region.

A simplified example of the data in the REGION SHEET worksheet looks like the following table.

Figure 3. Sample REGION SHEET data

Running the macro on this data should create four workbooks named North.xlsx, South.xlsx, East.xlsx, and West.xlsx. Each workbook should contain the first three rows of boilerplate data, followed by the row of information for that region.

Note:

The example shows only four items in the row for each region (region name, owner name, and their associated labels). Because the macro code is designed to copy the entire row, you can include additional information about the region in the row. Additionally, the code is designed to accommodate a variable number of region names, so the user can enter as many region names as necessary starting at cell B4.

The VBA code consists of three parts:

  • A macro to create a new worksheet for each region listed in column B.
  • A function in the macro that checks whether a worksheet with the same name exists before creating a new one.
  • A function in the macro that saves a region worksheet as a new workbook by using the region name as the file name.

Writing Code to Create New Worksheets

The basic strategy is to create a new worksheet for a region name, copy the data for that region to the new worksheet, and then save the new worksheet as a new workbook. When the new workbook is saved, the worksheet used to create that workbook is deleted. The following code example shows the entire CreateWorkbooks macro that creates new workbooks based on the contents of the REGION SHEET worksheet.

Sub CreateWorkbooks()

Dim newSheet As Worksheet, regionSheet As Worksheet

Dim cell As Object

Dim regionRange As String

 

Set regionSheet = Sheets(“REGION SHEET”)

 

‘ Turn off screen updating to increase performance.

Application.ScreenUpdating = False

 

‘ Build a string that specifies the cells in column B that

‘ contain region names starting from cell B4.

regionRange = “B4:” & regionSheet.Range(“B4”).End(xlDown).Address

 

For Each cell In regionSheet.Range(regionRange)

If SheetExists(cell.Value) = False Then

‘ Add a new worksheet.

Sheets.Add After:=Sheets(Sheets.Count)

‘ Set newSheet variable to the new worksheet.

Set newSheet = ActiveSheet

‘ Copy boilerplate data from first three rows

‘ of the master worksheet to the range starting at

‘ A1 in the new sheet.

regionSheet.Range(“A1:A3”).EntireRow.Copy newSheet.Range(“A1”)

‘ Copy and paste the column widths to the new sheet.

regionSheet.Range(“A1:A3”).EntireRow.Copy

newSheet.Range(“A1”).PasteSpecial xlPasteColumnWidths

‘ Copy the entire row for the current region and

‘ paste starting at cell A4 in the new sheet.

cell.EntireRow.Copy newSheet.Range(“A4”)

‘ Name the new sheet.

newSheet.Name = cell.Value

‘ Call the SaveWorkbook function to save the current

‘ worksheet as a new workbook file.

SaveWorkbook (cell.Value)

‘ Turn off alerts, and then delete the new worksheet

‘ from the current workbook.

Application.DisplayAlerts = False

newSheet.Delete

‘ Turn alerts back on.

Application.DisplayAlerts = True

End If

Next Cell

 

‘ Notify the user that the process is complete.

MsgBox “All workbooks have been created successfully”

 

‘ Turn screen updating back on.

Application.ScreenUpdating = True

 

End Sub

The first several lines declare variables that are used in the remaining lines of code to work with elements in the workbook. For example, the code uses the regionSheet variable to work with the REGION SHEET worksheet that contains the seed data for the new workbooks.

The following line of code creates a string that specifies the range of cells in column B that contain region names, and then assigns that value to the regionRange variable.

regionRange = “B4:” & regionSheet.Range(“B4”).End(xlDown).Address

The code that follows the & operator identifies the last cell in column B that contains a region name and returns its address. This enables you to add as many rows for region names as you need in column B, and then be sure that the code will create a workbook for every row that contains data. The code identifies the last cell that contains data by specifying the first cell that contains data, Range(“B4”), and then calls the End property of the Range object with the xlDown enumeration to move the selection to the last cell in column B that contains data, returning a Range object that represents that cell. This is equivalent to selecting cell B4 in the worksheet and then pressing END+DOWN ARROW. For the sample data at the beginning of this section, this operation will move the selection to cell B7 (which contains the region name “WEST”). The code then uses the Address property of the Range object to return the cell’s address.

The For Each…Next loop that follows then loops through each region name in the range specified by the regionRange variable to create a worksheet for each region. Before the macro attempts to create a new sheet, the If statement within the loop (If SheetExists(cell.Value) = False Then) passes the region’s name to the SheetExists function to check whether a worksheet with the region’s name already exists in the workbook. The code for the SheetExists function is another loop that checks each of the worksheets in the workbook’s Sheets collection, and returns True if it finds an existing worksheet, or False, if it does not. The following example shows the code for the SheetExists function.

Function SheetExists(sheetName As String)

Dim sheet As Worksheet

For Each sheet In Sheets

If sheet.Name = sheetName Then

SheetExists = True

Exit Function

Else

SheetExists = False

End If

Next

End Function

The first line of code after the SheetExists check adds a new worksheet after the last sheet in the workbook’s collection by using the Add method of the Sheets collection.

Sheets.Add After:=Sheets(Sheets.Count)

The remaining code in the CreateSheets macro calls the EntireRow property of the Range object to select the specified ranges of rows from the REGION SHEET worksheet, followed by a call to the Copy method to copy those rows to the new worksheet. Looking at the code, you might wonder why the Copy method is applied twice to the three rows of boilerplate information.

regionSheet.Range(“A1:A3”).EntireRow.Copy newSheet.Range(“A1”)

 

regionSheet.Range(“A1:A3”).EntireRow.Copy

newSheet.Range(“A1”).PasteSpecial xlPasteColumnWidths

 

This is done to preserve the column widths for wider cells in the boilerplate information. The first Copy method operation copies all of the data from the A1:A3 range in the REGION SHEET worksheet to the destination in the new worksheet, but using that operation alone does not preserve column width information. The second call to the Copy method does not pass a value for the Destination parameter, which causes the Copy method to copy the range data to the clipboard. The last line of code calls the PasteSpecial method with the xlPasteColumnWidths enumeration to paste the column widths to the destination in the new worksheet.

The next two lines of code name the new worksheet by using the name of the region, and then call the SaveWorkbook function to save the new worksheet as a new workbook file as shown in the following example.

‘ Name the new sheet.

newSheet.Name = cell.Value

‘ Call the SaveWorkbook function to save the current

‘ worksheet as a new workbook file.

SaveWorkbook (cell.Value)

 

The next section discusses how to write the code for the SaveWorkbook function.

The final lines of code inside the If statement delete the new worksheet after it has been saved as a new workbook by using the Delete method of the Worksheet object, as shown in the following example.

‘ Turn off alerts, and then delete the new worksheet

‘ from the current workbook.

Application.DisplayAlerts = False

newSheet.Delete

‘ Turn alerts back on.

Application.DisplayAlerts = True

Note that before calling the Delete method, the DisplayAlerts property of the Application object is set to False. That setting suppresses the confirmation dialog box that Excel displays when a user attempts to delete a worksheet that contains data.

The final lines of the CreateWorkbooks macro display a message box to notify the user that the process is complete by using the MsgBox function, and then turn screen updating back on as shown in the following example.

‘ Notify the user that the process is complete.

MsgBox “All workbooks have been created successfully”

 

‘ Turn screen updating back on.

Application.ScreenUpdating = True

 

Writing the Code to Create New Workbooks

The following example shows the code for the SaveWorkbook function.

Function SaveWorkbook(workbookName As String)

Dim filePath As String

 

filePath = “C:\Region Sheets\” & workbookName & “.xlsx”

Sheets(workbookName).Copy

ActiveWorkbook.SaveAs Filename:=filePath

ActiveWorkbook.Close

 

End Function

Most of the code in this function is easy to understand. The code uses the filePath variable to specify the path and file name for the new workbook (using the name of the region as the file name). The code example assumes that you have a folder named C:\Region Sheets, but you can change the code to use any existing location.

The effect of the following line of code is less obvious.

Sheets(workbookName).Copy

This line of code uses the Copy method of the Sheets collection. When the Copy method is called without specifying either of the optional Before or After parameters, it copies the worksheet into a new workbook in memory rather than to another location in the collection of worksheets in the current workbook. The remaining two lines of code use the ActiveWorkbook property of the Application object to access the new workbook, and then call the SaveAs and Close methods of the Workbook object to save and close it.

Conclusion

In this article, you learn how to write two Visual Basic for Applications (VBA) macro solutions that automate operations on worksheets in Excel 2007. The first solution automates the process of naming and sorting worksheets in the current workbook. The second solution automates the process of creating and naming sets of worksheets based on data in the current workbook, and then saves each new worksheet as a new workbook file.

Posted by: yuvan004 | April 6, 2011

21 Effective Quotation of Swami Vivekananda


1. If the mind is intensely eager, everything can be accomplished—mountains can be crumbled into atoms.

2. Take up one idea. Make that one idea your life – think of it, dream of it, live on idea. Let the brain, muscles, nerves, every part of your body, be full of that idea, and just leave every other idea alone. This is the way to success.

3. Come out into the universe of Light. Everything in the universe is yours, stretch out your arms and embrace it with love. If you every felt you wanted to do that, you have felt God.

4. All knowledge that the world has ever received comes from the mind; the infinite library of the universe is in our own mind.

5. Stand up, be bold, be strong. Take the whole responsibility on your own shoulders, and know that you are the creator of your own destiny. All the strength and succor you want is within yourself. Therefore make your own future.  6. There is no help for you outside of yourself; you are the creator of the universe. Like the silkworm you have built a cocoon around yourself…. Burst your own cocoon and come out aw the beautiful butterfly, as the free soul. Then alone you will see Truth.

7. It is our own mental attitude which makes the world what it is for us. Our thought make things beautiful, our thoughts make things ugly. The whole world is in our own minds. Learn to see things in the proper light. First, believe in this world, that there is meaning behind everything. Everything in the world is good, is holy and beautiful. If you see something evil, think that you do not understand it in the right light. Throw the burden on yourselves!

8. Hold to the idea, “I am not the mind, I see that I am thinking, I am watching my mind act,” and each day the identification of yourself with thoughts and feelings will grow less, until at last you can entirely separate yourself from the mind and actually know it to be apart from yourself.

9. All love is expansion, all selfishness is contraction. Love is therefore the only law of life. He who loves lives, he who is selfish is dying. Therefore love for love’s sake, because it is law of life, just as you breathe to live.

10. Our duty is to encourage every one in his struggle to live up to his own highest idea, and strive at the same time to make the ideal as near as possible to the Truth.

11. Even the greatest fool can accomplish a task if it were after his or her heart. But the intelligent ones are those who can convert every work into one that suits their taste.

12. Condemn none: if you can stretch out a helping hand, do so. If you cannot, fold your hands, bless your brothers and let them go their own way.

13. Each work has to pass through these stages—ridicule, opposition, and then acceptance. Those who think ahead of their time are sure to be misunderstood.

14. If you think that you are bound, you remain bound; you make your own bondage. If you know that you are free, you are free this moment. This is knowledge, knowledge of freedom. Freedom is the goal of all nature.

15. As long as we believe ourselves to be even the least different from God, fear remains with us; but when we know ourselves to be the One, fear goes; of what can we be afraid?

16. Your Atman is the support of the universe—whose support do you stand in need of? Wait with patience and love and strength. If helpers are not ready now, they will come in time. Why should we be in a hurry? The real working force of all great work is in its almost unperceived beginnings.

17. Learning and wisdom are superfluities, the surface glitter merely, but it is the heart that is the seat of all power. It is not in the brain but in the heart that the Atman, possessed of knowledge, power, and activity, has its seat.

18. Understanding human nature is the highest knowledge, and only by knowing it can we know God? It is also a fact that the knowledge of God is the highest knowledge, and only by knowing God can we understand human nature

19. Purity, patience, and perseverance are the three essentials to success and, above all, love.

20. If you want to have life, you have to die every moment for it. Life and death are only different expressions of the same thing looked at from different standpoints; they are the falling and the rising of the same wave, and the two form one whole.

21. Each soul is potentially divine. The goal is to manifest this divinity within by controlling nature, external and internal. Do this either by work, or worship or psychic control or philosophy – by one or more or all of these and be free.

« Newer Posts - Older Posts »

Categories

%d bloggers like this: