✔ VBA Dashboards Specialist®: VBA Excel - Trabalhando com Arquivo e Pasta temporários

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

Estou disponibilizando estes módulos que contém três procedimentos relacionados com arquivos e pastas temporárias.

O primeiro procedimento, GetTempFolderName, retorna o nome da pasta que está configurada para arquivos temporários no usuário atual. Ela retorna o nome da pasta que o sistema tenha especificado para o usuário atual para armazenar os arquivos temporários.

O segundo procedimento, GetTemporaryFolderName, é usado para obter o nome de uma pasta temporária e, opcionalmente, criá-la. Esta pasta será criada na pasta do usuário do sistema, temp, retornado pela função GetTempFolderName.

O terceiro processo, GetTempFile, é usado para obter o nome do arquivo que está garantido para ser exclusivo dentro da pasta na qual ele for criado.

A documentação de cada procedimento está incluído no próprio código .

Para que estes funcionem, usem a função GetSystemErrorMessageText, disponível aqui; e a função TrimToNull também disponível aqui.

Public Function TrimToNull (S As String) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''

' TrimToNull
' This returns the portion of the string S that
' is to the left of the first vbNullChar character.
' If vbNullChar is not found, the entire string is
' returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim Pos As Integer
Pos = InStr(1, S, vbNullChar)
If Pos > 0 Then
TrimToNull = Left(S, Pos - 1)
Else
TrimToNull = S
End If

End Function
Option Explicit
Option Compare Text

'''''''''''''''''''''''''''''''''''

' Maximum Length Of Full File Name
'''''''''''''''''''''''''''''''''''

Private Const MAX_PATH = 260 ' Windows Standard, from VC++ StdLib.h

Private Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" ( _
ByVal lpszPath As String, _
ByVal lpPrefixString As String, _
ByVal wUnique As Long, _
ByVal lpTempFileName As String) As Long

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" ( _

ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Private Declare Function PathGetCharType Lib "shlwapi.dll" _
Alias "PathGetCharTypeA" ( _

ByVal ch As Byte) As Long

GetTempFolderName

Public Function GetTempFolderName( _
Optional IncludeTrailingSlash As Boolean = False) As String

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' GetTempFolder
' This procedure returns the name of the folder that the system has designated
' for temporary files for the current user.
' Returns the name of the folder or vbNullString if an error

' occurred. The argument IncludeTrailingSlash indicates whether to include a
' trailing slash at the end of the folder name.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TempPath As String
Dim Length As Long
Dim Result As Long
Dim ErrorNumber As Long
Dim ErrorText As String

''''''''''''''''''''''''''''''''''''''

' Initialize the variables
''''''''''''''''''''''''''''''''''''''

TempPath = String(MAX_PATH, " ")
Length = MAX_PATH

'''''''''''''''''''''''''''''''''''''''''''''''''

' Get the Temporary Path using GetTempPath.
'''''''''''''''''''''''''''''''''''''''''''''''''

Result = GetTempPath(Length, TempPath)
If Result = 0 Then
'''''''''''''''''''''''''''''''''''''

' An error occurred
'''''''''''''''''''''''''''''''''''''

ErrorNumber = Err.LastDllError
ErrorText = GetSystemErrorMessageText(ErrorNumber)
MsgBox "An error occurred getting the temporary folder" & _
" from the GetTempFolderName function: " & vbCrLf & _

"Error: " & CStr(ErrorNumber) & " " & ErrorText
GetTempFolderName = vbNullString
Exit Function
Else
'''''''''''''''''''''''''''''''''''''''

' No error, but the buffer may have
' been too small.
'''''''''''''''''''''''''''''''''''''''

If Result > Length Then
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' The buffer TempPath was too small to hold the folder name.
' This should never happen if MAX_PATH is set to the proper
' value.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

MsgBox "The TempPath buffer is too small. It is allocated at " & _
CStr(Length) & " characters." & vbCrLf & _
"The required buffer size is: " & CStr(Result) & " characteres.", _

vbOKOnly, "GetTempFolderName"
GetTempFolderName = vbNullString
Exit Function
End If

' trim up the TempPath. It includes a trailing "\"

TempPath = TrimToNull(Text:=TempPath)

If IncludeTrailingSlash = False Then
'''''''''''''''''''''''''''''''''''''''''''''''''

