VBA - Visual Basic for Applications

If you want to export a collection of type Scripting.Dictionary into a text file the below function may come handy.

If you have to create and send an email automatically, the following method might help.

I have not tested it with HTML yet, but will do that at some point.

The method requires MS Outlook to be installed, since it is automating Outlook  in order to create an email with attachment.

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-15
' @Description - exports all queries available in current database query definitions.
' @Param trgDir - a String representing a target directory to export
' all queries in current query definition.
' @Returns true, if all queries have been successfully exported into provided target directory.
Function exportQueries(ByVal trgDir As String) As Boolean
    Dim sql As String
    Dim name As String
    Dim q As Object
    Dim trgFile As String
 
    ' Reference a query.
    For Each q In CurrentDb.QueryDefs
        ' Get the SQL from a referenced query as text.
        sql = q.sql
        ' Get a queries name.
        name = q.name
        ' Replace special characters in file name.
        trgFile = trgDir & "\" & VBATools.replaceSpecialCharacters(name) & ".sql"
        ' Delete the target file, if already existing.
        Call VBATools.deleteFileOnHD(trgFile)
        ' Write the query text into a separate text file.
        Call VBATools.writeLineToTextFile(trgFile, sql, False)
    Next
End Function

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).

VBScript to delete file

Subcategories

This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.

Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.