How to Work with Files in Excel Visual Basic

You can use Excel Visual Basic to read and write text files (Sequential and Random file access).   But we will not touch on text files in this post.  Rather, we will look at how to save and open Excel files using Excel code, plus a few valuable functions:

The bigger examples below are in the file Macros.xlsm, which you can get free of charge from us.  Or you can copy the code into a blank worksheet and then run it.

1.        Save and Backup Excel Files

The following code (in Macros.xlsm Module PrintSave2) saves a file with its existing name:

Sub SaveFile()  ' Save File in current format: {XLCALL SaveFile}
  ActiveWorkbook.Save
End Sub

The following code saves a file with a new name supplied by the calling routine (NewName$). Then it creates a backup.  The code is in Macros.xlsm Module FileCombine:

Sub SaveAsBackup(NewName$)  ' File SaveAs with Backup
  ' Save Excel File under New Name, to same path as current file.
  ' Macro 05/04/2000 by Rick Raubenheimer
  Dim Path1$
  Path1$ = ActiveWorkbook.Path
  If Right$(Path1$, 1) <> "\" Then Path1$ = Path1$ & "\"
  ActiveWorkbook.SaveAs FileName:=Path1$ & NewName$, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=True
End Sub

2.        Open, Swap, and Close Excel Files

The following code is in Macros.xlsm Module FileCombine.  It works as follows:  Excel does not have File Combine Add Numbers like Lotus 1-2-3 has.  In Excel, one has to open both files and then copy and paste (or Paste Special) from one to the other.  This is not as versatile, and needs both files to be open.  This code demonstrates how to:

1. Work with file paths,
2. Open a file in code,
3. Swap from one open file to another,
4. Display the Status Bar and Application Caption,
5. Change values in a cell, and
6. Close a file.

Sub FileCombineAddRange() ' = /FCAN in 123.  Load ImportRange, ImportFile first!
 
  Dim cel As Range
  Dim ImportRange$, ImportFile$, SorcVal$
  Dim TargetFile$, TargVal$
  Dim TarRo%, TarCo%, OffsetRo%, OffsetCo%
  Dim Msg$
 
  ' These ranges must be created and set ahead of time:
  ImportRange$ = Range("ImportRange").Value
  ImportFile$ = Range("ImportFile").Value
 
  ' If Path is not given, use current file's path:
  If InStr(ImportFile$, "\") = 0 Then ImportFile$ = ActiveWorkbook.Path & "\" & ImportFile$
  If InStr(ImportFile$, ".xls") = 0 Then ImportFile$ = _
    ImportFile$ & ".xls"
 
  ' Display Status Bar and Application Caption:
  Application.DisplayStatusBar = True
  Application.Caption = "Opening " & ImportFile$ & " for Add..."
 
  ' Determine starting Target cell (Active cell):
  TargetFile$ = ActiveWorkbook.Name
  TarRo% = ActiveCell.Row
  TarCo% = ActiveCell.Column
 
  ' Open Source File:
  Workbooks.Open FileName:=ImportFile$
  Application.GoTo Reference:=ImportRange$
  Msg$ = "Adding " & ImportRange$ & " ("
  Msg$ = Msg$ & Replace(ActiveWindow.RangeSelection.Address, "$", "")
  Msg$ = Msg$ & ") from " & ImportFile$ & "  "
  Application.Caption = Msg$
  OffsetRo% = TarRo% - ActiveCell.Row
  OffsetCo% = TarCo% - ActiveCell.Column
 
  ' Add from each numeric cell in ImportRange$:
  For Each cel In ActiveWindow.RangeSelection
    SorcVal$ = cel.Value    ' Get answer to formula in source
    If IsNumeric(SorcVal$) Then  ' Source Value numeric
      ' Determine corresponding destination cell:
      TarRo% = OffsetRo% + cel.Row
      TarCo% = OffsetCo% + cel.Column
      TargVal$ = Workbooks(TargetFile$).ActiveSheet.Cells( _
        TarRo%,  TarCo%).Formula
      ' If destination is numeric (or blank), add answer:
      If IsNumeric(TargVal$) Or TargVal$ = "" Then
        Application.StatusBar = cel.Address & " - Adding"
        Workbooks(TargetFile$).ActiveSheet.Cells(TarRo%, TarCo%).Formula = Val(TargVal$) + Val(SorcVal$)
      Else
        Application.StatusBar = cel.Address & " - Ignoring"
'       cel.Activate      ' Show some activity
      End If
    Else
      Application.StatusBar = cel.Address & " - Blank!"
    End If
  Next cel      ' Next Cell
  ActiveWindow.Close False  ' Close Source File, no changes
  Application.StatusBar = False
  Application.Caption = Empty
  DoEvents
End Sub

Here are some other useful File Functions:

3.        File Length

Use the FileLen function for the size in bytes (the file need not be open):

FileLen(pathname)

4.        FileCopy

This is used to copy files, like the DOS command COPY:

Syntax

FileCopy source, destination

Example:

FileCopy "C:\AUTOEXEC.BAT",  "C:\AUTOEXEC.BAK"

5.        Name

This renames a file, like REN or RENAME in DOS:

Syntax

Name OldName AsNewName

Example:

Name "C:\AUTOEXEC.BAK" As "C:\BACKUPS\AUTOEXEC.BAC"

6.        Kill

Deletes a file, like DOS DEL:

Syntax

Kill Name

Example:

Kill "C:\BACKUPS\AUTOEXEC.BAC"

Use this with caution, obviously!

7.        Dir: Directory Listing

This is not the same as the DOS DIR command, which lists all the files that match.  Instead, it returns one file name at a time.  When run with arguments, it finds the first file on the given path.  Later runs with no arguments return successive files from the same path.  It returns a zero-length string at the end of the list.

With an attribute of vbVolume, Dir will return the volume label of a drive.

The Syntax is:

Dir(pathname[, attributes])          to find the first file
Dir                repeated to find subsequent files

Example

' First file with wildcards:
File1$ = Dir("C:\Examples\*.TXT")
While File1$ > ""
  ' Do something with the file:
…
  ' Next file with same wildcards:
  File1$ = Dir()
Wend

The Dir function supports the wildcards “?” (any one character) and “*” (any number of any characters) in the same way that DOS does.  The files are not returned in any sorted order.

8.        Find and Change Drive and Directory

All the above functions work on the current drive and directory (path) unless otherwise specified.

To find the current path, use:

CurDir             to find the current drive and directory
CurDir("C")        to find the current directory on drive C.

To change the current directory, use:

ChDir path
e.g. ChDir "C:\DOS"

The string “path” may include the drive.  If no drive is specified, ChDir changes the default directory on the current drive.  ChDir changes the default directory but not the default drive.

To change the default drive, use:

ChDrive drive
e.g. ChDrive  "C"