' If IncludeTrailingSlash is false, get rid of
' the trailing slash.
'''''''''''''''''''''''''''''''''''''''''''''''''

TempPath = Left(TempPath, Len(TempPath) - 1)
End If
End If

GetTempFolderName = TempPath

End Function

GetTemporaryFolderName

Public Function GetTemporaryFolderName(Optional Create As Boolean = False) As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' GetTemporaryFolderName
' This function returns the name of a temporary folder name. The folder will be
' in the user's designated temp folder. If Create is True, the folder will

' be created.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FName As String
Dim FileName As String
Dim TempFolderName As String
Dim Pos As Integer

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Get a temp file name with no extension, located in the
' user's system-specified temporary folder.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

FName = GetTempFile(vbNullString, vbNullString, " ", False)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Find the location of the last "\" character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Pos = InStrRev(FName, "\", -1, vbTextCompare)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Get the filename (without the path) of the temp file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

FileName = Mid(FName, Pos + 1)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Get the user's system-specified temp folder name
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

TempFolderName = GetTempFolderName(IncludeTrailingSlash:=True)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' append FolderName to the full folder fname
TempFolderName = TempFolderName & FileName

''''''''''''''''''''''''''''''''''''''''

' Create the folder is requested.
''''''''''''''''''''''''''''''''''''''''

If Create = True Then
On Error Resume Next
Err.Clear
MkDir TempFolderName
If Err.Number <> 0 Then
MsgBox "An error occurred creating folder '" & TempFolderName & _

"'" & vbCrLf & _
"Err: " & CStr(Err.Number) & vbCrLf & _
"Description: " & Err.Description

GetTemporaryFolderName = vbNullString
Exit Function
End If
End If

''''''''''''''''''''''''''''''''''''''''

' return the result
''''''''''''''''''''''''''''''''''''''''

GetTemporaryFolderName = TempFolderName

End Function

GetTempFile

Public Function GetTempFile (Optional InFolder As String = vbNullString, _
Optional FileNamePrefix As String = vbNullString, _

Optional Extension As String = vbNullString, _
Optional CreateFile As Boolean = True)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' GetTempFileName
' This function will return the name of a temporary file, optionally suffixed with the
' string in the Extension variable. It will optionally create the file.
'
' If InFolder specifies an existing folder, the file will be created in that folder.
' If InFolder specifies a non-existant folder, the procedure will attempt to create
' the folder.

' If InFolder is vbNullString, the procedure will call GetTempFolderName to get
' the folder designated for temporary files.
' InFolder must be a fully qualified path. That is, a folder name begining with a

' network prefix "\\" or containing ":".

' If FileNamePrefix is specified, the file name will begin with the first three
' characters of this string. In this case, FileNamePrefix must be three characters

' with no spaces or illegal file name characters. These are validated with
' PathGetCharType. If FileNamePrefix is vbNullString, the value of C_DEFAULT_PREFIX
' will be used.
' If FileNamePrefix contains spaces or invalid characters, an error occurs.
'
' If Extension is specified, the filename will have that Extension. If must be three
' valid characters (no spaces). The characters are validated with PathGetCharType.

' If Extension is vbNullString the default extension from GetTempFileName ("tmp") is
' used. Do NOT put the period in front of the extension (e.g., use "xls" not ".xls").

' If Extension is a single space, the file name will have no extension.
'
' If CreateFile is omitted or True, the file will be created. If CreateFile is false,
' the file is not created. (Actually, it will be created by GetTempFileName and then

' KILLed.)
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim PathBuffer As String
Dim Prefix As String
Dim FolderPath As String
Dim Res As Long
Dim FileName As String
Dim ErrorNumber As Long

Dim ErrorText As String
Dim FileNumber As Integer

Const C_DEFAULT_PREFIX = "TMP"
FileName = String$(MAX_PATH, vbNullChar)

If InFolder = vbNullString Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' InFolder was an empty string. Call GetTempFolderName
' to get a temporary folder name.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

