VBA Write to the registry

[TXSkydiver]

I am writing an app in Access to help maintain a standard set of files, programs and updates for all the people on my team.

The only piece I am missing is the ability to write to the registry. I know the exact key I need to change, I just need to know the code to edit the value in the key.

It's for another software application, so I will need to be able to specify a full path (HKEY_CURRENT_USER\Software\TRC Consultants\PHDWin 2.75\System Preferences\Default Locations\" )

The key name is GraphsSubdir

I am finding all kinds of ways to do this in VB, but I am unfortunately limited to VBA in this instance.

Thanks!!!!

[X-BRichard-X]

I actually encountered a similar challenge a while back where I needed to write to the registry using VBA. I will tell you that my research yielded some very interesting findings which I will share with you.

First, you should know that while it is possible to write to the registry using VBA, you are LIMITED in the extent to which you can do so. In fact, all code associated with VBA registry functions is written to and read from ONLY the following registry path:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\

Different operating systems will have different paths or names to the same location, viz, "VB and VBA Program Settings."

So, at the very least, you are going to have to be creative if you want to use the registry to store settings against which you may later want to programmatically access.

Consequently,

there are (4) commands contained within the VBA language that are used to directly work with the Windows Registry. The methods are found under the INTERACTION namespace and are as follows:


Interaction.DeleteSetting
Interaction.SaveSetting
Interaction.GetSetting
Interaction.GetAllSettings

'The following VBA code retrieves a setting from the Windows Registry as described below:



Dim RegEntry As String
RegEntry = Interaction.GetSetting("Recurrency Project", "Integrated Communications", "Processing Line", 3)


'NOTE1: The 3 value (fourth argument) serves as a default key value to return when the GetSetting function is executed


The information above comes directly from out of my code library. I hope this helps your efforts.

[matthewspatrick]

X-BRichard-X is correct about how to manipulate the Registry using only native VBA capabilities. However, using WScript allows you more complete access to the Registry. The snippet below shows how to instantiate a WScript.Shell object and use it to create or update any Registry key.

As always, take great care with the Registry: the wrong move could cause you a world of technical hurt :nervous:


Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.RegWrite "HKCU\Testing\Testing\OneTwoThree", "foo", "REG_SZ"
Set wsh = Nothing

[X-BRichard-X]

Just to add to Matthew Patrick's post, you might want to take writing to the registry FURTHER. I actually did not know that one could write to the registry beyond what was natively offered in VBA but apparently you can.

In fact, instead of using the code provided by Matthew Patrick, you should use the Windows Script Host Object Model COM library. Why? Because by instantiating this COM, you will have direct access to multiple methods and properties through intellisense.

The following code essentially replicates Matthew Patrick's code, but with the added asset of intellisense. Once intellisense is available, it becomes much easier to Write to, read from or otherwise edit ANY part of the Windows Registry.

'NOTE: To write to, read from or otherwise edit the registry without being limited to a specific location, you must use the Windows Script Host Object Model library.
'Create a reference to the Windows Script Host Object Model library then use the code below to write a registry key. Use the RegDelete and RegRead methods to delete a key and get a key respectively.

Dim wsh As New WshShell
wsh.RegWrite "HKCU\Testing\Testing\OneTwoThree", "foo", "REG_SZ" 'Sample key to write; notice the abbreviated class root name 'HKCU'
Set wsh = Nothing
EndSub

[Bob Phillips]

You are using exactly the same function, it is just that you are using early binding whereas matthewspatrick uses late binding. Early binding gives you access to intellisense.

[X-BRichard-X]

...and in my opinion Early Binding is MUCH better on performance and application robustness than Late Binding.

I believe there are performance gains made with the use of early binding.....

[matthewspatrick]

Yes, early binding tends to run faster, and it enables Intellisense, both marks in its favor.

Late binding can be better, though, when you may have to support multiple versions of an app (for example, if I am automating Excel from Access, and my users may not all be on the same version of Office as I am), and in expert forums such as this, it eliminates the step of having to explain to Askers how to add a reference to the project :devil2:

Honestly though, I hardly think the relative pros and cons of late vs early binding are worth arguing strenuously about...

[Dr.K]

Holy crap, this is extremely cool!
It never occured to me that I could store info in the registry!

I have a ToolPak Add-In that I dsitribute internally, and currently I use cells on one of the hidden sheets to store user info and version info. Sometimes its tricky to juggle this stuff when updating an old version with a new version. Storing this info in place external to Excel and VBA would have some advantages.

I'll have to play around some more with this.
Someone should make a KB entry, if there isn't one already.