How to show the Developer tab in Excel 2013

                     This post is for people who are new to VBA. If you are thinking of creating macros or writing VBA scripts then this should be the first step you should take.
When you open your MS Excel application, you will see that the Developer tab is not displayed by default. So you need to add Developer tab to the ribbon before write/read macros, use ActiveX/Form controls. This post will explain how to add this Developer tab step by step.

First open the excel application.

Then click on the blank workbook. This will generate a new workbook.

Once the new workbook created you can see that there are tabs like File, Home, Insert , Page Layout etc. But there is no Developer tab. So now click on the File tab. I have marked it by a red circle in the above image.

Then click on the options button. Once you click the button, Excel options dialog box will be opened.

On that dialog box, choose Customize Ribbon button.

Then you will see list of Main tabs in the right hand side of this dialog box. All the tabs are selected except Developer. Select it as well and click on OK button.

Developer tab will appear at the end.

Now you can run and record macros.

Getting started with Visual Basic Editor in Excel

In my earlier posts, I have explained lot of things on how to automate your excel workbooks with VBA. Also I explained how to do data mining from websites and download files automatically using VBA. However if you are new to Excel VBA,  you may probably don't know how to get Visual Basic Editor in Excel 2013. So today I will show you how to open Visual Basic Editor in your Excel application and give some brief introduction. First open an excel workbook where you want to develop your VBA.

Then click on the "DEVELOPER" tab. You will see several categories like Code, Add-Ins etc under that tab. Go to the "Code" category and click on the "Visual Basic" Icon

Once you click on that icon the Visual Basic Editor will be open up.

There is an alternative way to go to the Visual Basic Editor very quickly. It is using short cut keys. Once you open the workbook just press Alt+F11. Visual basic editor will be open in the excel application.

So now I will explain few things about Visual Basic Editor. You can find the "Menu Bar" in the top.

You can see the "Tool Bar" below that.

As you can see there are two other windows open in the Visual basic editor. Top one is called "Project Explorer window"

Other one is called "Properties Window"

When you developing applications you may need an another important window.  It is "Immediate" window. Just click on the "View" menu of the Menu bar. You will see a drop down list like below.

Then click on the Immediate Window. Immediate Window will be open at the bottom of the Visual basic editor. Also you can use short cut keys Ctrl+G to open that window.

This can be use for debugging the code.

Check whether folder exist and create if it is not

                We can use VB scripts to manage our data. In this kind of applications we often need to get data from folders as well as save data to folders. This data may in any form like text files, XML files, Excel files etc. Some of our folders contains sub folders. Those sub folders can even have their sub folders. So a particular folder can have folders inside them in different levels. If we know the structure of those folders we can even communicate with folder which are located inside several folders.

Below code is a similar example used in a excel file. It check whether there is a folder call "Image Files" in the folder where the excel file is located. If there is no folder, It creates a one with that. If folder exist it tells that folder is exists.

If Dir(ThisWorkbook.Path & "/Image Files/", vbDirectory) = "" Then     MkDir ThisWorkbook.Path & "/Image Files/"   Else     MsgBox "Directory Exists", vbInformation, "" End If

