VBA Archives - Page 2 of 2 - Francesco Foti's weblog

Manipulating filenames in Excel or Access, or VB/A

Do you often need to manipulate filenames in VBA ?Here’s a small set of very useful functions that I regularly use: GetFileExt

Public Function GetFileExt(ByRef psFilename As String) As String

1	Public Function GetFileExt(ByRef psFilename As String) As String

Get the file extension part of a filename, without the leading dot (“.”). Example (type in immediate window):

  ?GetFileExt("test.txt")
  txt
  ?GetFileExt("https://www.example.com/index.html")
  html

?GetFileExt("test.txt")

txt

?GetFileExt("https://www.example.com/index.html")

html

StripFileExt

Public Function StripFileExt(ByRef psFilename As String) As String

1	Public Function StripFileExt(ByRef psFilename As String) As String

Get the left part of a filename (and path), without… Continue reading Manipulating filenames in Excel or Access, or VB/A

A simplified sprintf function for VBA

Did you ever need to quickly replace some placeholders in a string with a variable number of literals or variable values ? Here’s a ssprintf() VB/A variant of C’s sprintf() function. Let’s say that the first “s” in the name stands for “simplified”. We’re – of course – far away from the full fledged power… Continue reading A simplified sprintf function for VBA

The case for the accented characters

Do you want of the get rid of the accented characters when you convert a string to upper case in VBA ? Sure, if you’re developing an Access (or another Office – Excel, Word, etc… – or VB/A) application targeting french, you’ll want to sometimes avoid the À for Ucase$(“à”). I’m speaking of the default… Continue reading The case for the accented characters

How to get the same result in VBA and in PHP computing a CCITT CRC checksum

I was working last week on a Microsoft Access application and I needed to get some data from a webserver running Apache/PHP. Exchanging data with the server was easy, the WinHTTP and XML ActiveX library components are really cool and easy to use for that. But then I came unto some unexpected trouble when I… Continue reading How to get the same result in VBA and in PHP computing a CCITT CRC checksum

Share the Clipboard with VBA and the Windows API

Accessing the clipboard in Windows becomes quite tricky in VBA, particularly if you have to stay 32 and 64 bits compatible. Here’s my compilation of portable routines, inspired by MSDN, Internet scraping and good sense. First the Declares section (I know, it pours out of the frame on the right, just select and copy the… Continue reading Share the Clipboard with VBA and the Windows API

Microsoft Access Databases ADO connection strings for 32/64 bits

If you try to open a connection to an Access database via ADO, you’ll have to use a different provider, whether you’re coding for 32 or 64 bits environments. Here’s a utility function I use for that (updated 25.02.2014):

Public Function ADOGetAccessConnString(ByVal psDatabasePathname As String) As String
  Dim sConnString   As String
#If Win64 Then
  sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & psDatabasePathname
#Else
  If GetFileExt(psDatabasePathname) = "accdb" Then
    sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & psDatabasePathname
  Else
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & psDatabasePathname
  End If
#End If
  ADOGetAccessConnString = sConnString
End Function

Public Function ADOGetAccessConnString(ByVal psDatabasePathname As String) As String

Dim sConnString As String

#If Win64 Then

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & psDatabasePathname

#Else

If GetFileExt(psDatabasePathname) = "accdb" Then

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & psDatabasePathname

Else

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & psDatabasePathname

End If

#End If

ADOGetAccessConnString = sConnString

End Function

Of course you have to have the correct jet engine files installed on the target… Continue reading Microsoft Access Databases ADO connection strings for 32/64 bits