VBA - Visual Basic for Applications

In case you need the temp directory on a Windows machine in VBA, the following method might do the trick for you.

Source Code

It looks up the TMP or TEMP system environment variable.

1
2
3
4
5
6
7
8
9
10
11
12
13
' @Author - Alexander Bolte
' @ChangeDate - 2017-05-09
' @Description - Gets the path to a directory either available
' under the "TMP" or "TEMP" system environment variable.
' @Returns a String providing the directory registered in the users
' system under the corresponding environment variable.
Public Function getTempDirectory() As String
    Dim dirPath As String
    
    dirPath = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))
    
    getTempDirectory = dirPath
End Function

 

Saving an Excel workbook is a rather simple task. However you have to regard many things in IO operations, which can go wrong.

Therefore it is always good to  add some extra exception handling for IO operations that are likely to fail.

Hence, although saving a workbook requires only one line of code I use below method for saving workbooks.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-26
' @Description - Saving handed workbook under provided path.
' @Param wrk - an initialized object of type Workbook.
' @Param trgPath - a String holding the target path the handed workbook to be saved to.
' @Returns - true, if the Workbook has been saved successfully, else false.
' @Remarks - the workbook is handed by reference. Therefore the calling function has to take care of closing it properly after has been saved.
' This function is only saving a handed workbook.
Public Function saveWorkbook(ByRef wrk As Workbook, ByVal trgPath As String) As Boolean
    Dim isSaved As Boolean
    
    On Error GoTo errHandle:
    
    wrk.SaveAs trgPath
    isSaved = True
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    saveWorkbook = isSaved
End Function 

In one of my projects I had to read all worksheet names from several Excel files.

Here is the code for getting all worksheet names from a closed or already opened Excel file.

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
27
28
' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in workbook available under given file path.
' @Param filePath - Path to Microsoft Excel file the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel file.
Public Function getSheetNames(ByVal filePath As String) As VBA.Collection
    Dim wrk As Workbook
    Dim sheetNames As New VBA.Collection
    
    On Error GoTo errHandle:
    
    Set wrk = getOpenedWorkbook(filePath)
    If wrk Is Nothing Then
        Set wrk = Application.Workbooks.Open(filePath)
    End If
    ' Get the sheet names for one file.
    Set sheetNames = getSheetNamesFromWorkbook(wrk)
    ' Close workbook without saving.
    wrk.Close False
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    Set wrk = Nothing
    
    Set getSheetNames = sheetNames
End Function

Referenced Functions can be found below. 

getSheetNamesFromWorkbook(wrk)
Getting all sheet names from a workbook
getOpenedWorkbook(filePath)
Getting an opened Excel workbook

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.