All posts by AnalystCave

Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
map

Excel Google Charts Tool

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

I always wanted to utilize the beautiful and interactive Google Charts in Excel. The Google Charts repository is constantly growing and sometimes Excel lacks those features. Hence I introduce the Excel Google Charts Tool to display a way to leverage some of those Google Charts directly in Excel.

The WebBrowser control is no longer supported by Office 2013 and above, hence this functionality might not work by default in those versions of Office

The Excel Google Charts Tool contains example Google Charts embedded inside an Excel xlsm file allowing you to visualize data in a more attractive way and enabling more user interaction.

Excel Google Charts: Gauge Chart

Gauge charts are extremely useful to highlight important values in reports. You can also visualize the good and bad ranges of values e.g. orange and red for too high values. These however, can be easily configured. I

Excel VBA Gauge Chart
Excel VBA Gauge Chart

How to configure a Gauge Chart?

Google Chart: Gauge Chart

Excel Google Charts: Treemap Chart

Treemaps can be particularly useful when you want to drill-down data values e.g. used disk space and drill-down across folders. Google Treemaps have 2 values which you can visualize – the area of the treemap and the color.

Excel VBA Treemap Chart
Excel VBA Treemap Chart

How to configure a Treemap Chart?

Google Charts: Treemap Chart

Excel Google Charts: Org Chart

Excel VBA Orgchart
Excel VBA Orgchart

How to configure a Org Chart?

Org Charts come in handy when you want to visualize the tree/organisational structure.
Google Charts: Org Chart

Excel Google Charts: Geo Chart

I would say – one of the most useful charts when playing with geo-data. Using the Geo Chart you can easily visualize how your data is broken down across countries. You can zoom in the Geo Chart just to show a single continent, country or region.

Excel VBA Geochart
Excel VBA Geochart

How to configure a Geo Chart?

Google Charts: Geo Chart

Download

The file below contains all examples of Google Charts used in the Excel Google Charts Tool.


Currently the Google Chart Tool contains examples of the following Google Charts:

Issues and errors

One issue you might stumble on when using the above Google Charts may be due to recent scriptable control restrictions imposed by Microsoft. Due to these in Excel 2013 and above Excel will restrict (by default) the use of some controls e.g. Microsoft Web Browser Control – which is required to run the above Google Charts. There is a way around that so utilize the link above to read more.

SQL in VBA

Using SQL in VBA on Excel. Run SELECT Queries from VBA

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

Many times I was irritated of the lack of some Excel functionality (or just I don’t know there is) to easily transform data w/o using pivot tables. SQL in VBA was the only thing that was missing for me.

Distinct, grouping rows of Excel data, running multiple selects etc.
Some time agon when I had a moment of time to spare I did my homework on the topic only to discover that running SQL queries from Excel VBA is possible and easy…

Using SQL in VBA example

sql in excel
An Excel Worksheet and the Output of the RunSELECT Macro
Let see how to run a simple SELECT SQL Query in Excel VBA on an example Excel Worksheet. On the right see my Excel Worksheet and the Message Box with the similar output from my VBA Macro. The VBA Code is below:

Explaining the Code

So what is happening in the macro above? Let us break it down:

Connecting to the Data Source

First we need to connect via the ADODB Driver to our Excel Worksheet. This is the same Driver which runs SQL Queries on MS Access Databases:

The Provider is the Drive which is responsible for running the query.

The ConnectionStrings defines the Connection properties, like the path to the Queries File (example above is for ThisWorkbook) or if the first row contains a header (HDR).

The Open command executes the connection.

You can find more information on the ADODB.Connection Object on MSDN.

Looking for other Connection Strings to XLS or Access files? Check out ConnectionStrings.com. Here are some examples:

Running the SQL Select Query

Having connected to our Data Source Excel Worksheet we can now run a SQL SELECT Query:

So what happens here? First we run the Execute command with our SELECT query:

What does it do? It indicates that our records are in Sheet1. We can obviously extend this query just to filter people above the age of 30:

This would be the result:

sql in excel - above 30
SELECT * FROM [Sheet1$] WHERE Age > 30

The Execute command returns a ADODB RecordSet. We need to loop through the recordset to get each record:

Clean up

Lastly we need to Clean up our Objects to free memory. This is actually quite an important step as if you VBA code is runs a lot of queries or computations you might see a slow-down soon enough!

What Else Can I Do?

