API: Copy variables/control contents to memory

I used to have this bookmarked but somewhere along the line I lost it: API: Copy variables/control contents to memory from The Access Web. I stumbled across it today when reading this post on UtterAccess.com.

I have found copying things to and from the clipboard to be a useful feature in several of my applications. Here's one I built in Word. This little macro copies the current document path to the clipboard.

Public Sub PathToClipboard()
    With Application.ActiveDocument
        ClipBoard_SetText .Path & "\" & .Name
    End With
End Sub

(You'll need the code from The Access Web link above in addition to this little snippet.) I found I often had to give a colleague the path to a document I had just updated on a shared drive somewhere. Using this macro I would just hit the keyboard shortcut I had set up, pop over to email, and hit ctrl-v to paste. And there I'd have the full path to the document in my email.

There are lots of other applications for the putting data on the clipboard, so it's a good thing to have in your toolkit- bookmark the post on The Access Web and you'll have it when you need it.

Getting the path to windows special folders

Sometimes your VBA program needs the path to the users desktop, or to a temp folder where working files can be placed, or to the user's documents folder. There are several ways to do this.

There's some great API code here at The Access Web.

You can also do it using the Windows Scripting Host. That method, as well as another API method, is described here, in a previous blog post. That post talks specifically about getting the path to the temp folder. I can't see that choice within the Access Web code above. Perhaps no one approach offers all the special paths. In the previous blog post linked earlier in the paragraph I use GetSpecialFolder() to get the temp folder path. You can get a couple of other special folders with that method as well- see the Microsoft documentation for details.

Office VBA and the Windows API

Here's a great overview of using the Windows API with VBA applications: Office VBA and the Windows API

**Edit Sept 16 '08: The link above doesn't seem to work anymore, but I think the same article is at http://msdn.microsoft.com/en-us/library/aa201293.aspx.

Getting the path for the "Temp" folder

Way back in the days of DOS, and the early days of windows, you could usually put temporary files in C:\TEMP. Today Windows provides TEMP folders, but they won't be in the root of C:. In XP for instance the path will be C:\Documents and Settings\username\Local Settings\Temp. If you want to use that folder in your program, such as to save a copy of a report for sending via e-mail, you'll need to get the specific path name. In this post I'll show you two ways to ask Windows for the path for the TEMP folder.

Using an API

Application Programming Interfaces (APIs) provide a way for your program to call Windows functions that aren't otherwise available. There's an API called GetTempPath that does just what we want. You'll usually need to build a function around API calls to make them easier to use in your program. Paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name.

' delaration of API to get the special folder name for temp objects
' (in XP this is, for example, C:\Documents and Settings\user.name\Local Settings\Temp\)
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferlength As Long, ByVal lpBuffer As String) As Long


Const MAX_PATH = 260 ' maximum length of path to return- needed by API call to provide a memory block to put result in


' function GetTmpPath
' return windows special folder for temp objects
' path will not include a backslash on the end
' path will be expressed using short (8.3) names for folders
    
Public Function GetWindowsTempPath()
    Dim strFolder As String ' API result is placed into this string
    
    strFolder = String(MAX_PATH, 0)
    If GetTempPath(MAX_PATH, strFolder) <> 0 Then
        GetWindowsTempPath = Left(strFolder, InStr(strFolder, Chr(0)) - 1)
    Else
        GetWindowsTempPath = vbNullString
    End If
    
    ' remove any trailing backslash
    If Right(GetWindowsTempPath, 1) = "\" Then
        GetWindowsTempPath = Left(GetWindowsTempPath, Len(GetWindowsTempPath) - 1)
    End If
    
End Function

Using the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. One of the many things the FileSystemObject can do for you is give you the paths to some of Windows special folders. Like the previous example, paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name. Because of the use of the FSO you'll need to go into Tools/References and add a reference to the Microsoft Scripting Runtime.

Public Function GetTempFolderName() As String
    ' use a FileSystemObject to returns the name of the user's temp folder
    ' in this case FSO is early bound- it's common to late bind with this library but
    ' this works well and provides compile time checking
    ' requires a reference to Microsoft Scripting Runtime
    ' path will not include a slash
    ' path will be expressed using short (8.3) names for folders
    
    Dim fso As Scripting.FileSystemObject ' an instance of the FileSystemObject
    
    Set fso = New FileSystemObject

    GetTempFolderName = fso.GetSpecialFolder(TemporaryFolder).Path
    
    Set fso = Nothing

End Function

Additional reading:

• Working with Files, Folders, and Drives: More VBA Tips and Tricks Includes a great overview of the FileSystemObject.
• Programming the FileSystemObject
• Windows API Microsoft reference for APIs.
• Getting The Path To Windows Special Folders

How to display and to use the File dialog box in Microsoft Access

This Microsoft article describes the use of the FileDialog method to show a select file dialog.

The FileDialog object is new (introduced with Access 2002). It is an alternative to the Common Dialog Control (which may not be available to you depending on your licensing) and the File Open/Save API (which is the generally recommended solution on the boards these days).

The FileDialog object doesn't work with the runtime version, and it requires a reference to the Microsoft Office library, but for my money it's simple and clean to code and it fits my needs.

Also note that the FileDialog method can be called from other VBA hosting applications, such as Word or Excel, with no changes becuase it's part of Office, not part of Access.

Also see: FileDialog Property [Access 2003 VBA Language Reference]  #