WriteTempFile(): A Simple VBA Function to Save Text to a New Temporary File
If you need to save a string to a temporary file in VBA, this simple function will do the trick.
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
- Build a guaranteed-to-be-unique temporary file name
- "Print" the string in the Contents argument to the file
- 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 FunctionThe Full Code
The code below includes required helper functions that I've written about in the past:
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 FunctionSample Usage
Image by congerdesign from Pixabay
