Recently a collegue had to import a SharePoint list into Excel for automated processing.

Below you can find the source code that I provided him with as an example.

Source Code

Sub LinkedSharePointList()
 
    ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
 
        Source:=Array(<"URL String">, <"ListNameString">, _
 
        <"{GUIDString}">), LinkSource:=True, Destination:=Range("A1")
 
End Sub

Last Updated: 25 July 2015

Hits: 5041

Whenever you have to interact with users there is information you would like to store outsie the lifetime of an application in order for it to be available at next startup.

The below class can assist in writing and reading property files into hidden temp directories like AppData in Windows systems. This way you can for example store user settings without having to deal with protecting and hiding Excel sheets. I think it is bad style to store information that only rarely is touched by a user in a workbook, which is used to process data. Unless Excel is not used as an interface to configure a lot of different parameters in an application, I use property files.

However the main advantage is that you can use a Scripting.Dictionary internally in the below class in order to ease getting and setting properties by name. This includes the following advantages compared to storing information in Excel files.

1. No duplicates possible.
2. No handling of indices in Excel sheets necessary.
3. Property files are more reliable because Excel sheets are more likely to be modified by users.

Source Code

Last Updated: 19 August 2015

Hits: 4796

With this collection of articles I want to build a wiki for myself as well as others.

The goal is to build not only a little framework for Excel VBA, but also a collection of general tips and hints on issues I experienced working with Excel and Visual Basic for Excel.

Once completed, I am guessing it never will be, this will serve me well. Like my wife always says, I am "Mr. Forgetful" and a "Lazy Bum".

Yes dear, I love you too.

And it's true, good programmers are lazy!

Last Updated: 01 June 2014

Hits: 4949