You can do tons of great things with ADODB / MS Queries / SQL in Excel. Here are some additional ideas:

  • Run Queries Across Worksheets – you can run JOIN queries on multiple Excel Worksheets. E.g.

    On the below tables:
    sql in excel example
    Running SELECT with JOIN on 2 Worksheets
  • Extracting Data from External Data Sources – use different connection strings to connect to Access Databases, CSV files or text files
  • Do more efficient LOOKUPs – read my post on VLOOKUP vs SQL to learn more
outlook users

Get Outlook recipients information via VBA (Outlook users data)

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

Being a corporate Consultant I always thought of the vast amount of data stored in various corporate databases that could be put to good use. Lot of data is stored and is accessible via Outlook – although mostly not appreciated.

I once received an assignment which required analyzing the data of all employees in our company. One way to approach this assignment was to contact HR, which would then need to contact regional HR … and so on. A process that would last at least a week before I could get my hands on the actual data. And even then this would require verifying the data and some cleansing. The other easier way – the corporate Outlook correspondence group containing all active employees and to somehow dump this data to an Excel/CSV file for analysis. Hence, I conducted a short research resulting in the following VBA function:

VBA to extract Outlook recipient information

Here is the VBA code:

Sub DumpOutlookData()
    Dim NewMail As Outlook.MailItem
    Set NewMail = Application.ActiveInspector.CurrentItem
    Dim s As String, filePath As String
    s = ""
    On Error Resume Next
    For Each Recipient In NewMail.Recipients
      s = s & Recipient.AddressEntry.GetExchangeUser.Alias
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Name
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.JobTitle
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.City
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Department & vbNewLine
    Next Recipient
    Set FSO = CreateObject("Scripting.FileSystemObject")
    filePath = Date & "-Outlook Data.txt"
    Set oFile = FSO.CreateTextFile(filePath)
    oFile.WriteLine s
    oFile.Close
    Call MsgBox("Saved to: " & filePath)
End Sub

Notice that what it does is locate the current open email and inspect its recipients. Next it traverses the whole list of recipients and dumps that data to your default VBA directory.

How to dump Outlook recipient information – Step by step

To make sure you are using the code above correctly be sure to follow the instructions below:

Insert the function to your Outlook Visual Basic project

Go to Developer ribbon tab and select Visual Basic. In the VBE (Visual Basic Editor) right click on your VBA Project and select New Module. Insert the macro above.

Add recipients to Outlook draft email

Open a new email message in Outlook and add the desired recipients to the To: section e.g. if adding a correspondence group be sure to expand the list to list all recipients individually. This is an important step – be sure to list INDIVIDUAL recipients as the macro will not expand any group emails

Execute the macro

Go to Developer ribbon tab, select Macros and run the DumpOutlookData procedure. Wait for it to dump the data to the txt file (to your default folder – Documents, read more how to change default directory.). This might take some time depending on how many email recipients you want to dump to your file.
outlook recipients dump
You can also extend the macro to get additional information on Outlook recipients like:

  • Manager (Recipient.AddressEntry.Manager.Alias)
  • Office Location (Recipient.AddressEntry.GetExchangeUser.OfficeLocation)
  • Phone Numbers (Recipient.AddressEntry.GetExchangeUser.MobileTelephoneNumber )

..and other information.

Where to go from here?

Ok so you got the data in a semicolon ; separated CSV file. What next?

Cool ideas to utilize this data

There are tons of useful things you can do with this Outlook recipient information. Here are just some:

Hopefully you see what an incredible resource everyone in a company has – which you can utilize for tons of internal company projects without needing to approach HR.

google translate

Excel Google Translate functionality

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

Translations are not a fascinating task for me. I once received a request to help out a colleague in translating an Excel file to English. Copy to Google Translate, translate, copy back to Excel and again… After going through part of the file we quickly extrapolated that the whole translation would take at least a whole day if done in this manner. I had to optimize this process as much as possible.

That’s when I thought of leveraging Google Translate to help with my localization efforts. Why translate stuff manually when we can put Excel to do the job for you.

Excel Google Translate Code

Find below a quickly written VBA procedure that adds a shortcut to Excel to quickly translate any cell from one language to another. The below is a simple procedure you can easily pin to your Excel shortcuts (e.g. CTRL+K or similar). Simply select a certain Excel range and execute the TranslateCell procedure below.

