Archive for the ‘Registry’ Category.

Getting the Printer Port

Do you know how windows appends that "on Ne01" to your ActivePrinter. In the old days we had to loop through all the possible digits to find which one didn't error out. Well no more!

Holger uses the registry to find the printer port. Very clever. However, if you have back slashes in your regestry key name, the scripting shell object won't work for retrieving them. To the shell, back slashes are path separators, so it's trying to navigate down some path that doesn't exist.

To overcome that problem, you can use Registration Manipulation Classes.

If your you're a late binding kind of a guy, use CreateObject("RegObj.Registry") in your code. With this dll, we can loop through all of the keys in a folder, like so:

Function GetPrinterPort(sPrinterName As String) As String
   
    Dim objReg As RegObj.Registry
    Dim objRootKey As RegObj.RegKey
    Dim sKey As String
    Dim objVal As RegObj.RegValue
    Dim sData As String
    Dim vData As Variant
   
    sKey = "\HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\"
    Set objReg = New RegObj.Registry
    Set objRootKey = objReg.RegKeyFromString(sKey)
   
    For Each objVal In objRootKey.Values
        If objVal.Name = sPrinterName Then
            sData = objVal.Value
            Exit For
        End If
    Next objVal
   
    If Len(sData)> 0 Then
        vData = Split(sData, ",")
        GetPrinterPort = vData(UBound(vData))
    Else
        GetPrinterPort = ""
    End If
   
    Set objReg = Nothing
   
End Function

I haven't tested that extensively, so use caution.

Installing An Excel Add-in Using Setup Factory

Being an MVP has some benefits. One of them is that some software companies offer Not For Resale (NFR) products to them for free.
Of course these companies hope the MVP's will advocate their products.

Well, for this one I will do so: Setup Factory.

I've started using this tool just a week ago and I must say I'm most impressed with it.

I published an article on my website today, which first discusses how Excel manages add-ins in the registry and then includes the needed scripts to include with Setup Factory to create and remove those registry entries.

Here is the article:

Installing An Excel Add-in Using Setup Factory

What is your favourite application to build setup files?

Regards,

Jan Karel Pieterse

A Setup Utility to install your add-ins (take two)

Hi everyone,

I've revisited this utility yesterday whilst travelling by train.
I was doing a review on a manuscript for book about Excel VBA and whilst testing some (faulty!) example code I discovered mine had a similar potential problem.

I've fixed this in the latest version.

Find the utility here.

A setup utility to install your add-ins

If you have created a nice add-in (see this article how to do that), a setup utility is an easy way for your users to install your addin. This free utility just needs you to change 4 cells to roll your own setup file.

Find the utility here.

More Registry Stuff

In addition to GetSetting and SaveSetting, VBA provides the GetAllSettings statement. This statement is used to get all of the settings for a particular section in the registry at one time. It returns a two dimensional array: the first dimension is the key and the second dimension is the key's value. For instance:

Sub UseGetAllSettings()

    Const sAPPNAME As String = "Euchre"
    Const sSECTION As String = "PlayerData"
    Dim vaSettings As Variant
    Dim i As Long
    
    SaveSetting sAPPNAME, sSECTION, "Name", "Paul"
    SaveSetting sAPPNAME, sSECTION, "Age", 35
    
    vaSettings = GetAllSettings(sAPPNAME, sSECTION)
    
    For i = LBound(vaSettings) To UBound(vaSettings)
        Debug.Print vaSettings(i, 0) & ":" & vbTab & vaSettings(i, 1)
    Next i
    
End Sub

Getallsettings

Storing Data in the Registry

You can store information relating to your Excel application in the registry. VBA provides an easy way to set and retrieve that information via the SaveSetting and GetSetting statements.

The arguments for these statements are AppName, Section, Key, and Setting. All of these arguments are data that you create. That is, there's no right or wrong AppName, it's whatever you want it to be. To be fair, there are some situations where that data can be wrong. First, when you using GetSetting, you have to specify the correct arguments – the same ones you used when with SaveSetting. If you don't, you won't get the correct information. Also, if you use the same arguments as some other program that's storing information in the registry, you will overwrite it/get the wrong data and generally create havoc. To avoid that, make sure you are using unique AppNames.

With SaveSetting, VBA creates the registry entry if it doesn't exist. If the entry doesn't exist when you use GetSetting, you get an empty string.

Below are three subs that demonstrate the use of GetSetting and SaveSetting. The sub ChangeName saves a name to the registry and the function GetName retrieves it.

Sub ChangeName(sName As String)

    SaveSetting appname:="Euchre", _
        section:="PlayerData", _
        key:="Name", _
        setting:=sName
    
End Sub

Function GetName()

    GetName = GetSetting(appname:="Euchre", _
        section:="PlayeData", _
        key:="Name")
        
End Function

Sub TestIt()

    ChangeName "Dick"
    MsgBox GetName
    
End Sub

Once ChangeName is run, the registry looks like this:

Registry1