VBA - Visual Basic for Applications

Whenever you have a list of unique key values identifying one file in a directory, but the file name contains other text as well it is possible to use wildcards in order to open the file you want.

Below code snippet will do the trick for you.

Source Code

The function Dir() returns an empty String (""), in case a file could not be identified. Therefore you will need extra handling for this case.

In case more than one file was identified by Dir(), only the first file (whichever the first may be) is returned.

1
2
3
4
5
6
Dim sFound As String
 
sFound = Dir(ActiveWorkbook.Path & "\302113*.xlsm")    'the first one found
If sFound <> "" Then
    Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If
 

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

 

I am Mr. Forgetful. What was this password again?

Anyway, if you do not know any better, use brute force!

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
27
28
29
30
31
' @Author - Alexander Bolte
' @ChangeDate - 2013-12-15
' @Description - Removes the password from a protected worksheet using brute force.
' Select a protected worksheet and start the sub.
Sub PasswordBreaker()
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    Dim trg As Worksheet
  
    On Error Resume Next
  
    Set trg = ActiveSheet
  
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
        trg.Unprotect Chr(i) & Chr(j) & Chr(k) & _
            Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
            Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
        If trg.ProtectContents = False Then
            MsgBox "One usable password is " & Chr(i) & Chr(j) & _
              Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
              Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n), vbInformation, ";0)"
            Exit Sub
        End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

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.