Tag Archives: worksheet

VBA Run Macro on All Files in a Folder / All Worksheets in a Workbook

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

A very popular Excel automation scenario is the need to a VBA run macro on all files in a folder or running VBA on all Worksheets in an Excel Workbook. This is a very typical case where you process similar data dump files and want to extract data or transform the workbook. In this post I will provide ready code snippets to address these scenarios and walk you through what happens.

VBA Run Macro on All Files in a Folder

To run macro on all files in folder use the code snippet below. The code will do the following:

  • Open a VBA FileDialog in the current workbook path and ask for you to select a folder where all files are stored
  • It will open a separate Excel process (Application) and then open each file one by one
  • Replace the YOUR CODE HERE section with any code you want to run on every opened workbook
  • Each opened workbook will be closed w/o saving

To make it more simple currWb and currWS represent the ActiveWorkbook and ActiveWorksheet whereas wb represents the newly opened Workbook from the selected folder.

There is also built in simple progress tracking via the Application StatusBar.

VBA Run Macro on All Files in Subfolders

A scenario of the above case when you want to run a macro on all Excel files in a folder is also traversing all subfolders to run your macro. The below is an extension of the above and utilizes a slightly modified version of the TraversePath procedure from here.

The below is almost identical to the above, however, notice the global variable fileCollection. This will be used to first store all file identified in subfolders and only after used to run all macros on files stored in this VBA Collection.

Run VBA on All Worksheets

To run macro on all Sheets in Workbook you need to can use the code snippet below. Here is a walkthrough of the code:

  • Opens each worksheet in ActiveWorkbook that isn’t the ActiveSheet. This clause is to avoid running on Worksheet on which macro was activated assuming this is a working sheet, feel free to remove the If clause if needed.
  • Replace the YOUR CODE HERE section with any code you want to run on every opened Worksheet

How to unhide sheets in Excel? Unhide all Sheets in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

  1. Right click on a the Worksheets tab
  2. Click the Unhide button in the opened tab
  3. Select the Worksheet you want to unhide and click Ok

Unhide hidden Sheets in Excel

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

Unhide all Sheets in Excel using VBA
Unhide all Sheets in Excel using VBA

Below the same code as above but spread across multiple lines. Let us run through this:

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:
Hidden worksheets Excel

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:
Manage Sheets UserForm

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

When executed this is how it will look like:
Unhide Multiple Sheets in Excel

Excel VBA Last Row, Last Column, Last Cell

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.00 out of 5)
Loading...

Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

CONTENTS

VBA Last Row

The Last Row may as be interpreted as:

Last Row in a Column

vba last row
Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range

'Get Last Row with Data in Column
Debug.Print Range("A1").End(xlDown).Row 'Result: 5
Set lastRow = Range("A1").End(xlDown).EntireRow

'Get Last Cell with Data in Row
Dim lastRow as Range
Set lastRow = Range("A1").End(xlDown)

Last Row with Data in Worksheet

vba last used row
Last Used Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 

'Get Last Row with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
Set lastRow = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireRow

Last Row in Worksheet UsedRange

vba last used row usedrange
Last Used Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange
Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Column

The Last Column may as be interpreted as:

Last Column with Data in a Row

vba last column
Last Column in Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range

'Get Last Column with Data in Row
Debug.Print Range("A1").End(xlRight).Row 'Result: 5
Set lastColumn = Range("A1").End(xlRight).EntireColumn

'Get Last Cell with Data in Column
Dim lastColumn as Range
Set lastColumn = Range("A1").End(xlRight)

Last Column with Data in Worksheet

vba last used column
Last Column with Data in a Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Column with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 

'Get Last Column with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireColumn

Last Column in Worksheet UsedRange

vba last used column usedrange
Last Used Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange
Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Cell

The Last Cell may as be interpreted as:

Last Cell in a series of data

vba last cell in data series
Last Cell in data series

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range

'Get Last Cell in a series of data
Dim lastCell as Range
Set lastCell = Range("A1").End(xlRight).End(xlDown)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cells with Data in Worksheet

