Tag Archives: Files

VBA – Get Short Path

I was trying to help out in a forum discussion in which a user was needing to retrieve the old DOS short path format of a path.  As with all things VBA, there are multiple ways to do this and one of the more common approaches is to use the GetShortPathName API.  Now there is nothing wrong with the use of APIs, but at the same time, they can unnecessarily complicate things, as you need to worry about bitness, code can become complex… so normally, if you truly don’t need them, you are best to use alternative solution.

Anyways, I thought I’d show a simpler solution using FSO (File System Object).  Below was my answer to the question

Continue reading →

VBA – File Exist

Commonly, to check for the existence of a file we advise people to use the Dir function or FileSystemObject. The problem that can be encountered with these approaches is that, for network drive & mapped drives, should they be disconnected, can result in long delay because you have to wait to the function to timeout.

I set out to try and resolve this issue and this is what I came up with. As it turns out, it was slightly more involved than I originally thought and as such, I broke certain elements into independent function should they prove useful for other application.

Continue reading →

MS Access – VBA – Copy a File

Have you ever needed to make a copy of a file? Well, there are a number of ways that you can do it.

Built-in VBA FileCopy

I find that using the built-in FileCopy function to be the simplest and cleaness method. Below is a simple procedure around it that trap certain common errors.

'---------------------------------------------------------------------------------------
' Procedure : CopyFile
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Copy a file
'             Overwrites existing copy without prompting
'             Cannot copy locked files (currently in use)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSource - Path/Name of the file to be copied
' sDest - Path/Name for copying the file to
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' 1         2007-Apr-01             Initial Release
'---------------------------------------------------------------------------------------
Public Function CopyFile(sSource As String, sDest As String) As Boolean
On Error GoTo CopyFile_Error

    FileCopy sSource, sDest
    CopyFile = True
    Exit Function

CopyFile_Error:
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & sSource & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    End If
    Exit Function
End Function

All you have to do is copy it into a module and then call it as required. Enjoy!

Copying a File Using FSO

As an alternative, here is an example to copy a file using FSO.

'---------------------------------------------------------------------------------------
' Procedure : FSO_FileCopy
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Copy a file
'               Overwrites existing copy without prompting (you can change the varible
'               in the CopyFile method call)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
' Ref       : FSO - https://msdn.microsoft.com/en-us/library/ms127964(v=vs.110).aspx
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSource - Path/Name of the file to be copied
' sDest - Path/Name for copying the file to
'
' Usage:
' ~~~~~~
' FSO_FileCopy("C:\TE.MP\Tab.le1.txt", "C:\TE.MP\Tab.le3.txt")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-06-16              Initial Release - Blog Help
'---------------------------------------------------------------------------------------
Public Function FSO_FileCopy(ByVal sSource As String, _
                             ByVal sDest As String) As Boolean
    On Error GoTo Error_Handler
    Dim oFSO                  As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Call oFSO.CopyFile(sSource, sDest, True)
    FSO_FileCopy = True

Error_Handler_Exit:
    On Error Resume Next
    If Not oFSO Is Nothing Then Set oFSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_FileCopy" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function