VBA - Visual Basic for Applications

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 
Hits: 3807

Sometimes it is necessary to get a folder path from the user instead of receiving a file path.

The Excel Application class is providing a corresponding function, which is used in below method.

Source Code

Hits: 3689

If you need to know a files name from a file path it might be interesting to also know, if this file is alos existing.

Below you will find a method, which only returns a files name if the file is available in current file system.

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
24
25
26
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-21
' @Description - Returning a files name from a given file path.
' @Param filePath - a String providing a full path to one file.
' @Returns - a String holding a files name, if file exists and is accessible. Else an empty String is returned.
Public Function getFileNameFromFilePath(ByVal filePath As String) As String
    Dim fileName As String
    Dim fso As New scripting.FileSystemObject
    Dim f As scripting.File
    
    On Error GoTo errHandle:
    
    If fso.fileExists(filePath) Then
        Set f = fso.GetFile(filePath)
        fileName = f.Name
    End If
    Set f = Nothing
    Set fso = Nothing
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    getFileNameFromFilePath = fileName
End Function

Referenced API

Referenced APIs can be found below.

Mircosoft Scripting Runtime

 

Hits: 3754
  1. Find / search a value in a column
  2. Get record count in Recordset
  3. Deleting a worksheet
  4. Get Range object of NamedRange

Subcategories

Excel VBA Article Count:  30

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.

MS Office and VBA Article Count:  11

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

Power Point VBA Article Count:  1

Access VBA Article Count:  7

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

Access VBA DAO Article Count:  2