Get Active Directory User Name in Excel VBA

Found over at Mr Excel from a comment by Grizlore. This is a more complex way of getting Active Directory user information rather than just Application.UserName or Environ("Username")

Option Explicit

Sub Users_Fullname()
'originally coded as VB script by A.Vials, converted to VBA by Sly
Dim objInfo
Dim strLDAP
Dim strFullName

Set objInfo = CreateObject("ADSystemInfo")
strLDAP = objInfo.UserName
Set objInfo = Nothing
strFullName = GetUserName(strLDAP)

MsgBox "Full name of User is " & strFullName  'step to test

End Sub

Function GetUserName(strLDAP)
  Dim objUser
  Dim strName
  Dim arrLDAP
  Dim intIdx

  On Error Resume Next
  strName = ""
  Set objUser = GetObject("LDAP://" & strLDAP)
  If Err.Number = 0 Then
    strName = objUser.Get("givenName") & Chr(32) & objUser.Get("sn")
  End If
  If Err.Number <> 0 Then
    arrLDAP = Split(strLDAP, ",")
    For intIdx = 0 To UBound(arrLDAP)
      If UCase(Left(arrLDAP(intIdx), 3)) = "CN=" Then
        strName = Trim(Mid(arrLDAP(intIdx), 4))
      End If
  End If
  Set objUser = Nothing

  GetUserName = strName

End Function

Might be worth trying to looking into the objUser.Get("givenName") & Chr(32) & objUser.Get("sn") line to see what other information can be pryed from AD.

Create a New Rails App using Hashicorp Otto on Windows

These were the steps to successful get a rails app up and running using Hashicorp Otto.

In this case I didn't have an existing repository so couldn't clone an app from Github. Otto therefore didn't have any Gemfile to parse to determine what sort of app I was trying to build.

First step was to create an alias in my .bashrc so that I could use a command otto_new_rails appname. That alias looked like:

You can see I was playing around with getting Postgresql setup but couldn't figure that out.

Next steps were then:

Full stack trace can be found on [this Github Gist][1].

VBA to adjust a spreadsheets zoom level based on a users monitor resolution

I am currenty tweaking this code to make it so that the excel applications I build for our sales team adjust based on the large variation in monitor sizes. If you want to use this code on your own project please follow these instructions. Once you have accessed your excel document enter the VBA environment.

Accessing VBA in Excel Press Alt+F11 from within an opened Excel document Either 'Insert a Module' by clicking the insert button or 'Right-Click' on a worksheet and select 'View Code' Copy the code below. Paste the code into the new module or worksheet code area.

Adjusting the VBA code If you want to adjust the zoom of all worksheets then comment out (by adding a ' before the line) or remove the following lines If activews = cmws Then maxWidth = GetSystemMetrics(0) * 0.5 Else End If If you want to target a specific worksheet then change the name of the value between Sheets("enter worksheet name here").Name in the variable cmws

A Preview of the Microsoft Power BI for Office 365 Preview

That's right... it is a preview of a preview. I have been working recently building Excel applications on local drives with a target user base that needs to be connected to our intranet to access those drives. This is cumbersome and requires the client of the application to come off the road and into the office to perform their analysis that the application provides. In large corporations there isn't a lot you can do about this due to IT roadblocks (I say roadblocks but I know they are just trying to keep things secure) and user skill levels.

As the global cloud technology advances at a rapid pace it grows more obvious everyday that by the time IT organisations deploy new technologies, some Start Up somewhere will be putting the pressure on with a better product that does things more intuitively, easier and with less requirement for skilled analysts. To stay ahead of the curve I have been doing a lot of my own research in how to utilise the web through apache and MySQL databases to bring the data to the user on their devices (usually brought in from home) so that I am not limited by IT to bring the system to the user. It is an odd rationale to determine whether I am overstepping my scope but from my perspective I am just utilising these technologies to have easier access to my clients information for my own purposes.

Take for example forms that are used for administrative or data collection purposes. Usually built in Excel, deployed through local drives and usable by people connected to those drives. But what if I was to build a form into a WordPress site self hosted by me on my computer or through a secure server paid by me. What would be the limitation on doing this? I am not too sure. Especially considering the growing amount of employees utilising services such as MailChimp, Survey Monkey or Hootsuite that provide freemium benefits with little to no setup time. We do this because we can self manage these systems, they usually provide great training and we don't have to write business cases or rely on skilled labour to build an in-house solution that costs thousands of dollars.

While stumbling across this process of how I utilise the internet to gather information from my colleagues I came across Microsoft Business Intelligence for Office 365. This is pretty exciting stuff. At first I was just looking at how I can connect Microsoft Excel to my MySQL Database on my server or doing the same thing with Microsoft Access. What I see with Microsoft Power BI for Office 365 does a hell of a lot more than that. What about connecting to any database of information right from within Excel and all is required is a simple search. Need building approval data, just search for it, if a business has setup the information (usually Government agencies) then you just plug yourself in and off you go. No need for manual manipulation of data, running Macros, integrating Javascript and PHP into Excel through .Net to try and scrap databases online. This brings a whole new world of convenience to developing models and forecasting production. For example, plug data around average rainfalls into your production forecasts and see what it's effect will be on gross margin. Now you can have an understanding when it comes to setting budgets etc especially if that database is fairly accurate on forecasting future events.

Now if you are an analyst like myself, you may wonder what work this will leave for actual 'analysis'. From my perspective this, more intuitive design of information and databases allows us to be more creative in our identification of opportunities, rather than spending the bulk of our time just trying to visualise the data for easy interpretation.

For now, I will give Microsoft Power BI for Office 365 a spin, to see what the future is like and be ready for when it comes knocking. Check out the video to see what sort of opportunities will be coming your way.