How to get the Filename from a FilePath

I'm sure we all have helper functions. Those are the little functions we tend to use inside larger sections of code to simply return a specific piece of information for further processing. They are typically short with only a few lines of code in them.

As I was assisting a forum member today, I had to provide a way to extract the file's name from a given file path, so they can copy it to another folder location and rename the file at the same time. Just in case it may help others as well, the following code snippets are some of the several ways to accomplish that task.

This first one simply uses the InStrRev() function to locate the last backslash (\) in the file path to return the file's name.

Public Function iGetFilename(FilePath As String) As String
'thedbguy@gmail.com
'10/1/2021

iGetFilename = Mid(FilePath, InStrRev(FilePath, "\") + 1)

End Function

This next one uses the Split() function to divide the entire file path using the backslash as the slicer and then returns the last item in the array.

Public Function sGetFilename(FilePath As String) As String
'thedbguy@gmail.com
'10/1/2021

Dim strArr() As String

strArr() = Split(FilePath, "\")

sGetFilename = strArr(UBound(strArr))

End Function

I also got to thinking that we might be able to use a RegEx pattern to extract the file's name from the file path, and that's really the reason why I decided to post this blog today. I can't say the pattern I came up with is the best one to use. So, if anyone has a better one, please share it.

Public Function rGetFilename(FilePath As String) As String
'thedbguy@gmail.com
'10/1/2021

Dim regEx As Object
Dim regExMatch As Object

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .IgnoreCase = True
    .Pattern = "[^\\]+\.[a-z]{2,5}$"
    Set regExMatch = .Execute(FilePath)
End With

If regExMatch.Count > 0 Then
    rGetFilename = regExMatch(0).Value
End If

End Function

Additionally, I also found out another way of getting the file name using the FileSystemObject library. It turns out the FSO has many methods available for working with files. Here's an example.

Public Function fGetFilename(FilePath As String) As String
'thedbguy@gmail.com
'10/1/2021

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

fGetFilename = fso.GetFilename(FilePath)

Set fso = Nothing

End Function

As always, thank you for reading this and please let me know if you have any comments regarding this topic or any of the code I presented above. Cheers!

Copy Folder Structure

Hello there! First, I must apologize for the title of this article. I chose it because I think most Internet searches use a phrase similar to it, and I wanted to make sure people get to see and consider this information in their projects.

So, I am not really going to discuss the steps on how to duplicate a folder structure using VBA because there are already plenty of articles available on how to do it. Instead, I am just going to share a little bit of information I discovered yesterday as I was working on a utility for backing up my files from one drive to another.

In this utility, I needed a way to keep the folder structure the same on both the source and the target drives, so I can automatically compare the files between them and make sure I have the latest copy in the backup location. Listing the files and comparing them was easy enough to do. It was when I have a new source file to copy to the backup location where I ran into the issue of having to create a folder or subfolder on the other drive. (I might have a new source file located in a folder a few levels deep because I don't necessarily do a backup at a regular interval.)

We all know we can use the VBA MkDiR() function to create a folder or the CreateFolder method of the File System Object (FSO) to do the same. However, both of these methods suffer from the same issue; which is, they can only create a folder one level deep at a time. For example, using MkDir("C:\MyNewFolder") will create a folder called "MyNewFolder" under the root folder of the C: drive. Issuing the following command: MkDir("C:\MyOtherFolder\MySubFolder") will get a runtime error "76: Path not found" if C:\MyOtherFolder does not yet exist.

As a result, most articles on copying a folder structure use a recursive approach where the procedure walks the directory tree, one level at a time, creating each branch on its way to the end. So, in the above example, a recursive function might first check if C:\MyOtherFolder exists and creates it if necessary. Then, it will go down and check for the existence of the MySubFolder folder and then create it as needed. Both the VBA and FSO methods can be used in this recursive approach. If you want to see an example of a recursive approach for copying a folder structure, please let me know in the comments below, and I will post some links to a few articles showing how to do it (or I might post an example code).

So, back to the topic I wanted to share... Although there is nothing wrong with using a recursive approach to create a folder structure (I like using recursion, myself, on some of my projects), I thought it would have been nice to create the entire tree (actually, just one branch, but all the way to the leaf) in one instance (as in, one command or one line of code). This is exactly what I discovered yesterday and wanted to share with all of you.

As it turns out, the DOS command MKDIR or MD can do exactly what I was looking for. So, if you open up a DOS prompt and enter the command MD C:\Folder1\Folder2\Folder3, DOS will create this folder structure, if it doesn't already exist. I thought, how cool was that? So, how can I use this in VBA? I'm glad you asked. Here's the one line command I ended up using in my backup utility.

ws.Run "cmd /c mkdir """ & strTargetFolder & """"

where ws is a Shell object I created at the beginning of my procedure as follows:

Set ws = CreateObject("WScript.Shell")

and strTargetFolder is the folder structure for the new file. For example, "C:\MyFiles\ProjectName\Phase1\StatusUpdates\"

So, although recursion is a perfectly valid approach for copying a folder structure, I was just happy to find out another way to do the same without recursion and wanted to share it with everyone, in case some of you might also find it interesting and decide to use this approach in your projects.

Of course, we can discuss the pros and cons of each approach, but that's what the comments section is for. So, please let me know your thoughts, and I will be happy to discuss them below.

As always, thank you for reading, and I hope some of you will find this article useful.

Cheers!

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files in a folder or directory, I thought this was challenging enough for me to give it a try.

The original poster's request was to allow the user to search the folder for files matching a keyword and a list of specific file extensions. Also, the search should include all files inside subfolders.

Many of the examples I have seen use a loop to go through the folder structure and list all the files in them. Since I am a bit fascinated with recursion, I thought I would give it a try using a different approach. As a result of my attempt, I have created this demo.

Click on the above image to download the demo.

To use this demo, the user selects a starting folder path and enter an optional search keyword or list of file extensions to search. The search keyword is used to match the file names, while the list of file extensions will return only the files with matching file extensions.

The demo presents the result of the search using a Listbox. Double-clicking on an item in the Listbox will attempt to open the file for viewing. This version of the demo (v1.0) currently uses a Value List in the Row Source of the Listbox, which limits the number of files allowed to be displayed. The demo can be modified to store the file names in a table to allow the Listbox to display more items. I might do this in future versions of the demo.

In the meantime, I hope you find this demo useful, or at least, find the approach a little interesting.

As usual, I would appreciate any comments or feedback regarding this demo.

Cheers!

How to Retrieve the Hard Disk's Serial Number

I found myself looking for a way to retrieve the serial number of the computer's hard drive as a way of preventing unauthorized copies of an Access app. I have done it before but couldn't remember how. To my surprise, I found several ways to accomplish this task. I decided to post a couple of those techniques here to help anyone else who may find himself or herself looking to do the same thing in the future.

Before we start, the first thing we need to realize is hard disk drives have more than one serial numbers. One of the serial numbers we can retrieve is the one assigned by the hard disk manufacturer. This serial number should stay consistent throughout the life of the equipment. The other serial number available to us is the logical serial number assigned by the operating system when a disk is formatted. The value for the logical serial number may change if the disk is reformatted. Each technique presented below depends on which serial number you are interested in retrieving.

Physical Disk Drive Serial Numbers

The following function uses Windows Management Instrumentation (WMI) to create a connection to the local computer. The "WinMgmts" moniker is used to create a WMI object. Once a WMI object is instantiated, we can use the InstancesOf method to query the machine for system information.

Public Function HDSerial() As String
'6/14/2017
'thedbguy@gmail.com
'Returns the hard disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objPM As Object
Dim strSN As String

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_PhysicalMedia")

For Each objPM In objWin32
    strSN = strSN & (";" + objPM.SerialNumber)

Next

HDSerial = Trim(Mid(strSN, 2))

errExit:
    Set objPM = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

Logicl Disk Drive Serial Numbers

The above function used the "Win32_PhysicalMedia" collection to reference all the physical drives connected to the computer. We can now modify the above function using "Win32_LogicalDisk" to get a collection of all logical or mapped drives connected to the computer for the current user.

Public Function LDSerialWMI(Optional DriveLetter As Variant) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objLD As Object
Dim strSN As Variant

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_LogicalDisk")

If IsMissing(DriveLetter) Then
    For Each objLD In objWin32
        DriveLetter = objLD.DeviceID
        strSN = strSN & (";" + DriveLetter + objLD.VolumeSerialNumber)

    Next

Else
    For Each objLD In objWin32
        DriveLetter = Left(DriveLetter,1) & ":"
        If DriveLetter = objLD.DeviceID Then
            strSN = ";" & objLD.VolumeSerialNumber

        End If    
    Next

End If

LDSerialWMI = Trim(Mid(strSN, 2))

errExit:
    Set objLD = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

However, there is a more straightforward way to get the serial number of a specific logical disk drive. The following function uses the File System Object.

Public Function LDSerialFSO(DriveLetter As String) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objFSO As Object
Dim objDrv As Object
Dim strSN As Variant

DriveLetter = Left(DriveLetter,1) & ":"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDrv = objFSO.GetDrive(DriveLetter)

If objDrv.IsReady Then
    strSN = objDrv.SerialNumber
Else
    strSN = Null
End If
    
LDSerialFSO = strSN

errExit:
    Set objFSO = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

There is an important difference between the two functions for logical disk drive presented above. Using WMI returns the serial number as HEX; whereas, using FSO returns the serial number as a Long Integer. However, you can use the Hex() function to either convert the return value from the LDSerialFSO() function to HEX or modify the LDSerialFSO() function to return the serial number as HEX.

I hope you find this post helpful. As usual, please feel free to submit your comments to let me know how I can improve these functions. Thank you!

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents of a text file into your database. This task may seem challenging at first because it involves an external file, but it doesn't have to be hard.

The OPEN Statement

There are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:

Open "C:\FolderName\FileName.txt" For Input As #1

The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:

Line Input #1, VariableName

After we are done reading its contents, we need to close the connection to the file with the following:

Close #1

Here's an example code snippet to read an entire text file into memory:

Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String

intFile = FreeFile()

strFile = "C:\FolderName\FileName.txt"

Open strFile For Input As #intFile

Do While Not EOF(#intFile)
     Line Input #intfile, strInput
     strText = strText & strInput
Loop

Close #intFile

Please notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.

The File System Object

My preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.


For instance, compare the following function, using FSO, to the one above, using the OPEN statement:

Public Function ReadTextFile(strFile As String) As String
'3/10/2016
'http://accessmvp.com/thedbguy

Dim fso As Object
Dim objFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then
     Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending
     ReadTextFile = objFile.ReadAll
End If

Set objFile = Nothing
Set fso = Nothing

End Function

The above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.


I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!