Looking to translate an Excel or Word document? Why not use my Word / Excel Translator AddIns?
Sub TranslateCell()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "fr"
    translateTo = "en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    getParam = ConvertToGet(ActiveCell.Value)
    URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
        trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
        ActiveCell.Value = Clean(trans)
    Else
        MsgBox ("Error")
    End If
End Sub

'----Used functions----
Function ConvertToGet(val As String)
    val = Replace(val, " ", "+")
    val = Replace(val, vbNewLine, "+")
    val = Replace(val, "(", "%28")
    val = Replace(val, ")", "%29")
    ConvertToGet = val
End Function
Function Clean(val As String)
    val = Replace(val, "&quot;", """")
    val = Replace(val, "%2C", ",")
    val = Replace(val, "&#39;", "'")
    Clean = val
End Function
Public Function RegexExecute(str As String, reg As String, _
                             Optional matchIndex As Long, _
                             Optional subMatchIndex As Long) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
    If regex.Test(str) Then
        Set matches = regex.Execute(str)
        RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
        Exit Function
    End If
ErrHandl:
    RegexExecute = CVErr(xlErrValue)
End Function

How to setup Google Translate code in Excel?

    • Go to the DEVELOPER ribbon and add select Visual Basic a and new Module to your VBA Project
      insert macro
    • Insert the code from sections above (notice that the function is “Public” – therefore will be visible in your workbook as a so called UDF (User Defined Function) add macro
    • Go to the worksheet and input the function as shown above
    • Modify the translateFrom and translateTo languages in the function to change the translation e.g. change “pl” to “de” to change the translation from Polish to German.

Here you can find the full list of language 2 letter codes.

  • In the Excel Developer ribbon open Macros
  • Select the TranslateCell sub and go to Options
  • Add a shortcut: e.g. CTRL+SHIFT+T

How to use it?

  • Select any cell in Excel and click the configured shortcut e.g. CTRL+SHIFT+T

What if I want an UDF?

The procedure above can easily be converted to a User Defined Function (UDF) instead, allowing you to use it as a formula. Be sure to copy the required supporting functions (from the sections above) ConvertToGet, Clean and RegexExecute.

VBA UDF Google Translate

Public Function Translate(rng As Range, Optional translateFrom As String = "nl", Optional translateTo As String = "en")
    Dim getParam As String, trans As String, objHTTP As Object, URL As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    getParam = ConvertToGet(rng.Value)
    URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
        trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>

")
        Translate = Clean(trans)
    Else
        Translate = CVErr(xlErrValue)
    End If
End Function

 

 

 

How to use the Translate UDF?

See an example below of how to translate text in cell A1 from French (fr) to English (en):

=TRANSLATE("A1","fr","en")

What if I want it to run on all selected cells?

Here is a modification of the function above to run on ALL selected cells:

Sub TranslateCell()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "pl"
    translateTo = "en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Dim r As Range, cell As Range
    Set cell = Selection
    For Each cell In Selection.Cells
        getParam = ConvertToGet(cell.Value)
        URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
            trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
            cell.Value = Clean(trans)
        Else
            MsgBox ("Error")
        End If
    Next cell
End Sub
map

Get Google Maps address coordinates (latitude & longitude) in Excel VBA

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

Continuing my last post, on how to get the distance between any addresses using VBA, I wanted to add a capability to my previous Excel file that would allow me to get any address coordinates. How to quickly get the lat. and long. of an address? Again why not use Google API…

Address coordinates in Excel

Sub GetLocation(address As String, ByRef lat As String, ByRef lng As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/geocode/json?address="
    lastVal = "&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(address, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """lat""") = 0 Then GoTo ErrorHandl
    tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lat"" : ") - 7)
    lat = Split(tmpVal, ",")(0)
    tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lng"" : ") - 7)
    lng = Replace(Split(tmpVal, "}")(0), " ", "")
    Exit Sub
ErrorHandl:
    lat = lng = 0
End Sub

This function can easily be used in any VBA algorithm like this:

Dim lat1 as String, lng1 as String
call GetLocation("Al. Jerozolimskie 2, Warsaw", lat:=lat1, lng:=lng1)

In the example above the address coordinates (latitude and longitude) will be returned to lat1 and lng1 variables.

Limits and common issues

Google limits the amount of free queries you can execute. Considering the limitations and common issues please read this section of my post.