Bolte IT Consulting
VBA Export Query SQL into text file
The following method exports all available queries in an Access database into individual text files, which are called like the corresponding query.
Source Code
DAO does not provide the support ADODB gives you, therefore this method will only work with queries created and stored in a current MS Access database.
Resources
The method writeLineToTextFile is not a standard VBA method, but can be found at following URL.
The target encoding should not be UTF-16LE but ASCII, if you intend to use a versioning tool like GIT to keep track of changes in MS Access queries.
Write a String into a text file
Replacing special characters can be a pain, if you do not rely on regular expressions.
Replace special escape characters in String
Delete a file on a users hard disc (VBScript, which can easily be adjusted to VBA).
- Hits: 12481
Eclipse-Editor for VBScript
If you have to code in VBScript or other unpopular languages, it is up to you which tools you use for developing.
Since I like to code in Java and I have grown to love Eclipse I had a look for an editor that would allow me to combine useful tools like eGit with editing source code in Eclipse directly.
I found one that looks ok so far and it supports all kinds of text based programming languages. Below you can find the standard set of langugaes the editor is shipped with. It can be extended by a vast amount of other languages.
- C/C++
- C#
- CSS
- HTML
- Java
- JavaScript
- mySQL
- Perl
- PHP
- Ruby
- VBScript
- VisualBasic
- XML
- Hits: 23343
Write a String into a text file
Whenever I have to write data into a text file following function has proven to fulfill my needs.
Source Code
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: 23636
Register and Unregister Dynamic Link Library (DLL)
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.
Source Code
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.
Param | Value | Comment |
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.
Register DLL
The following method registers a dll or ocx.
Unregister DLL
The following method unregisters a dll or ocx.
Unit Tests
Below two simple unit tests.
References
SheelExecute
https://msdn.microsoft.com/en-us/library/windows/desktop/bb762153%28v=vs.85%29.aspx
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.
http://www.myengineeringworld.net/2013/06/register-unregister-dll-file-through-vba.html
The following URL finally gave me the hint I needed in order to get things running.
- Hits: 16988
Remove line breaks from Oracle column
If you want to remove line breaks or other characters from Oracle fields, the following article might help.
One scenario is for example export of data into text files.
SQL
The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').
The Oracle function 'translate' is applied on the whole String and might show better performance than regular expressions depending on the complexity of your regex.
Enjoy.
- Hits: 23196