VBA File Operations
Automate file handling: open, close, read, write, import, and export files with VBA
Quick Reference
Open: Workbooks.Open("path")
Save: ActiveWorkbook.Save / SaveAs
Close: Workbooks("name").Close
Read Text: Open file For Input
Write Text: Open file For Output
Opening Excel Workbooks
Basic Open:
Sub OpenWorkbook()
Workbooks.Open "C:\Data\Sales.xlsx"
End Sub
Open with Options:
Sub OpenWithOptions()
Workbooks.Open _
Filename:="C:\Data\Report.xlsx", _
ReadOnly:=True, _
Password:="secret123", _
UpdateLinks:=0 ' Don't update links
End Sub
Open and Assign to Variable:
Sub OpenToVariable()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Data\Sales.xlsx")
' Work with workbook
wb.Worksheets("Sheet1").Range("A1").Value = "Updated"
wb.Close SaveChanges:=True
End Sub
Check if File Exists Before Opening:
Sub SafeOpen()
Dim filePath As String
filePath = "C:\Data\Report.xlsx"
If Dir(filePath) <> "" Then
Workbooks.Open filePath
MsgBox "File opened successfully"
Else
MsgBox "File not found: " & filePath
End If
End Sub
Saving and Closing Workbooks
Save Existing File:
ActiveWorkbook.Save ' Save current workbook
Workbooks("Sales.xlsx").Save ' Save specific workbook
Save As (New Location/Name):
Sub SaveAsExample()
ActiveWorkbook.SaveAs _
Filename:="C:\Reports\Monthly.xlsx", _
FileFormat:=xlOpenXMLWorkbook ' .xlsx
End Sub
Common File Formats:
xlOpenXMLWorkbook- .xlsx (Excel 2007+)xlOpenXMLWorkbookMacroEnabled- .xlsm (with macros)xlExcel8- .xls (Excel 97-2003)xlCSV- .csv (comma delimited)xlTextWindows- .txt (tab delimited)
Close Workbook:
' Close with prompt to save
ActiveWorkbook.Close
' Close and save
Workbooks("Sales.xlsx").Close SaveChanges:=True
' Close without saving
Workbooks("Temp.xlsx").Close SaveChanges:=False
Save Copy and Close:
Sub SaveCopyAndClose()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Data\Original.xlsx")
' Make changes
wb.Worksheets(1).Range("A1").Value = "Modified"
' Save as new file
wb.SaveAs "C:\Data\Modified.xlsx"
wb.Close SaveChanges:=False
End Sub
Reading Text Files
Read Entire File:
Sub ReadTextFile()
Dim fileNum As Integer
Dim fileContent As String
Dim filePath As String
filePath = "C:\Data\data.txt"
fileNum = FreeFile
Open filePath For Input As #fileNum
fileContent = Input$(LOF(fileNum), fileNum)
Close #fileNum
MsgBox fileContent
End Sub
Read Line by Line:
Sub ReadLineByLine()
Dim fileNum As Integer
Dim lineText As String
Dim rowNum As Long
fileNum = FreeFile
rowNum = 1
Open "C:\Data\data.txt" For Input As #fileNum
Do While Not EOF(fileNum)
Line Input #fileNum, lineText
Cells(rowNum, 1).Value = lineText
rowNum = rowNum + 1
Loop
Close #fileNum
End Sub
Import CSV File:
Sub ImportCSV()
Dim fileNum As Integer
Dim lineText As String
Dim dataArray() As String
Dim row As Long, col As Long
fileNum = FreeFile
row = 1
Open "C:\Data\data.csv" For Input As #fileNum
Do While Not EOF(fileNum)
Line Input #fileNum, lineText
dataArray = Split(lineText, ",")
For col = 0 To UBound(dataArray)
Cells(row, col + 1).Value = dataArray(col)
Next col
row = row + 1
Loop
Close #fileNum
End Sub
Writing Text Files
Write String to File:
Sub WriteTextFile()
Dim fileNum As Integer
fileNum = FreeFile
Open "C:\Output\report.txt" For Output As #fileNum
Print #fileNum, "Report generated on " & Now
Print #fileNum, "Total sales: $50,000"
Close #fileNum
End Sub
Append to Existing File:
Sub AppendToFile()
Dim fileNum As Integer
fileNum = FreeFile
' Append mode adds to end
Open "C:\Logs\activity.log" For Append As #fileNum
Print #fileNum, Now & " - User logged in"
Close #fileNum
End Sub
Export Range to CSV:
Sub ExportToCSV()
Dim fileNum As Integer
Dim row As Long, col As Long
Dim lineText As String
fileNum = FreeFile
Open "C:\Export\data.csv" For Output As #fileNum
' Export range A1:C10
For row = 1 To 10
lineText = ""
For col = 1 To 3
lineText = lineText & Cells(row, col).Value
If col < 3 Then lineText = lineText & ","
Next col
Print #fileNum, lineText
Next row
Close #fileNum
End Sub
File System Operations
Check if File Exists:
Function FileExists(filePath As String) As Boolean
FileExists = (Dir(filePath) <> "")
End Function
Get File Name from Path:
Function GetFileName(fullPath As String) As String
Dim parts() As String
parts = Split(fullPath, "\")
GetFileName = parts(UBound(parts))
End Function
Copy File:
Sub CopyFile()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile _
Source:="C:\Data\original.xlsx", _
Destination:="C:\Backup\original.xlsx"
Set fso = Nothing
End Sub
Delete File:
Sub DeleteFile()
Dim filePath As String
filePath = "C:\Temp\oldfile.xlsx"
If Dir(filePath) <> "" Then
Kill filePath ' Delete file
MsgBox "File deleted"
End If
End Sub
Warning: Kill permanently deletes files. No recycle bin!
Get File Properties:
Sub GetFileInfo()
Dim fso As Object, file As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.GetFile("C:\Data\report.xlsx")
MsgBox "Size: " & file.Size & " bytes" & vbCrLf & _
"Created: " & file.DateCreated & vbCrLf & _
"Modified: " & file.DateLastModified
End Sub
List All Files in Folder:
Sub ListFiles()
Dim folderPath As String
Dim fileName As String
Dim row As Long
folderPath = "C:\Data\"
fileName = Dir(folderPath & "*.xlsx")
row = 1
Do While fileName <> ""
Cells(row, 1).Value = fileName
row = row + 1
fileName = Dir ' Get next file
Loop
End Sub
File Dialog (User File Selection)
Open File Dialog:
Sub OpenFileDialog()
Dim filePath As Variant
filePath = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xlsx), *.xlsx", _
Title:="Select a file to open")
If filePath <> False Then
Workbooks.Open filePath
MsgBox "Opened: " & filePath
Else
MsgBox "No file selected"
End If
End Sub
Save As Dialog:
Sub SaveAsDialog()
Dim filePath As Variant
filePath = Application.GetSaveAsFilename( _
InitialFileName:="Report.xlsx", _
FileFilter:="Excel Files (*.xlsx), *.xlsx")
If filePath <> False Then
ActiveWorkbook.SaveAs filePath
End If
End Sub
Folder Picker Dialog:
Sub SelectFolder()
Dim folderPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder"
If .Show = -1 Then
folderPath = .SelectedItems(1)
MsgBox "Selected: " & folderPath
End If
End With
End Sub
Best Practices
✓
Always use FreeFile: Get next available file number to avoid conflicts
✓
Close files in error handlers: Ensure files are closed even on errors
✓
Check file existence: Before opening or deleting
✓
Use error handling: File operations fail frequently (permissions, missing files)
✓
Avoid hardcoded paths: Use Application.GetOpenFilename or variables
✓
Close workbooks after use: Free memory and avoid conflicts
✓
Use FileSystemObject: For advanced file operations (copy, move, properties)