Friday, October 1, 2021

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

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

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

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

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!

Wednesday, August 25, 2021

SimpleCSV() v2.0

 A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of this function is it doesn't work with parameter queries.

I have always pointed people to Leigh's Generic Recordset function to deal with parameter queries and avoid the "3061. Too few parameters" error.

Today, I was trying to help someone in the forum and decided to combine the two functions together. The following updated version of the SimpleCSV() function should be able to handle certain parameter queries. Please let me know if you find any bug.

Option Compare Database
Option Explicit

Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'v1.0 - 8/20/2013
'v2.0 - 8/25/2021 Handles parameter queries

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Variant
Dim strCSV As String

Set db = CurrentDb()

Set qdf = db.CreateQueryDef("", "SELECT * FROM (" & strSQL & ")")

With qdf
    For Each prm In qdf.Parameters
        qdf.Parameters(prm.Name) = Eval(prm.Name)
End With

Set rs = qdf.OpenRecordset

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Function

Friday, March 5, 2021

Download a File from the Internet

 To download a file from the Internet, one option is to use the URLDownloadToFile API, which looks something like this.

#If VBA7 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                                (ByVal pCaller As Long, _
                                 ByVal szURL As String, _
                                 ByVal szFileName As String, _
                                 ByVal dwReserved As Long, _
                                 ByVal lpfnCB As Long) As Long
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                        (ByVal pCaller As Long, _
                         ByVal szURL As String, _
                         ByVal szFileName As String, _
                         ByVal dwReserved As Long, _
                         ByVal lpfnCB As Long) As Long
#End If

You could then use the above in a function like this.

lngResult = URLDownloadFile(0,URL,Filename,0,0) 

However, if you're like me and don't want to use API calls, you can also achieve the above goal by using a HTTPRequest class. 

Here's an example VBA subroutine to download a file from the Internet.

Public Sub DownloadFile(URL As String)
'used to download a file from the Internet
'assumes filename is at the end of the URL
'file is saved in the current project folder
'usage: DownloadFile ""

Dim objHTTP As Object
Dim FileByte() As Byte
Dim strFile As String
Dim intFile As Integer

intFile = FreeFile()
strFile = Mid(URL, InStrRev(URL, "/") + 1)

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

With objHTTP
    .Open "GET", URL, False
    If .Status = 200 Then
        FileByte = .responseBody
    End If
End With

Open CurrentProject.Path & "\" & strFile For Binary Lock Read Write As #intFile
    Put #intFile, , FileByte
Close #intFile

Set objHTTP = Nothing

End Sub

The above code does not include any error handlers, so you will have to add that part yourself. Also, the above code was not fully tested on all possible file types. It worked well for me when downloading image and PDF files though. Please let me know if you find a file type that this code was not able to handle.

As usual, thank you for reading and please let me know if you find this information useful.

Good luck with your project.

Friday, July 3, 2020

Get User's Full Name

I received an email from someone requesting assistance in Access on how to retrieve the user's full name from the system, not their username. I was told that Excel has the method Application.UserName available to do this.Not being familiar with Excel, I wasn't aware of that method.

Unfortunately, that method is not available in Access. However, I knew you can run some Excel methods from Access. For example, the following code should do the job.

Public Function GetUserXLFullName() As String

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")

GetUserXLFullName = xlApp.Application.UserName

Set xlApp = Nothing

End Function

But on the other hand, I also know I have used code to query the Active Directory before. As an example, here is a link to a function on my website on how to get the user's email address from Active Directory.

So, based on that example, a simple modification is all we need to get the user's Full Name from Active Directory. For instance:

Public Function GetUserADFullName() As String

Dim objADInfo As Object
Dim objADUser As Object

Set objADInfo = CreateObject("ADSystemInfo")
Set objADUser = GetObject("LDAP://" & objADInfo.UserName)

GetUserADFullName = objADUser.FullName

Set objADUser = Nothing
Set objADInfo = Nothing

End Function

While testing these two approaches, I discovered using the Active Directory method to be faster than using Excel. Please let me know if your experience is the same. Also, please tell us if you know another method of retrieving the user's full name from the system. Thank you for reading. Cheers!

Saturday, March 14, 2020

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had when I recently participated in a forum thread discussion on this subject. So, if I misspoke or provided inaccurate information below, please do not hesitate to let me know. I would really appreciate it.

If you are not familiar with "bitwise operation," it is basically a way to perform value calculations or comparisons at the bit level. For example, one might need to use bitwise operation to check if a particular bit within a value is either ON (1) or OFF (0). In essence, performing a bitwise operation is a way to determine the state (1 or 0) of each individual bit of a particular value.

There are [as far as I know] three bitwise operators: AND, OR, and XOR

The AND operator returns a value of 1 if both the bits being compared are 1.

The OR operator returns a value of 1 if either of the bits being compared is a 1.

And the XOR operator returns a value of 1 if, and only if, one of the bits being compared is a 1.

Okay, so how do we normally perform a bitwise operation in Access. Well, we would usually use VBA for this. For example, the following function can be used to perform an AND bitwise operation.

Public Function bitwiseAND(var As Long, bit As Long) As Boolean
'Returns True if the bit being tested is ON

bit = 2 ^ bit

bitwiseAND = var And bit

End Function

To check if bit position #3 is ON (remember, bit position is zero-based), we might do something like:


The above function returned True because 45 is represented as 101101 in binary, and the third bit from the right is a 1.

I hope the above example is clear. So, as you can see, we can use VBA in Access to perform bitwise operation. However, the topic of this article is how can we do it in an Access query? Obviously, we can use the above function in a query, but it would have been better if we can simply use the bitwise operators (AND, OR, and XOR) in SQL. Unfortunately, not in Access. However, the bitwise operators are available in T-SQL. So, one potential solution, if someone needs to perform bitwise operation against multiple sets of data in a query is to migrate the data into SQL Server.

Okay, now we come to the point of this article. So, a forum poster asked how to perform a bitwise AND operation in an Access query because, for some reason or another, they don't want to use VBA and migrating to SQL Server is not an option. I did a little research and came up with the following alternate solution.

([FieldName]\2^BitPosition) Mod 2

In the above expression, we perform an integer division against a numeric field using the decimal value of the bit position we want to check and then check if the result is divisible by 2. If the bit position being checked is ON, then the result of this expression will be a 1. If the bit is OFF, then the result will be a 0.

And there you have it. You can use the above expression in an Access query to check if a bit is on or off. I can't think of a good example or a situation where this solution would apply, so please let me know if you have any situation where this information could be useful.

As always, thanks for reading...