PathBuffer = GetTempFolderName(IncludeTrailingSlash:=True)
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' test to see if we have an absolute path
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (Left(InFolder, 2) = "\\") Or _
(InStr(1, InFolder, ":", vbTextCompare) > 0) Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' We have an absolute path. Test whether the folder exists.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Dir(InFolder, vbHidden + vbSystem + vbHidden + _
vbNormal + vbDirectory) = vbNullString Then
'''''''''''''''''''''''''''''''''''''''''''''''''''

' InFolder does not exist. Try to create it.
'''''''''''''''''''''''''''''''''''''''''''''''''''

On Error Resume Next
Err.Clear
MkDir InFolder
If Err.Number <> 0 Then
MsgBox "An error occurred creating the '" & InFolder _

& "' folder." & vbCrLf & _
"Error: " & CStr(Err.Number) & vbCrLf & _
"Description: " & Err.Description, vbOKOnly, "GetTempFileName"

GetTempFile = vbNullString
Exit Function
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' MkDir succussfully created the folder. Set PathBuffer to the new
' folder name.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

PathBuffer = InFolder
End If
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''

' InFolder exists. Set the PathBuffer variable to InFolder
'''''''''''''''''''''''''''''''''''''''''''''''''''

PathBuffer = InFolder
End If
Else
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' We don't have a fully qualified path. Get out with an error message.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

MsgBox "The InFolder parameter to GetTempFile is not an absolute file name.", _
vbOKOnly, "GetTempFileName"
GetTempFile = vbNullString
Exit Function
End If ' LEFT
End If ' InFolder = vbNullString

''''''''''''''''''''''''''''''''''''''''''

' Ensure we have a '\' at the end of the
' path.
'''''''''''''''''''''''''''''''''''''''''

If Right(PathBuffer, 1) <> "\" Then
PathBuffer = PathBuffer & "\"
End If

If FileNamePrefix = vbNullString Then
'''''''''''''''''''''''''''''''''''''''''

' FileNamePrefix is empty, use 'tmp'
'''''''''''''''''''''''''''''''''''''''''

Prefix = C_DEFAULT_PREFIX
Else
If IsValidFileNamePrefixOrExtension(Spec:=FileNamePrefix) = False Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' FileNamePrefix is invalid. Get out with an error.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

MsgBox "The file name prefix '" & FileNamePrefix & "' is invalid.", _
vbOKOnly, "GetTempFileName"
GetTempFile = vbNullString

Exit Function
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' FileNamePrefix is valid.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Prefix = FileNamePrefix
End If
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Get the temp file name. GetTempFileName will automatically
' create the file. If CreateFile is False, we'll have
' to Kill the file. We set wUnique to 0 to ensure that
' the filename will be unique. This has the side effect
' of creating the file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Res = GetTempFileName(lpszPath:=PathBuffer, _
lpPrefixString:=Prefix, _
wUnique:=0, _
lpTempFileName:=FileName)

If Res = 0 Then
''''''''''''''''''''''''''''
' An error occurred. Get out

' with an error message.
''''''''''''''''''''''''''''
ErrorNumber = Err.LastDllError

ErrorText = GetSystemErrorMessageText(ErrorNumber)
MsgBox "An error occurred with GetTempFileName" & vbCrLf & _
"Error: " & CStr(ErrorNumber) & vbCrLf & _

"Description: " & ErrorText, vbOKOnly, "GetTempFileName"
GetTempFile = vbNullString
Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''

' GetTempFileName put the file name in the
' FileName variable, ending with a vbNullChar.
' Trim to the the vbNullChar.
'''''''''''''''''''''''''''''''''''''''''''

FileName = TrimToNull(Text:=FileName)

'''''''''''''''''''''''''''''''''''''''''''

' GetTempFileName created a file with an
' extension of "tmp". If Extension was
' specified and is not a null string,
' change the extension to the specified

' extension. We'll use the same validation
' routine as we did for the prefix.
'''''''''''''''''''''''''''''''''''''''''''

If Extension = vbNullString Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

' If Extension is vbNullString, use the extension
' created by GetTEmpFileName ("tmp"). Test whether
' CreateFile is False. If False, we have to kill the
' newly created file.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

If CreateFile = False Then
On Error Resume Next
Kill FileName
Else
''''''''''''''''''''''''''''''''''

' CreateFile was true. Leave
' the newly created file in place
''''''''''''''''''''''''''''''''''

End If
Else ' Extension is not vbNullString
If Extension = " " Then
''''''''''''''''''''''''''''''''''''

