MS Office and VBA
This category holds articles regarding general things in MS Office VBA independent from the MS Office application.
Whenever I have to write data into a text file following function has proven to fulfill my needs.
In case you want to export a file in Unicode encoding you just hand True for the parameter isUtf16Le, which will cause the FileSystemObject to encode the file in UTF-16 with low byte order mark (little endian).
- Hits: 19179
The following code snippet will show you how to use regular expressions in VBA.
- Hits: 14055
If you have to register or unregister dynamic link libraries (dll) or OLE Custom Controls (ocx) via VBA you will find the following two methods helpful. OLE Custom Controls are actually dlls implementing a certain interface, which allows them to be utilized as OLE objects in programming languages.
Below methods utilize an elevated command line (cmd executed as administrator) in order to register dlls using the windows program regsvr32.exe. The following syntax is used to elevate the command line.
The part after the first comma can be replaced with any command you want to run in an elevated command line. This is potentially dangerous as you could do nasty stuff with an elevated command line. However, at least the user will be asked starting with Windows Vista, if he / she wants to allow the command to modify the system.
In order for the function ShellExecute to run as expected we will need to pass the following parameters.
|Handle||"cmd"||The cmd.exe, which path should be available from a systems path environment variable.|
|Command||"/c" must be placed at the beginning of the command String to be executed.|
|File||""||We do not have a file to execute an operation on.|
|Parameters||This is an undocumented feature of the method ShelExecute, which tells it to run the speciffied executable as Administrator.|
If you want the executable regsvr32.exe to display a message dialog every time a dll has been registered or unregistered successfully, you will have to remove the parameter "/s" from the command line String to be executed.
The call to regsvr32.exe will work fine for 32 Bit systems. For 64 Bit operating systems this will not work, unless the dll to be registered is placed in the correct directory. The directories to place a dll in are as follows.
- 32 Bit dll on 32 OS can be registered anywhere. You will not have to copy it to "C:/Windows/System32/".
- 32 Bit dll on 64 Bit OS must be registered in "C:/Windows/SysWOW64/".
- 64 Bit dll on 64 Bit OS should be registrable anywhere, but I did not test this. If anything you might have to copy it to "C:/Windows/System32/".
The call to regsvr32.exe is the same on 64 Bit and 32 Bit OS although there are two versions (32 Bit and 64 Bit) available on a 64 Bit OS. Windows will automatically determine if a dll is of 32 or 64 Bit and call the corresponding version of regsvr32.exe. However this only works, if the dll to be registered is placed in the correct directory (see above list).
Funny enough, the call of method ShellExecute does not work as expected, if the command is passed in a String variable. Maybe an issue to do with type conversion as it works fine if I pass the command variable declared as Variant.
The moethod is actually supposed to return a numeric value, but no matter if the call succeeds or fails it always seems to return 0, which would mean my system is out of resources. For now I will have to live without a proper error handling on this one.
The following method registers a dll or ocx.
The following method unregisters a dll or ocx.
Below two simple unit tests.
Powershell and manual registration of dll, however the options provided in the methods at below URL will only work, if you are logged in as administrator and since I am admin on my machine, I am not too sure even then it will work without an elevated command prompt. I ran into the error code 0x80040201, which basically says that I have insufficient rights to register or unregister the dll (see second URL).
My solution still prompts the user with a confirmation message but at least it works as long as the user is allowed to elevate the command prompt as admin.
The following URL finally gave me the hint I needed in order to get things running.
- Hits: 14290
Page 1 of 4