vba last cell in worksheet
Last Cell with data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Cell with Data in Worksheet using SpecialCells
Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

'Get Last Cell with Data in Worksheet using Find
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cell in Worksheet UsedRange

vba last cell in usedrange
Last Cell in UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange
Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA UsedRange

vba usedrange
The UsedRange in a Worksheet with not Values or Formulas

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL+SHIFT+END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Often bloated, large Excel files are effects of misuse of UsedRange. If you want to know more about reducing Excel file size read my post

Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet
Set ws = ActiveSheet
    
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column
Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column    

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2
Last Cell in UsedRange; Row: 5, Column: 6

First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column

Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc.

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets… and everything else you need to know about the VBA Worksheet in general.

ThisWorkbook vs ActiveWorkbook

Some Excel Worksheets
Some Excel Worksheets
Lets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short:

  • ThisWorkbook – refers to the Workbook in which the VBA macro is running
  • ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window

It is extra important to understand this difference and I encourage you to read my post on this topic first.

The Excel VBA Object Hierarchy

Excel vba object hierarchySecondly it makes sense to remind the Excel Object hierarchy.

At the top, at the root we have our Excel Application. The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks. Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets. Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas.

Accessing VBA Worksheets

Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:

ActiveWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ActiveWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = Sheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

ThisWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ThisWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Sheets(1) 'Get first Worksheet in ThisWorkbook
Set ws = ThisWorkbook.Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = ThisWorkbook.Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Worksheet VBA Name vs Excel Name

caption id=”attachment_11373″ align=”alignright” width=”213″]Worksheet VBA Name vs Excel Name Worksheet VBA Name vs Excel Name[/caption]When speaking about Worksheets in ThisWorkbook, the VBA Name of a Worksheet is not the same as the Excel Name of a Worksheet. Let us understand this different. On the right we have a screen from an example Workbook. The VBAName string is the VBA Name of our Worksheet, on the other hand the Excel Name string is our Excel Name. You can use both names to refer to the same Worksheet but in different ways.

Using the VBA Name of a Worksheet

Worksheet VBA Name
Worksheet VBA Name
We can refer to a VBA Worksheet by its VBA Name directly – just by typing it. This is very convenient and a good practice. That is because the VBA Name can’t be changed by a user by mistake from the level of Excel (not the VBE). Hence whatever name the user give from the Workbook level to your Worksheet – its VBA Name stays the same.

Using the Excel Name of a Worksheet

Referring to an Excel Worksheet from VBA is not a recommended practice. Below I am referring to the Worksheet I named Excel Name in my example above.

Dim ws as Worksheet

Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below:

Dim ws as Worksheet

'---Sheet by Excel Name in ActiveWorkbook---
Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook
Set ws = ActiveWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

'---Sheet by Excel Name in ThisWorkbook---
Set ws = ThisWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

The VBA ActiveSheet

As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it.

Selected vs Activated Worksheet, the differences:

  • Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets
  • ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks

From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:

Example 1: Explaining ActiveSheet

vba worksheets exampleLet us say we have 2 Workbooks open. One is Book1.xlsm and there other is Book2.xlsm.

Lets open VBE in Book1.xlsm. We will add the following piece of code:

Sub TestActiveSheet()
 MsgBox ActiveSheet.Name
End Sub

Now lets activate Book2.xlsm. Rename any Worksheet e.g. to MyActiveWorksheet. Next go to the Developer ribbon select Macros and run Books1.xlsm!TestActiveSheet. What happened? You probably got something like this:
vba activesheet
Although you ran a macro from Book1.xlsm, VBA considers always the top-most worksheet as the ActiveSheet.

Example 2: Explaining Selected vs ActiveSheet

Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:

Sub SelectedVSActive()
  Dim res As String, ws As Worksheet
  res = "Selected sheets:" & vbNewLine
  For Each ws In ActiveWindow.SelectedSheets
    res = res & ws.Name & vbNewLine
  Next ws
  MsgBox res & vbNewLine & "ActiveSheet:" & vbNewLine & ActiveSheet.Name