' An Extension value of " " indicates
' that the filename should have no
' extension. First Kill FileName, modify
' the variable to have no extension, and then

' see if we need to create the file. If CreateFile
' if False, don't create the file. If True,
' create the file by openning it and then
' immmediately close it.

''''''''''''''''''''''''''''''''''''
On Error Resume Next

Kill FileName
On Error GoTo 0
FileName = Left(FileName, Len(FileName) - 4)
If CreateFile = True Then
''''''''''''''''''''''''''''''''''''''''

' Create the file by opening it for
' output, then immediately closing it.
''''''''''''''''''''''''''''''''''''''''

FileNumber = FreeFile
Open FileName For Output Access Write As #FileNumber
Close #FileNumber
Else
'''''''''''''''''''''''''''''''''''''''''

' CreateFile was false. Since we've already
' Killed the file created by GetTempFileName,
' do nothing.
''''''''''''''''''''''''''''''''''''''''''

End If


Else

If IsValidFileNamePrefixOrExtension(Spec:=Extension) Then
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' If we have a valid extension, kill the existing filename
' and the recreate the file with the new extension.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

On Error Resume Next
Kill FileName
On Error GoTo 0
FileName = Left(FileName, Len(FileName) - 4) & "." & Extension
If CreateFile = True Then
FileNumber = FreeFile
Open FileName For Output Access Write As #FileNumber
Close #FileNumber
Else

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' CreateFile was false. Since we've already killed the
' filename created by GetTempFileName, do nothing.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If
Else
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' The extension was not valid. Display an error and get out.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

MsgBox "The extension '" & Extension & "' is not valid.", _
vbOKOnly, "GetTempFileName"
GetTempFile = vbNullString

Exit Function
End If

End If
End If

''''''''''''''''''''''''''''''''''''''''''''

' We were successful. Return the filename.
''''''''''''''''''''''''''''''''''''''''''''

GetTempFile = FileName

End Function

Private Function IsValidFileNamePrefixOrExtension(Spec As String) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' IsValidFileNamePrefix
' This returns TRUE if Prefix is a valid 3 character filename
' prefix used with GetTempFileName
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const GCT_INVALID As Long = &H0
Const GCT_SEPARATOR As Long = &H8
Const GCT_WILD As Long = &H4
Const GCT_LFNCHAR As Long = &H1
Const GCT_SHORTCHAR As Long = &H2

Dim Ndx As Long
Dim B As Byte
'''''''''''''''''''''''''''''''''

' prefix contains a space. error.
'''''''''''''''''''''''''''''''''

If InStr(1, Spec, " ") > 0 Then
IsValidFileNamePrefixOrExtension = False
Exit Function
End If

'''''''''''''''''''''''''''''''''

' prefix is not 3 chars. error.
'''''''''''''''''''''''''''''''''

If Len(Spec) <> 3 Then
IsValidFileNamePrefixOrExtension = False
Exit Function
End If

'''''''''''''''''''''''''''''''''

' Loop through the 3 characters
' of Prefix. If we find an
' invalid character, get out with
' a result of False.
'''''''''''''''''''''''''''''''''

For Ndx = 1 To 3
B = CByte(Asc(Mid(Spec, Ndx, 1)))
Select Case PathGetCharType(B)
Case GCT_INVALID, GCT_SEPARATOR, GCT_WILD
IsValidFileNamePrefixOrExtension = False

Exit Function
Case GCT_LFNCHAR, GCT_SHORTCHAR, GCT_LFNCHAR + GCT_SHORTCHAR
Case Else
IsValidFileNamePrefixOrExtension = False
Exit Function

End Select
Next Ndx

'''''''''''''''''''''''''''''''''

' If we made it out of the loop,
' the Prefix was valid. Return
' True.
'''''''''''''''''''''''''''''''''

IsValidFileNamePrefixOrExtension = True

End Function

Public Function TrimToNull(S As String) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''

' TrimToNull
' This returns the portion of the string S that
' is to the left of the first vbNullChar character.
' If vbNullChar is not found, the entire string is
' returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim Pos As Integer
Pos = InStr(1, S, vbNullChar)
If Pos > 0 Then
TrimToNull = Left(S, Pos - 1)
Else
TrimToNull = S
End If

End Function