WriteTempFile(): A Simple VBA Function to Save Text to a New Temporary File

This is a no-frills function with a very specific purpose:

Write the contents of a string variable to a temporary file.

You can customize the file extension if you need something other than the default ".txt", but that's about it.

• You can't choose the destination folder.
• You can't set a custom file name or prefix.

By keeping the goal of this function narrowly defined, we can create a robust solution with a minimal amount of code.

The Approach

We use Environ("TMP") to get the temporary folder location (since that's all the API and FileSystemObject methods are doing anyway).

We base the file name on a globally unique identifier so that we don't have to worry about naming collisions or checking to see if a file already exists.

We use the built-in VBA Open, Print, and Close statements to minimize external dependencies.

The Algorithm

1. Build a guaranteed-to-be-unique temporary file name
2. "Print" the string in the Contents argument to the file
3. Return the full path to the temporary file name

The Function

Here is the WriteTempFile() function on its own without its required dependencies.

' ----------------------------------------------------------------
' Procedure : WriteTempFile
' Date      : 10/11/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/writetempfile/
' Purpose   : Save the passed Contents to a GUID-based temporary file.
' Returns   : The full path to the temporary file.
' Notes     - Since the file name is based on a GUID, we don't
'               need to check if the file already exists.
' ----------------------------------------------------------------
Function WriteTempFile(Contents As String, _
              Optional FileExt As String = "txt") As String
    'Build a guaranteed-to-be-unique temporary file name
    Dim fpTemp As String
    fpTemp = GetGuidBasedTempPath(FileExt)
    
    'Save the string in the Contents argument to the file
    Dim FNum As Integer
    FNum = FreeFile()
    Open fpTemp For Output As FNum
    'trailing semi-colon needed to prevent adding blank line at end of file
    '  see: http://stackoverflow.com/a/9445141/154439
    Print #FNum, Contents;
    Close #FNum
 
    'Return the full path to the temporary file name
    WriteTempFile = fpTemp
End Function

The Full Code

The code below includes required helper functions that I've written about in the past:

• CreateGuid: A Reliable Way to Generate GUIDs in VBA
• A GUID-Based Temporary File Name Generator

The code can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

Option Compare Database
Option Explicit

'--== Declare lines for CreateGUID() ==--
#If VBA7 Then
    Private Declare PtrSafe Function CoCreateGuid Lib "ole32" (id As Any) As Long
#Else
    Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
#End If


' ----------------------------------------------------------------
' Procedure : WriteTempFile
' Date      : 10/11/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/writetempfile/
' Purpose   : Save the passed Contents to a GUID-based temporary file.
' Returns   : The full path to the temporary file.
' Notes     - Since the file name is based on a GUID, we don't
'               need to check if the file already exists.
' ----------------------------------------------------------------
Function WriteTempFile(Contents As String, Optional FileExt As String = "txt") As String
    'Build a guaranteed-to-be-unique temporary file name
    Dim fpTemp As String
    fpTemp = GetGuidBasedTempPath(FileExt)
    
    'Save the string in the Contents argument to the file
    Dim FNum As Integer
    FNum = FreeFile()
    Open fpTemp For Output As FNum
    'trailing semi-colon needed to prevent adding blank line at end of file
    '  see: http://stackoverflow.com/a/9445141/154439
    Print #FNum, Contents;
    Close #FNum
 
    'Return the full path to the temporary file name
    WriteTempFile = fpTemp
End Function

' ----------------------------------------------------------------
' Procedure : GetGuidBasedTempPath
' Date      : 9/30/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getguidbasedtemppath/
' Purpose   : Returns an unused full path in the user's temp folder.
' Requires  : CreateGUID() from https://nolongerset.com/createguid/
' Notes     - For efficiency, the file name is based on a GUID which
'               obviates the need to check for an existing file.
' Usage     : ?GetGuidBasedTempPath()  'produces something like:
'   C:\Users\Mike\AppData\Local\Temp\11132708-F988-E84A-ADB0-F27F133B2116.tmp
' ----------------------------------------------------------------
Private Function GetGuidBasedTempPath(Optional FileExt As String = "tmp")
    'Build the destination folder with trailing slash
    Dim foDest As String
    foDest = Environ("TMP")
    If Right(foDest, 1) <> "\" Then foDest = foDest & "\"
    
    'Build the destination filename with extension
    Dim fnDest As String
    fnDest = CreateGUID() & "." & FileExt
    
    'Combine the folder and filename to create the full path
    GetGuidBasedTempPath = foDest & fnDest
End Function


' ----------------------------------------------------------------
' Procedure  : CreateGUID
' Author     : Dan (webmaster@1stchoiceav.com)
' Source     : http://allapi.mentalis.org/apilist/CDB74B0DFA5C75B7C6AFE60D3295A96F.html
' Adapted by : Mike Wolfe
' Republished: https://nolongerset.com/createguid/
' Date       : 8/5/2022
' ----------------------------------------------------------------
Private Function CreateGUID() As String
    Const S_OK As Long = 0
    Dim id(0 To 15) As Byte
    Dim Cnt As Long, GUID As String
    If CoCreateGuid(id(0)) = S_OK Then
        For Cnt = 0 To 15
            CreateGUID = CreateGUID & IIf(id(Cnt) < 16, "0", "") + Hex$(id(Cnt))
        Next Cnt
        CreateGUID = Left$(CreateGUID, 8) & "-" & _
                     Mid$(CreateGUID, 9, 4) & "-" & _
                     Mid$(CreateGUID, 13, 4) & "-" & _
                     Mid$(CreateGUID, 17, 4) & "-" & _
                     Right$(CreateGUID, 12)
    Else
        MsgBox "Error while creating GUID!"
    End If
End Function

Sample Usage

Image by congerdesign from Pixabay