These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Basic file and folder examples

How to get the active station and folder name:

MsgBox "Active station and folder name: " & CurDir

How to change the active station:

ChDrive "F" ' changes to the F-station

How to change the active folder:

ChDir "F:\My Documents\Private"

How to determine if a file exists in a folder:

If Len(Dir("F:\My Documents\My Workbook.xls")) > 0 Then 
' the file exists, returns "" (an empty string) if the file doesn't exist.

How to determine if a folder exists:

If Len(Dir("F:\My Documents\MyFolder", vbDirectory)) > 0 Then 
' the folder exists, returns "" (an empty string) if the file doesn't exist.

How to delete a file :

Kill "F:\My Documents\My Workbook.xls"

If you don't specify the station, Excel uses the active station. If you don't specify the folder, Excel uses the active folder.

How to create a new folder:

MkDir "NewPrivateFolder" ' creates a new folder in the active folder
MkDir "F:\My Documents\NewPrivateFolder"
' creates a new folder in the existing folder F:\My Documents

How to delete a folder (folder must be empty):

RmDir "NewPrivateFolder" 
' deletes the subfolder NewPrivateFolder in the active folder
RmDir "F:\My Documents\NewPrivateFolder" 
' deletes the subfolder NewPrivateFolder in the folder F:\My Documents

How to copy a file (the file must be closed):

FileCopy "OrgWorkBook.xls", "CopyWorkBook.xls" 
' copies OrgWorkBook.xls to CopyWorkBook.xls in the active folder
FileCopy "OrgWorkBook.xls", "F:\My Documents\CopyWorkBook.xls" 
' copies OrgWorkBook.xls from the active folder to F:\My Documents\CopyWorkBook.xls

How to move a file (the file must be closed):

OldFilePath = "C:\OldFolder\Filename.xls" ' original file location
NewFilePath = "C:\NewFolder\Filename.xls" ' new file location
Name OldFilePath As NewFilePath ' move the file

How to copy a folder, including subfolders:

CopyFolderAndSubFolders "C:\FolderName\SourceFolder", "C:\FolderName\TargetFolder"

Sub CopyFolderAndSubFolders(strSourceFolder As String, strTargetFolder As String)
' example: CopyFolderAndSubFolders "C:\FolderName\SourceFolder", "C:\FolderName\TargetFolder"
' requires a reference to the Microsoft Scripting runtime library
Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Application.StatusBar = "Copying " & strSourceFolder & " to " & strTargetFolder & "..."
    On Error GoTo NotAbleToCopy
    fso.CopyFolder strSourceFolder, strTargetFolder, True
    On Error GoTo 0
    Application.StatusBar = False
    Set fso = Nothing
    Exit Sub
    
NotAbleToCopy:
    MsgBox "Error Description: " & vbLf & Err.Description, vbExclamation, "An Error Occurred"
    Resume Next
End Sub

Document last updated 2005-12-28 12:54:44