End Sub

Now do the following:

  • Activate workbook Book2.xlsm
  • Select a number of Worksheets (at least 2)
  • Run Book1.xlsm!SelectedVSActive macro

What happens? You should get something like this:
vba activesheet vs selected
You see that:

  • Only the top-most Worksheet is considered as the ActiveSheet
  • The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)

What happens if you change ActiveWindow to e.g. Windows(2)? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.

Activating VBA Worksheets

Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Activate

Activate Worksheet examples

A few examples below:

Sheets(1).Activate 'Activate first Worksheet in ActiveWorkbook
Sheet1.Activate 'Activate Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Activate 'Activate Excel named Worksheet in ActiveWorkbook

Selecting VBA Worksheets

Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.

Selecting a single VBA Worksheet

Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Select

Selecting a single Worksheet examples

A few examples below:

Sheets(1).Select 'Select first Worksheet in ActiveWorkbook
Sheet1.Select 'Select Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Select 'Select Excel named Worksheet in ActiveWorkbook

Selecting multiple VBA Worksheets

Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function:

Sheets(Array(1, 2)).Select 'Select first and second Worksheet in ActiveWorkbook
Sheets(Array("Named1", "Named2")).Select 'Select 2 named Worksheets in ActiveWorkbook

This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace. Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:

Sheets(1).Select 
Call Sheets(2).Select(False)
Call Sheets(3).Select(False)

This will select the first three Worksheets in our ActiveWorkbook.

Deleting VBA Worksheets

To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts

Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True

You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:

Application.DisplayAlerts = False
Sheets(Array(1, 2)).Delete
Application.DisplayAlerts = True

Copying VBA Worksheets

You can copy Worksheets within a Workbook or from other open Workbooks.

'Copy Sheet1 and paste before Sheet2
Worksheets("Sheet1").Copy Before:=Sheets("Sheet2")

'Copy Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Moving VBA Worksheets

You can move Worksheets within a Workbook or from other open Workbooks.

'Move Sheet1 and paste before Sheet2
Worksheets("Sheet1").Move Before:=Sheets("Sheet2")

'Move Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Move Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Other VBA Worksheets properties

Below are other VBA Worksheet properties worth mentioning:

Worksheet property Description Example
Calculate Calculates all dirty formulas on the current Worksheet
Dim ws as Worksheet
'...Set ws
ws.Calculate
Name Get or set the Excel Name of the Worksheet
Dim ws as Worksheet
'...Set ws
Debug.Print ws.Name 'Print Excel Name of Worksheet
ws.Name = "New name" 'Set new name for Worksheet
Visible Display, Hide or make Worksheet VeryHidden. Possible values:

  • xlSheetVisible
  • xlSheetVeryHidden
  • xlSheetHidden
Dim ws as Worksheet
'...Set ws
ws.Visible = xlSheetHidden 'Make ws Worksheet Hidden

To learn more read Unhide sheets in Excel / Unhide all Sheets in Excel using VBA.

Excel WEBSERVICE and FILTERXML functions explained

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

The Excel WEBSERVICE and Excel FILTERXML Worksheet functions can be used to pull Internet data from a URL into a cell in a Excel spreadsheet. While the first pulls raw HTML data the second allows you to filter XML formats.

Excel up till now has been mostly an offline application. Although, you can use VBA, PowerQuery or other similar data tools in Excel to gain access to Internet and Online data sets these could not have been easily used with the help of Third Party AddIns such as my Scrape HTML AddIn. With the introduction of the WEBSERVICE function we gain immediate and easy access to any REST WebAPI.

In today’s post I will show you several examples of how to use these functions in practice. And how to extract data from an XML REST WebApi.

How to use the Excel WEBSERVICE function

The WEBSERVICE function downloads the HTTP response of the provided URL.

excel webservice function
The Excel WEBSERVICE Worksheet function

Try running the Excel WEBSERVICE function on Google:

What do you get? If all goes well you should get the HTML response for the Google Web Page.
Now past the above formula to cell A1 and the below to cell A2:

The result of this Worksheet Excel Formula should be:

Using FILTERXML in tandem with WEBSERVICE

Another function introduced in Excel 2013 is the FILTERXML function. It is designed to work in tandem with the Excel WEBSERVICE function.

What does the FILTERXML function do?

The Excel FILTERXML Worksheet function parses a XML string (string containing XML document) and returns a single element (node or attribute) provided by a XPath.

excel filterxml
The Excel FILTERXML Worksheet Function

Lots of odd words there right? So let us start breaking it down. XML is markup language for encoding documents. As a matter of fact HTML is based on XML and share a lot of similarities. Unfortunately for us HTML does not need often have to be as strictly parsed/validated as an XML does. Although some HTML could pass as XML files – in fact many Websites don’t validate as XML.

Want to learn how to manage XML documents in VBA instead? Read my VBA XML Tutorial

Now what is XPath? XPath is a query language for selecting XML elements such as nodes and attributes. XPath works for XML and HTML.

How to use the FILTERXML function

Now that we know what the FILTERXML functions let’s use it on a simple XML file. W3Schools fortunately has a lot of simple XML examples – let us use a simple XML Food Menu:

Example: WEBSERVICE and FILTERXML function

filterxml and webservice example 1
Using WEBSERVICE and FILTERXML in tandem – Formulas

Example: WEBSERVICE and FILTERXML result

filterxml and webservice example 2
Using WEBSERVICE and FILTERXML in tandem – Result

Explanation

xml example
W3Schools XML Example Food Menu
Now to explain what happens above. The XML file includes a couple of nodes – each one nested within the previous one. We start with node which hosts nodes. Each food node represents a single item in the menu. A food node contains , and node – which describe each menu item.

Now the FILTERXML functions used the following XPath: //food/name. This translates to: take the first food tag and return the contents of it name node.

XPath is an easy query language to learn. I personally recommend the WSchools XPath tutorial

Scraping a whole XML document

Now the example above is fine when you need just a single node from your XML document URL. What if you want to scrape the whole contents of that XML? Thankfully we can combine the WEBSERVICE and FILTERXML functions with Array Formulas.

In the example below I will show you how to acquire all the names of the food items in the menu. You can use a similar technique to get other items.

Input the FILTERXML formula

Input the FILTERXML formula as shown below:

filterxml and webservice example 4

Drag the formula down

Drag the formula down to row 8:
filterxml and webservice example 5

Hit CTRL+SHIFT+ENTER

Hit the following key combo to create and Array Formula: CTRL+SHIFT+ENTER.
That is it. Now in each row you should see the name of a food item from the menu.

Don’t like using Array Functions? You can also use the XPath node index instead:

will return the name of the second food menu item. To replicate this across all items use this example:

Provide you entered this function in cell B2, simply drag it down – the items should automatically complete.

WEBSERVICE functions Restrictions

Now the WEBSERVICE function unfortunately has several restrictions that will cause the function to return a #VALUE! error instead of the string:

  • If you don’t have a working Internet connection (or you are working with a proxy server)
  • Incorrect arguments or URL address
  • If HTTP result is not valid or contains more than the cell limit of 32767 characters
  • URL is a string that contains more than the 2048 characters that are allowed for a GET HTTP request
  • Protocols that aren’t supported, such as ftp:// or file://

Excel WEBSERVICE summary

The WEBSERVICE and FILTERXML functions are a great step forward to enabling access to Internet resources. These still have unfortunately a lot of limits. Especially when most websites have HTML files that exceed the 32727 character limit and often don’t parse as XML files.

This is where my VBA Web Scraping Kit fills the gap together with my Scrape HTML AddIn. The Kit has all the Web Scraping scenarios I consider possible in Excel where as the Scrape HTML AddIn extends a little the constrains of the WEBSERVICE and the FILTERXML functions

Want to learn Web Scraping?

Not satisfied? Want to know more about Web Scraping in Excel using VBA? Read my zero-to-hero Web Scraping Tutorial.