Excel 2013 Pivot tables

                               Today I'm going to teach you how to create pivot tables in Excel 2013. Pivot tables are powerful features of Microsoft Excel. They were continually developed in recent versions. First I will explain you what are the situations you need pivot tables. We normally use pivot tables for data related to transactions. There is no necessarily to have some kind of dependency among these items. But they are in similar measurements and have certain properties in common.

                                The case study I'm going to looking at is some what straight forward. It is a record of a company which has few branches Island wide. This data is a record of individual data of sales for nearly ten months of time. So my workbook contain thousands of data. Below you can see the part of my data set.

                           
                            Let's take row 3 as an example. In that row first value (In cell A3) is the date of the transaction. Next one is the branch where transaction took place. Third one is the value. And fourth one is the category of the sale. So here it is Food. It is not compulsory to data to be always sales to create pivot tables, Only it needs to be something measurable.

                            If you have very large amount of data, it is very difficult to analyze and present them. Consider above data set. You can analyze them in several ways. You can plot the total sales against the time. Also you can use different time frames like days, weeks and months. Also you can plot data of each and every branch against the time to see the growth of the sales in individual branches. So you can see that we can plot these data in various ways to suit to our requirement. We can  compare branches each other or we can compare categories each other. So you can see that we can analyze these data in many ways by changing the axes of charts time to time. To do that we need pivot tables and charts. I will explain about pivot charts in another post.

                           You need to know few things about Excel pivot tables before proceed further. One important thing is if you have created pivot tables with Excel 2003, please not that they are not compatible with higher versions of Excel. So it is advisable to find raw data you use to create those pivot tables in Excel 2003 and use them to create new pivot tables in Excel 2013. However newer versions of Excel pivot tables have higher level of compatibility. If you have created your pivot tables earlier with Excel 2007 or Excel 2010 you can use them in Excel 2013 without any problem. Also you can use external data to create pivot tables. So it is not compulsory to have data in the same work book where the pivot tables are. An another important thing. You need to have all the transactions in rows. You can't have the transactions in columns. If your transactions are in columns you need to somehow transpose them to rows as shown in above data set. Also it is advisable to have one transaction in one row. There should not be any blank columns or blank rows. And each column should have a unique title. And if your data set contain numeric values don't leave empty cells. Put zero instead. Check whether your data set fulfills all those requirements before start creating a pivot tables and pivot charts.

                           My data set fulfills all the requirements. So now let's start creating our pivot table. In Excel 2013 there are two method to create a pivot tables. I will explain both these methods. First we will look at quick method. Click in a cell inside the data range you use to create the pivot table. Then press Ctrl+asterisk(*). So you will see that all data is selected automatically.

                       Once all the data is selected you will see an icon call Quick Analysis icon. (Please see the bottom right hand corner of the above image.) You can use this icon to quickly analyse your data. Just click on that icon. You will get a menu bar like below.

                         It contains several menus like Formatting, Charts, Totals, Tables and Spark lines. So let's click on the Tables menu.

                          Under that menu you can find Table, two pivot table options and More option. If you take cursor on top of each icon it will show you a live preview. So I will click on first pivot table option. With that click Excel 2013 will create a new sheet like below.

So now you can see that the pivot table is created in the left hand side of the new sheet. This is a quick way of a creating a pivot table. In that method we created the pivot table for a data range. But when you create a pivot table it is better to create it on a table rather than a range of data. Because when time goes you may need to add new data to the data set. So if you have used a table it is much easier to update your pivot table later on. In this second method I will show you how you can create a pivot table after converting your data set to a table.
First click in a cell within the data range. Then go to "Insert" menu and click on "Table".

Once click on the "Table" you will get the "Create Table" dialog box. Excel also suggest a boundary for your data as well.

You can change that boundary to suit to your requirement. Define boundary of your table and click OK. With that click the table will be created and it will be selected. In this point simply give your table a name. In below image I have marked the place with a red circle where you can specify the name of your table.

So I just name it as SalesData. Next click inside your table and go to "Insert" menu and then click on "Pivot table"

Once you click on the pivot table you will get the "Create pivot table" dialog box

Here you can see that my table has automatically selected. It is because I have click inside my table before click pivot table. Also here you can decide where you need your pivot table to be created. Either in the sheet where your raw data are or in a new sheet. After making your selections click on the OK button.To give much space to data analyzing area, I choose new worksheet option.

Above is the result I got. As you can see this method is little bit different to the quick pivot table creation method. Because in that method we got pivot table right away. In this method we have got a some kind of place holder for our pivot table. Actual pivot table will be created once we select the fields from the right side panel.  The bottom part of the right panel has separated to four areas. Filters, Columns, Rows and Values. Here you will notice that when you select text field or date field they will automatically listed under Rows area. And if you select a field which has numeric values it will automatically listed in the Value area. So now I will select Branch field and value field.

So now let's look at the Pivot table we have created. There are branches in the column A and Sum of values in column B. We can control the pivot table by the panel shown in the left hand side. You can see in that panel only check boxes of Branch and Value are checked. As a result only those two fields are shown in the pivot table. So let's click on the check box of the category field and see what will happen.

As you can see, now my pivot table has expanded. Now it shows both the Branch and Category. So in Rows area the category label is placed under Branch label. According to that, in the pivot table categories are listed under each branch. Think you need to list branches under each category. Then you can see how sales are varied for different branches for different categories. You can simply do this. what you need to do is drag Category label at the Rows area to top of the Branch label of same area. Here is the result you will get.

