Excel – LDAP authentication

The beauty of Excel is the ubiquitousness of the program or support for the file formats. When adding business logic and power to these tools however it can be necessary to restrict access due to company confidential information.

Trying to accomplish this in Excel using VBA is basicly a curse due to the horrible developer environment, lack of debugging, and a fractured reference resource.

While with any programming process there are many ways to accomplish the goal and this is just one.

Assuming a userform that prompts for username and password it will add the domain name if necessary.


 

Const ADS_SECURE_AUTHENTICATION = 1

Const ADS_USE_SSL = 2

 

Dim conn As New ADODB.Connection

Dim rs As ADODB.RecordSet

Dim oRoot As Object

Dim oDomain As Object

Dim sBase As String

Dim sFilter As String

Dim sDomain As String

 

Dim sAttribs As String

Dim sDepth As String

Dim sQuery As String

Dim usern As String

 

 

On Error GoTo ErrHandler:

 

Set oRoot = GetObject(“LDAP://rootDSE”)

 

‘work in the default domain

sDomain = oRoot.Get(“defaultNamingContext”)

 

 

Set oDomain = GetObject(“LDAP://” & sDomain)

 

sBase = “<” & oDomain.ADsPath & “>”

‘Only get user name requested

sFilter = “(objectClass=*)”

sAttribs = “AdsPath, cn”

sDepth = “subTree”

 

sQuery = sBase & “;” & sFilter & “;” & sAttribs & “;” & sDepth

‘Add the domain to the user name

If InStr(1, txtUser.Text, “domainname”) = 0 Then

usern = “domainname” & txtUser.Text

Else

usern = txtUser.Text

End If

 

Set conn = CreateObject(“ADODB.Connection”)

conn.Provider = “ADsDSOObject”

conn.Properties(“User Id”) = usern

conn.Properties(“Password”) = txtPass.Text

conn.Properties(“Encrypt Password”) = True

‘Set Flags to ensure secure authentication is used instead of simple

conn.Properties(“ADSI Flag”) = ADS_SECURE_AUTHENTICTION + ADS_USE_SSL

 

conn.Open “Active Directory Provider”

 

Set rs = conn.Execute(sQuery)

GoTo ExitSub

 

ErrHandler:

 

On Error Resume Next

If Not rs Is Nothing Then

If rs.State <> 0 Then rs.Close

Set rs = Nothing

 

End If

 

If Not conn Is Nothing Then

If conn.State <> 0 Then conn.Close

Set conn = Nothing

 

End If

 

Set oRoot = Nothing

Set oDomain = Nothing

MsgBox “You are not authorized to use this application, please contact an Administrator.”

Application.Quit

 

ExitSub:

Unload Me

 

End Sub

Moving – Using Professional Movers and Temporary Living

Professional Movers

Having moved across this fine nation more then a few times I have had the occasion to use a number of professional movers and observed a few things.

Paying for professional packes can increase the cost, but it is very useful when on a tight schedule. A range of employees might show up from those that look extremely profesional to those look less so, but outside of the rare exception they are going to know how to pack your things and do so in an efficent manner.

Don’t skimp on the use of packing tape or packing paper. The way you ensure you have items arrive safely is by having solid void free boxes that can be stacked properly by the movers. Line each box with a sheet or two of paper and learn from the packers by taping down the seam as well as the edges.

the-h-taping-method

 

You might think this is wasting tape, but at the end of the day tape and packing materials are cheap compared to the cost of replacing your valuable items. It is money well spent.

Empty tape rolls are infinitely usable and should not be thrown away. They can be used for screw or shelf peg containers by using a little tape on the outside. Folded up they make exceptional doorstops to keep the door propped open.

One of the differences between average and elite movers will be the use of glass crating for glass table tops or the use of specific flat screen containers that comes with static free material and tapes. This can then be used with anti-tip levels which can tell if an item has been tipped over or set on its side. Even without these additional materials and costs you can safely transport glass with properly built picture boxes.

Anything can be packed using moving blankets, tape, paper, and shrink wrap.

The use of rolled sticky tape labels to put a unique item number is a godsend both for the owner and the movers. It makes it very simple to ensure that everything is accounted for when loading or unloading. Just watch and ensure that an item has a lable before it is loaded or enters the new location and accounted for on the inventory.

Temporary Living

The other side of using professional movers is the fact that you will be without your personal goods for a short to a long term. Unless your goods can fill an entire truck they are typically combined with others and this will result in scheduling efforts that can take some time before your items even leave the source storage facility.

Living out of hotels can be rather expensive and unless you are in a truly cheap solution it is typically more cost effective to live out of the home or apartment you want to finally reach.

Some items can be invaluable for this temporary living situation until your items arrive.

Bed

You can either use an inflatable mattress to sleep on which is very compact for transportation. The ones with the air pump included are easier. Pick one up for $30-$40 and it is useful the next time guests visit.

If you decided you didn’t want to move your mattress and box springs and want to get a new one at the your destination then go ahead and purchase one before your stuff arrives. It can just sit on the floor until your frame arrives.

Either bring or purchase sheets and comforters for either solution.

Bathroom equipment

Bring a spare towel and washcloth. You can use a second towel as a floormat.

Bring a showcurtain.

Kitchen Supplies

Paper plates, paper bowls, plastic cups, and plastic cutlery can get you by for long enough depending on what you decide to shop for. Things like cereal, sandwiches, salads, and other simple things that do not require much cooking with keep you going and avoid breakign the bank on food.

Bring trashbags and storage bags of various sizes.

Light

Most apartments and some homes are wired for the light switch to control a power outlet and as a result there is not light in rooms such as bedrooms. Bring a small lamp to ensure you have light.

Seating

Laying on a bed or sitting on a floor can easily become uncomfortable quickly when that is all you are doing in the temporary living location. While finding excuses to get excercise is good it is not always possible depending on where you are and what else is filling your time. Bringing a chair as simple as a folding chair or an camping chair can be very helpful to avoid comfort issues by sitting on the floor all the time.

Power Strip

With the advent of cell phones and laptops it might not be uncommon that you need more power then a wall outlet can provide next to your temporary bed. Bring a power strip to ensure you have access to everything.

Enterainment (optional)

Depending on your needs it might be practical to bring a smaller tv and movie player with you which will allow you to connect services such as cable or internet to the new location before you items arrive. Not a necessity by any means, but this can help avoid the waiting time stretch out too long.

 

 

Leasing

With as many times as I have relocated across the country and found various apartments to live in for each stint of various lengths it never ceases to amaze me that each location, apartment owner, or leasing representatives always find a way to create a minor crisis that has to be overcome. I accept this fact based on the previous pain and suffering that each even while completely avoidable results in  scrambling and momentary panic. 

What is amazing is that for the folks that do this for a living one would think that they would make their own life easier by just making everything clear up front and avoiding any thing resembling an assumption to avoid complications. But then again this would be the same as expecting common sense to be common. 

On the other hand I will just resign myself to the fact that this will be just practice for the closing process of buying a home since that will have suprises and mystery as well.