VBA File I/O to Python open() and pathlib (With Examples)

VBA File I/O Example

VBA

Sub ProcessSalesData()
    ' Read sales data from CSV, calculate totals, and write summary
    Dim fileNum As Integer
    Dim line As String
    Dim parts As Variant
    Dim totalSales As Double
    Dim recordCount As Integer
    
    fileNum = FreeFile
    totalSales = 0
    recordCount = 0
    
    ' Read input file
    Open "C:\Data\sales.csv" For Input As #fileNum
    Do While Not EOF(fileNum)
        Line Input #fileNum, line
        If Len(line) > 0 Then
            parts = Split(line, ",")
            totalSales = totalSales + CDbl(parts(2)) ' Sales amount column
            recordCount = recordCount + 1
        End If
    Loop
    Close #fileNum
    
    ' Write summary report
    fileNum = FreeFile
    Open "C:\Reports\summary.txt" For Output As #fileNum
    Print #fileNum, "Sales Summary Report"
    Print #fileNum, "Records Processed: " & recordCount
    Print #fileNum, "Total Sales: $" & Format(totalSales, "#,##0.00")
    Close #fileNum
End Sub

Python open()/pathlib Equivalent

Python

from pathlib import Path
from typing import Tuple

def process_sales_data() -> None:
    """Read sales data from CSV, calculate totals, and write summary."""
    total_sales = 0.0
    record_count = 0
    
    # Read input file
    input_file = Path("C:/Data/sales.csv")
    with open(input_file, "r", encoding="utf-8") as f:
        for line in f:
            line = line.strip()
            if line:
                parts = line.split(",")
                total_sales += float(parts[2])  # Sales amount column
                record_count += 1
    
    # Write summary report
    output_file = Path("C:/Reports/summary.txt")
    output_file.parent.mkdir(parents=True, exist_ok=True)
    
    with open(output_file, "w", encoding="utf-8") as f:
        f.write("Sales Summary Report\n")
        f.write(f"Records Processed: {record_count}\n")
        f.write(f"Total Sales: ${total_sales:,.2f}\n")

Common VBA → Python Mappings

VBA File I/O	Python open()/pathlib
Open "file.txt" For Input As #1	open("file.txt", "r")
Open "file.txt" For Output As #1	open("file.txt", "w")
Print #1, data	f.write(str(data) + "\n")
Line Input #1, line	line = f.readline().strip()
EOF(1)	for line in f (exhausted)
Close #1	with open() context manager

Side-by-Side Comparison

VBA

' Check if file exists and get file size
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("data.txt") Then
    Dim fileSize As Long
    fileSize = fso.GetFile("data.txt").Size
    MsgBox "File size: " & fileSize & " bytes"
End If

Python

# Check if file exists and get file size
from pathlib import Path

file_path = Path("data.txt")
if file_path.exists():
    file_size = file_path.stat().st_size
    print(f"File size: {file_size} bytes")

Important Differences

File Handle Management: VBA uses numbered file handles (#1, #2) that must be manually closed. Python uses file objects with context managers that automatically handle cleanup.
Line Termination: VBA Print statement automatically adds line breaks. Python write() method requires explicit newline characters or use writelines() for multiple lines.
File Mode Syntax: VBA uses verbose keywords like 'For Input', 'For Output'. Python uses concise mode strings like 'r', 'w', 'a', 'rb'.
Path Handling: VBA works directly with string paths. Python's pathlib provides object-oriented path manipulation with better cross-platform compatibility.

Have VBA macros that read or write files?

Convert your file-handling code to Python's modern I/O patterns automatically.

Convert My VBA Code