Now all the branches are listed under each category.So now let's move the category label to columns area and see what will happen.

Now it has also break down by categories as well. So each column represent a unique category. In addition to that you can filter column labels as you like. Not only that you can even pivot two fields. So let's interchange Branch and Category in the bottom part of the right panel and see what will happen.

So you can see how the Branches and Categories swapped with that action. This flexibility of the Excel pivot tables is a key reason to it's popularity.

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc.

So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL”

Sub DownloadFileFromURL()

End Sub

First we need to declare a few variables.

Dim FileUrl As String
Dim objXmlHttpReq As Object
Dim objStream As Object

Next assign the URL of the file to the FileUrl variables

FileUrl = "URL of your file"

For an example if your file URL is https://www.example.com/images/chart.jpg then you can assign it to the variable as follows.

FileUrl = "https://www.example.com/images/chart.jpg"

Next step is to create a XMLHttp object to request the data from the server.

Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")

Call the open method followed by the send method.

objXmlHttpReq.Open "GET", FileUrl, False, "username", "password"
objXmlHttpReq.send

Then check the status. If it equals 200 (OK), we can move to the next step.

If objXmlHttpReq.Status = 200 Then

Now we need to create an ADODB.Stream object to write the data(received) to a file.

Set objStream = CreateObject("ADODB.Stream")

Stream in programming means flow of data. In programming data is what flows between processors and input or output devices. Check this link to see all the Properties, Methods and Events of Stream object.

Stream Object Properties, Methods, and Events

But we need to use only a few of them here.

objStream.Open
objStream.Type = 1
objStream.Write objXmlHttpReq.responseBody
objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2
objStream.Close

In the SaveToFile method I have entered 2 as SaveOptions. So the program will overwrite the file with the data from the stream if the file already exists. If you don’t want to overwrite the existing file then replace it with 1.

objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 1

Below is the full code which you can use to download a file through a URL.

Sub DownloadFileFromURL()

     Dim FileUrl As String
     Dim objXmlHttpReq As Object
     Dim objStream As Object

     FileUrl = "URL of your file"

     'example
     'FileUrl = "https://www.example.com/images/chart.jpg"

     Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")
     objXmlHttpReq.Open "GET", FileUrl, False, "username", "password"
     objXmlHttpReq.send

     If objXmlHttpReq.Status = 200 Then
          Set objStream = CreateObject("ADODB.Stream")
          objStream.Open
          objStream.Type = 1
          objStream.Write objXmlHttpReq.responseBody
          objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2
          objStream.Close
     End If

End Sub

In the above example I showed how to download a .jpg file. But you can use this method to download other file types as well. Remember to change the file extension depending on the type of the file you download. For example if you are downloading a CSV file then the SaveToFile method should be changed as follows.

objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2

How to add @ or = sign

            Hope you already know that you can enter any formula to excel cell by entering formula with equal (=) sign. This may have helped you to automate your spread sheets very easily.

But have you ever needed to put = sign or @ sign to a excel cell as a first character. (Not to enter a formula) .
If you enter equal sign first and then if you enter some thing which is not a formula then you will get an error message like below.

 #NAME?

And if you enter @ sign first then you will get following error message.

