VBA - Visual Basic for Applications

It has been always annoying me that I could not select more than one column in the Excel Text Import Wizard / Assistant.

I had to select single columns in a preview window and then set a fields data type one by one even if all fields were of the same data type.

Using the Shift key you are able to work around this bug in the text import assistant.

Select the first column then press and hold shift key down and select a column to the right or left of the before selected column.

All columns in between the two selected will be selected as well and are available for property editing :0).

Below function displays a file picker dialog and returns the picked file path as String.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Displaying a Windows explorer standard file picker dialog to user and returning picked file path as String.
' @Returns - A String holding a full file path. If user aborted file picker dialog the returned String is empty("").
Public Function getSingleFilePath() As String
Dim myFileDialog As FileDialog
Dim filePath As String

' Search for file.
Set myFileDialog = Application.FileDialog(msoFileDialogFilePicker)
myFileDialog.InitialView = msoFileDialogViewDetails

' Prompt dialog to user.
If myFileDialog.Show = -1 Then
filePath = myFileDialog.SelectedItems.Item(1)
End If

getSingleFilePath = filePath
End Function

If you ever wondered how to determine, if an opened workbook was saved before below you'll find a code snippet that might do the job for you.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'@Author - Alexander Bolte 
'@ChangeDate - 2014-05-30 
'@Description - Returns true, if a workbooks path property 
'is unequal an empty string. 
'This is true only if the workbook has been saved. 
'@Param wrk - an initialized Excel Workbook object. Function fails, if Nothing is handed. 
'@Returns true, if a given workbook has been saved. Else False.
Public Function wasWorkbookEverSaved(ByRef wrk As Workbook) As Boolean
  Dim ret As Boolean
  
  ret = wrk.path <> ""
  
  wasWorkbookEverSaved = ret
End Function

 

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.