So I will show you how to enter something which is not a formula to a cell with = sign first. Or how to enter @ sign first. Solution is easy. You just need to put apostrophe ( ' ) before the = or @.

Below image will show how it works. You can see @Head at cell. And in formula bar you can see how it has entered using apostrophe.

Web Scraping - Complete Guide

In this lesson you will learn all the essentials of the VBA Web scraping. And at the end of the lesson, you will be able to write your first web scraping program to navigate to a webpage you like and collect the data. Also there are lots of other articles in this website which explain about various other techniques you can use in vba web scraping. So please check the link at the end of this lesson. You will find lots of helpful resources to develop efficient and robust VBA web scraping programs.

Today, websites play major role in our day today lives. Some websites are built to show particular information to the visitors. These websites usually contain only static web pages. But some websites can interact with their visitors. Amount of data included in the websites vary from one website to another. Some websites contain a little amount of data and some contain a large amount. Sometimes business organizations and individuals want to extract that information for their needs. This is where the technology called Web scraping comes into action. VBA Web scraping is a popular technique used to collect data automatically from websites to Excel.

Visual Basic for Applications (VBA) makes web scraping a powerful tool for extracting data from websites. VBA, integrated with Microsoft Excel, enables users to automate the retrieval of information, transforming tedious tasks into efficient processes. With a few lines of code, VBA can navigate web pages, interact with elements, and collect data systematically. This automation is particularly valuable for analysts, researchers, or businesses requiring up-to-date information for decision-making.

Here are some uses of web scraping

• To compare prices
• To monitor weather data
• To detect changes made to websites
• For research purposes
• To collect various other information

Complexity of a VBA web scraping program depends on the website's coding structure and the type of data we are going to collect.

So now let’s look at how we can create a simple VBA web scraping program which can open an internet explorer window and navigate to a particular webpage.

Before writing the VBA Web scraping program, we need to enable the developer tab and set up the Visual basic editor for coding.

Follow these simple steps to enable the developer tab if you don’t have it in your Excel ribbon.

1. Launch Microsoft Excel on your computer.
2. Click on the "File" tab in the ribbon and select "Options" at the bottom.
3. In the Excel Options window, choose "Customize Ribbon" from the left sidebar.
4. On the right side, you will see the main tabs. Check the box next to "Developer" to enable it, and then click "OK."
5. Go to the Developer tab on the ribbon, and click on "Visual Basic" to open the Visual Basic for Applications (VBA) Editor.

You can find a more detailed explanation from this post.
How to show the Developer tab in Excel

Now you have enabled the developer tab in Excel. The Developer tab provides access to various tools, including the Visual Basic Editor, allowing you to create powerful automation scripts and macros. So now we need to insert a module to write our VBA web scraping program.

To insert a module to our project in Excel, follow these simple steps:

1. Go to the Developer tab on the ribbon.
2. Click on "Visual Basic" to open the VBA Editor.
3. In the Project Explorer window on the left, click on the workbook(to select it if you have multiple workbooks open) for which you want to insert a module.
4. From the menu, select "Insert" and then choose "Module."
5. A new module will appear under the workbook in the Project Explorer.
6. Now double-click on the module to open it in the central editing area.

VBA web scraping programs use internet explorers to collect the data. But you don’t need to configure anything manually for the Internet explorer to do this. Internet explorer will behave according to the code written in the VBA web scraping program. When developing VBA web scraping software, our first step is to create a subroutine to write the code. Let’s name the subroutine as MyFirstWebScrapingProgram.

Sub MyFirstWebScrapingProgram()


End Sub

Then, first of all, we need to define a variable of type object and then assign an internet explorer object to it. You can use the below line of code to create that object.

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

Then set the following properties to the created object. You can change them according to your requirements. First two properties(Top and Left) decide the position of the internet explorer window. Next two properties(Width and Height) will decide the size of the window.

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

Following property enables the visibility of the internet explorer window. You can set it to false if you want to run the program in the background. If you set it to false, the user won’t see how the data is collected using the internet explorer. He/she will only see how the data is filled to the Excel file.

objIE.Visible = True

So now we have created the internet explorer object and set the relevant properties. Next let's see how to navigate to a webpage. You can use the below VBA statement to navigate to the webpage you want.

objIE.Navigate ("http://www.example.com")

When creating a web scraping software, it's crucial to bear following in mind. Once you develop a complete VBA web scraping program, it will have to run through several VBA statements after navigating to a specific webpage. However, webpages can take some time to be fully loaded due to various reasons. So when you run the program there is a chance of executing subsequent VBA statements before the webpage is fully loaded. This can adversely affect the results you get from the software. But luckily we can take preventive action to overcome this. You can use below VBA statements to tell the program to wait until the webpage is fully loaded.

Do
DoEvents
Loop Until objIE.readystate = 4

Below is the full code to create an Internet Explorer object and navigate to a webpage.

Sub MyFirstWebScrapingProgram()

     Dim objIE As Object

     Set objIE = CreateObject("InternetExplorer.Application")

     objIE.Top = 0
     objIE.Left = 0
     objIE.Width = 800
     objIE.Height = 600

     objIE.Visible = True

     objIE.Navigate ("http://www.example.com")

     Do
     DoEvents
     Loop Until objIE.readystate = 4

End Sub

Also if the website is temporarily unavailable, VBA web scraping programs can be suspended before collecting all the relevant data. So If our program tries to collect data from a website while it is down, it will halt our program. So we need to use error handling techniques to overcome temporary interruptions like this. Then our program will wait until the website is up again. If you don’t use this technique, then the user will have to check from time to time whether the program is working or halted.

Here is an example of using error handling in VBA web scraping.

Dim objIE As Object

the_start:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.Visible = True

On Error Resume Next
objIE.navigate ("http://www.example.com/")

Do
   DoEvents
   If Err.Number <> 0 Then
        objIE.Quit
        Set objIE = Nothing
        GoTo the_start:
   End If
Loop Until objIE.readystate = 4

MsgBox "Web page has loaded"

In the above example we have used the error handling technique to check whether it has navigated to a webpage. But in practical situations a program will need to navigate through hundreds or thousands of pages. Then you will need to use a similar error handling technique inside loop iterations.

In the first steps of the web scraping programs, often we need to assign the HTML code to a variable of type string. So the below code shows how to assign HTML code of a webpage to a variable of type string. Here HTML_Code is the variable name.

Dim HTML_Code as String

HTML_Code = objIE.document.body.innerhtml

This will extract the inner HTML content of the <body> element of the web page loaded in the Internet Explorer (objIE) object and assign it to the variable called HTML_Code. You can even print the inner HTML content to the immediate window as follows.

Dim HTML_Code As String

HTML_Code = objIE.document.body.innerHTML

Debug.Print HTML_Code

Sometimes it is very easy to get required data from the inner-text without dealing with HTML codes. Below example shows how to assign inner text to a variable of type string and print it in the immediate window.

Dim InnerText as String

InnerText = objIE.document.body.innertext

Debug.Print InnerText

However if you do this, in lots of cases, you will realize that extracted content is not in a usable format. In lots of times you will get a stockpile of useless data. So in VBA web scraping we should always try to focus on getting only required data using id attributes and class attributes. Because these attributes help us to get only the targeted data. Read these posts to learn how to collect data using id and class attributes.
Web Scraping - Collecting Data From a Webpage
When to use the getElementsByClassName method
getElementsByClassName Vs getElementById

But sometimes required data might not be associated with any id or class name. In such cases we can use the javascript method “getElementsByTagName'' to collect the required data. Following example shows how to get elements by < a > tags. (anchor tags)

Set Alllinks = objIE.document.getelementsbytagname("A")
For Each Hyperlink In Alllinks
    MsgBox Hyperlink.innertext & "-" & Hyperlink.href
Next

Sometimes we need our VBA web scraping programs to follow the links and collect the data. To do that the program needs to click on < a > tags. Now let’s see how we can accomplish this. To achieve this the program needs to loop through all the < a > tags of the webpage and find the relevant link. Then the VBA program can click the link to navigate to that page. Let’s try to understand this using an example. In this example, the VBA web scraping program will click the link if it finds a link with the text "New Arrivals".

Set Alllinks = objIE.document.getelementsbytagname("A")
For Each Hyperlink In Alllinks
        If InStr(Hyperlink.innertext, "New Arrivals") > 0 Then
               Hyperlink.Click
               Exit For
        End If
Next

When creating VBA web scraping programs to collect data from websites, several built-in functions become imperative. Here are some key VBA functions essential for web scraping programs.

Commonly used VBA functions in web scraping

Mid - This VBA function can be used to extract a string segment from it's parent string.
InStr - This function can be used to check whether certain string is included in another string and get the position of that sub string if it exists.
Replace - This VBA function replaces a character or string segments with another character or string.

Above are the basics you need to know when developing VBA web scraping programs. Follow this complete guide if you want to become an expert in VBA web scraping.

Open and read text file

                         We need to handle text files in various situations. Sometimes we need to create, open, write  or delete text files while the program is running. So today I will explain you how to Open and read text file through VBA.
First of all we need to define the variables.

Dim myFile, text, textline as String

Variable "myFile" is used to store the file path of the text file. Each and every text lines will be stored temporarily to the variable call "textline" while program run throgh the Do loop until it reaches to the end of the file. All the text lines of the text file will be appended to variable "text".

There are two ways to assign the text file to "myFile" variable depending on your requirement.

Either you can use below line of code to assign the file path directly. This method can be used if you always use same folder path and file.

myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt"

Or you can use below line of code to assign the file name to variable. Here user get opportunity to select the file through browsing window.

myFile = Application.GetOpenFilename()

So you should use one of the above two lines in your program according to end user requirement. However rest of the program will not affected by what you chooses from above two ways.  
Below line will open the file for reading

Open myFile For Input As #1

Below do loop will run until it reaches to the end of the text file. Inside the do loop, program read the text file line by line and assign that single line to variable "textline". Whole text of text file is appended to variable call "text" by the next line.
Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
Loop

Below line of code will close the text file which was opened for reading.
Close #1

Now you have whole content of text file in variable "text". Now you can do any string manipulation to that text from here.
However I will just show the content in a message box.

MsgBox text

Here below is the full code for you to easily copy paste for your project

Dim myFile, text, textline as String myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt" or myFile = Application.GetOpenFilename() Open myFile For Input As #1 Do Until EOF(1)     Line Input #1, textline     text = text & textline Loop Close #1 MsgBox text

VBA - Error 400

Have you ever got a error similar to shown below. If you are a VBA developer you might have got this error when you run or debug your program.
This error message contains only 400 and OK button.

I saw lot of people have asked about this error in lot forums. This error made developers very frustrated because it doesn't show any error message or anything. It only pop up a message box with "400" and OK button.

Soon after you press the OK button, it will kill the process.

However in microsoft website I found information about similar error. I saw following line in that website.

400 Form already displayed; can't show modally

So here what has happened is program tried to display the form again while it is already displayed. May be it is hidden by a sheet or another form.

But in our case error message is different. Only similarity is both the error messages have "400" in common. However after doing some experiments I was able to find one reason for "error 400". Think your program access files in your hard drives while it runs. So this error can occur if you have given non existent path to the program. So program try to access a file. But it is not existing. This will cause error 400. Then even missing of a one letter in the file path can throw this error. Lot of people says it takes much more time to identify a small misspelling than finding a complicated one. 

Open files in a specific directory (folder)

Some times VBA programs needs to access files from a specific folder. Assume you have a folder call "Test" within your drive "D".

Following code will open all the files inside that folder. You can easily modify this code for your requirement.

Dim file As Variant Dim pathAndFileName  as String file = Dir("D:\Test\") While (file <> "")     pathAndFileName = "D:\Test\" & file     Application.Workbooks.Open (pathAndFileName)     file = Dir Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name.

Dim file As Variant
Dim pathAndFileName  as String

Next we assign a our first value to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to open all the files inside that folder. We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.



Application.Workbooks.Open (pathAndFileName)

So now we have opened the first file in that directory. We need to repeat the process for all the other files. But before going to next cycle we need to clear the attributes of our variable "file". We use following line of code to clear them

file = Dir

That is the full explanation of the code.

Finding a specific file from a folder

Some times we need to check whether specific file is contained in a folder. Below example checks whether file "book1.xlsx" is contained in a folder call "Test" which is located in "D" drive. Then open the file if it is exists.

Dim file As Variant Dim FName, pathAndFileName As String FName = "book1.xlsx" file = Dir("D:\Test\") While (file <> "")     If StrComp(file, FName) = 0 Then         pathAndFileName = "D:\Test\" & file         Application.Workbooks.Open (pathAndFileName)         MsgBox "found " & file     End If file = Dir Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name. Variable FName holds the file name we want to check.

Dim file As Variant
Dim FName, pathAndFileName  As String
FName = "book1.xlsx"

Next we assign the first file of the folder to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to compare all the files inside that folder with file name we want. For the comparison we use below line of code

 If StrComp(file, FName) = 0 Then

If the file names matched then the codes inside the if statement will be executed.
We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.

Application.Workbooks.Open (pathAndFileName)

Then program will give the message that it has found the file we want.

 MsgBox "found " & file

There is important thing we need to do when loop through the program. It is that we need to clear the attributes of our variable "file"  before going to next cycle. We use following line of code to clear them

file = Dir

Sort Function

                   Today we will discuss about using Sort function through VBA. So assume you have below kind of excel sheet. You can notice that values in column A under the title "NO" are not in and order. So what we going to do is sort the data according to the values in column  A.

Just go to the Data tab of excel application. You will see below icon in that tab.

                   If you click that icon, it will pop-up a table similar to below. From that table you can choose based on what column you going to sort the data. Based on what, you going to sort the data. And also you can select the order as well.

                      After selecting the parameters accordingly, you can click the OK. Button. Then worksheet will end up with result similar to shown in below image.

So now we have come to the important part. What we are going to do is complete the above process through VBA. You can use below code for that. This sample code will sort data from cells A2 to E13 based on values in the 1st column.

Dim WS As Worksheet Set WS = ActiveSheet WS.Range("A3:E13").Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     ("A3:A13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range("A3:E13")     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With

Also you can use this subroutine to sort data in ascending order. This subroutine take data range, sort fields as parameters. So you can readily use this inside your projects as separate sub routine.

Sub SortAscending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _ FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

And this is the subroutine for sort descending

Sub SortDescending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _ FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

Sometimes we need to add two or more levels when we sorting the data. This is how we do it manually.

Following is a subroutine which can use for two levels. This will sort both levels in ascending order.

Sub SortAscendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _ KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal WS.Sort.SortFields.Add Key:=WS.Range _     (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

If you want to sort both levels in descending order, then you can use below subroutine.

Sub SortDescendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _ KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal WS.Sort.SortFields.Add Key:=WS.Range _     (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

Date format issue (How to solve)

     
                  In this lesson I'm going to teach you how to overcome formatting errors when you input data to excel sheet from a text box, combo box etc.

                  Here is a my own experience. Once I had a date value 01/08/2014 in one of text box of a user form.I got that value to the text box from a date picker. So it was in the format dd/mm/yyyy.
So the above date refers to 1st of August 2014.

                  However when data entered to the excel sheet it took the form of 08/01/2014
So now it refers to 8th of January 2014
    
                  If you face this kind of situation here below is the solution.Just format the value using Format function before input data to the sheet.

Here txtDueDate is the name of the text box. Below example enters date value to cell E3 of worksheet("Data")

Dim WS as worksheet     set WS=worksheets("Data")     Dim DDate As Date     DDate = Format(txtDueDate.Text, "dd/mm/yyyy")     WS.Range("E3").Value = DDate

Find function

Think you have a spread sheet similar to this. It contains value "100" in several cells.

So today I will teach you how to get the address of those cells from VBA code

First let's define the value we need to search.

Dim s1 As String

Then assign the value we search to that variable.

s1 = "100"

And it is a good practice to use objects where you can. It prevent occurrence of errors. Think you have worksheet call "Report". If you use conventional way you need to refer that sheet by worksheets("Report") at every where you needed to refer it. But if you use worksheet object you can refer it from very short name. So this will give neat look to your coding as well. So I prefer using objects. So use worksheet objects as follows.

Dim WS As Worksheet Set WS = Worksheets("sheet1")

Then Set the range we need to search.

With WS.Range("A1:T23")

Following line of code will give you the address of each cell having 100 in it.

MsgBox c.Address

Loop will continue until it find a value for c except first value.

Loop While Not c Is Nothing And c.Address <> firstAddress

So here is the full code. You can use this wherever you need by changing the sheet names, range and search values accordingly. Also you can pass the search value as a variable.

Dim s1 As String s1 = "100" Dim WS As Worksheet Set WS = Worksheets("sheet1") With WS.Range("A1:T23")     Set c = .Find(s1)     If Not c Is Nothing Then         firstAddress = c.Address         Do             MsgBox c.Address             Set c = .FindNext(c)         Loop While Not c Is Nothing And c.Address <> firstAddress     End If End With

With the above code you will get the cell address of cells having "100" to msgboxes.

Populate combo box with sheet name of opened work book

Here cboFromSheeet is the name of the combo box.

Dim wbFrom As Workbook
Set wbFrom = Application.Workbooks.Open(lblFromFilePath.Caption)
Dim ws As Worksheet

'Clear the combo box
cboFromSheeet.Clear

With cboFromSheeet
    For Each ws In Worksheets
            .AddItem ws.Name
    Next ws
End With

cboFromSheeet.Text = "Select a Sheet"

Add work sheet with a name

Think you want to add a worksheet with a name "Report"

This code will do it.

Sheets.Add.Name = "Report"

However you can do this even with objects

See following code

Dim WS as Worksheet
Set WS = Sheets.Add

You can refer this new sheet by WS wherever at the code. You don't need to know anything about like it's name, location etc. Even you can give it a name later.

For an example think you want to name it as "Calculation". So below code will do it.

WS.Name = "Calculation"

Find last row having data in a certain column

    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

reference workbooks and sheets Explicitly

This example shows how to reference workbooks and sheets explicitly.

Dim wbFrom As Workbook
Dim wbTo As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet
Dim Str1, Str2, s1, s2, s3, s4 As String
'paths of 2 workbooks
Str1 = lblFromFilePath.Caption
Str2 = lblToFilePath.Caption
Dim WrdArray() As String
WrdArray() = Split(Str1, "\")
's1 and s2 are Workbook names
s1 = WrdArray(UBound(WrdArray()))
WrdArray() = Split(Str2, "\")
s2 = WrdArray(UBound(WrdArray()))
'Worksheet names
s3 = cboFromSheeet.Value
s4 = cboToSheet.Value

Set wbFrom = Workbooks(s1)
Set wbTo = Workbooks(s2)
Set wsFrom = wbFrom.Worksheets(s3)
Set wsTo = wbTo.Worksheets(s4)

If cboFromSheeet.Text <> "Select a Sheet" And cboToSheet.Text <> "Select a Sheet" Then
    MsgBox wsFrom.Name, , "from"
    MsgBox wsTo.Name, , "To"
Else
    MsgBox "Please select relevant sheets from the drop down menu.", vbOKOnly, "WARNING!"
End If

Browse File And Assign Full Path And Name Into Two Variables

This Excel VBA tutorial explains how to use file browsers in Excel VBA applications. In Excel VBA applications, we often need to let the user input data from various files to the application. If we know the file path then we can directly use that path in the code to open the file. But this method is not practicable because we can’t ask the users to open the VBA editor and change the codes. Also it will be impossible if you lock the source code. Therefore using a file browser is the ideal solution for that kind of requirement.

If the application has a file browser option like above, then users can easily select files to upload the data. So let’s look at how to add a file browser option to a VBA application. Let’s name this subroutine as “SelectFile”

Sub SelectFile()

End Sub

First we need to declare a few variables.

Dim strFile As String
Dim FullPath As String
Dim FileName As String
Dim WrdArray() As String

Next we can show the “Open dialog box”. So the user can select a file.

sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

Now we need to check whether the user clicked on the “Open” button or the “Cancel” button. We can use a If .. Then .. Else Statement to determine that.

If sFile = "False" Then
     'If user click "Cancel" button
Else

End If

If the user clicks on the “Cancel” button then we should exit the subroutine using “Exit Sub” statement. Otherwise the program will be executed to the file opening section and produce an error. If the user clicks on the “Open” button, sFile will contain the full path of the file. So we can assign it to the FullPath variable as follows.

FullPath = sFile

Now the remaining task is to extract the name of the file from the sFile variable. We can use the Split function to get that job done. Name of the file will occur after the last “\” character. So we can split the sFile string using “\” as a delimiter and then get the last element of the result array.

WrdArray() = Split(sFile, "\")
FileName = WrdArray(UBound(WrdArray()))

Want to learn about Split function? Read this post => Split Function

Now complete If .. Then .. Else block should look like this.

If sFile = "False" Then
     'If user click "Cancel" button
     Exit Sub
Else
     FullPath = sFile
     WrdArray() = Split(sFile, "\")
     FileName = WrdArray(UBound(WrdArray()))
End If

Finally we can open the file and assign it to the WB variable as follows.

Set WB = Workbooks.Open(FullPath)

Note that the full path is enough to open the file. But knowing the file name can be useful for some other purposes. Below is the full code for the “SelectFile” subroutine.

Sub SelectFile()

    Dim WB As Workbook
    Dim strFile As String
    Dim FullPath As String
    Dim FileName As String
    Dim WrdArray() As String

    sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

    If sFile = "False" Then
         'If user click "Cancel" button
         Exit Sub
    Else
         FullPath = sFile
         WrdArray() = Split(sFile, "\")
         FileName = WrdArray(UBound(WrdArray()))
    End If

    Set WB = Workbooks.Open(FullPath)

End Sub

In the above example we let the user to only select the Excel files. If you want to allow the user to select any file type then replace “sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")” with below line.

sFile = Application.GetOpenFilename()

And sometimes you may want to permit users to only select CSV files. Use the below code in that case.

sFile = Application.GetOpenFilename("CSV files,*.csv")

In this lesson we considered selecting only one file. But you can use the “GetOpenFilename” method to select multiple files as well. But then it will return an array (array will contain names of the selected files.) instead of a string.