To open an Excel workbook for reading in VBA, use Workbooks.Open with ReadOnly:=True, then refer to the workbook and worksheet through variables. A typical workflow is to select a file, open it read-only, read the cells you need, and close it without saving. The steps below are for desktop Excel for Microsoft 365 on Windows; VBA macros cannot run in Excel for the web.
What You’ll Need
- Desktop Excel for Microsoft 365 with macros enabled for the workbook that contains your VBA code.
- A source Excel file you are permitted to access. Its full file path is useful if you want to open a known file without a selection dialog.
- The worksheet name and the cells or range you want to read. The example below expects a worksheet named
Data, with headers in row 1 and data in columns A and B. - A backup copy of the workbook containing your macro before you run or change VBA. Macro actions cannot be undone with Ctrl+Z.
Step-by-Step
- Open the Visual Basic Editor. In Excel for Windows, press Alt+F11, or choose Developer > Visual Basic. If the Developer tab is hidden, choose File > Options > Customize Ribbon, select Developer, and click OK. On a Mac, use Developer > Visual Basic if available, or open the Visual Basic Editor from the Tools > Macro menu; the keyboard shortcut can vary by keyboard, commonly Option+F11 or Fn+Option+F11.
- Choose where the code will live. In the editor, select the workbook that will hold your macro in Project Explorer. If that pane is hidden, press Ctrl+R on Windows or use View > Project Explorer. Choose Insert > Module to create a standard module, then paste the procedure there. Save the macro-containing workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it is not already saved in a macro-capable format. - Use a file picker and open the selected workbook read-only. The example below lets you select an Excel file, opens it without updating external links, reads values from columns A and B, prints the first data row to the Immediate window, and closes the source file without saving. Before running any macro, save a separate copy of the workbook that contains the code; VBA changes cannot be reversed with Ctrl+Z.
Option Explicit
Sub ReadWorkbook()
Dim selectedFile As Variant
Dim sourceWb As Workbook
Dim sourceWs As Worksheet
Dim lastRow As Long
Dim data As Variant
Dim oldScreenUpdating As Boolean
Dim errText As String
On Error GoTo ErrHandler
oldScreenUpdating = Application.ScreenUpdating
' Let the user choose an Excel workbook.
selectedFile = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xlsx;*.xlsm;*.xls;*.xlsb),*.xlsx;*.xlsm;*.xls;*.xlsb", _
Title:="Select workbook to read")
' GetOpenFilename returns False if the user cancels.
If VarType(selectedFile) = vbBoolean Then Exit Sub
Application.ScreenUpdating = False
' Open read-only and do not refresh external workbook links.
Set sourceWb = Workbooks.Open( _
Filename:=CStr(selectedFile), UpdateLinks:=0, ReadOnly:=True)
Set sourceWs = sourceWb.Worksheets("Data")
' Find the last used row in column A; row 1 is the header.
lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
' Read the range into a two-dimensional array in memory.
data = sourceWs.Range("A2:B" & lastRow).Value2
Debug.Print data(1, 1), data(1, 2)
End If
' Close the source without saving anything to it.
sourceWb.Close SaveChanges:=False
Set sourceWs = Nothing
Set sourceWb = Nothing
Application.ScreenUpdating = oldScreenUpdating
Exit Sub
ErrHandler:
errText = Err.Description
On Error Resume Next
If Not sourceWb Is Nothing Then sourceWb.Close SaveChanges:=False
Application.ScreenUpdating = oldScreenUpdating
MsgBox "Could not read the workbook: " & errText, vbExclamation
End Sub
- Adjust the worksheet and range for your file. Replace
Datawith the exact worksheet tab name, including spaces and punctuation. For example, a tab namedMonthly Salesmust be written asWorksheets("Monthly Sales"). Change columns A:B if the fields you need are elsewhere. The sample finds the final row by looking up column A, so column A should be filled on every data row. If another column is more reliable, use that column in theCellsexpression. - Decide how you want to use the values. In the sample,
datais a two-dimensional array:data(1, 1)is the value from A2, anddata(1, 2)is the value from B2. The array contains the range’s calculated values, not formula text. Replace theDebug.Printline with your own processing, such as copying values into a report or checking them before use. To inspect output in the editor, show the Immediate window with Ctrl+G on Windows. - Run the procedure and check the result. Click inside
ReadWorkbookand press F5, or return to Excel and use Developer > Macros, selectReadWorkbook, and click Run. Choose the source file when prompted. If the data is found, the first row’s two values appear in the Immediate window. The source workbook should close when the procedure finishes. Test with a non-sensitive copy first, especially if you add code that changes other workbooks.
If you already know the path and do not need a picker, the essential opening call can instead use a full path. Declare the workbook variable in the procedure where you use it, and confirm that the path and file name are correct. For example, a Windows path might look like C:\Reports\Sales.xlsx. A Mac file path uses a different format, so select the file with the picker or use the path format appropriate to macOS.
Dim sourceWb As Workbook
' Open the known file without allowing edits to be saved to the source.
Set sourceWb = Workbooks.Open( _
Filename:="C:\Reports\Sales.xlsx", ReadOnly:=True, UpdateLinks:=0)
Common Mistakes
- Leaving out
ReadOnly:=True. Without it, Excel normally opens the file in an editable state when access permits. Include the argument when your intention is to avoid saving changes to the original file. Read-only does not prevent VBA from changing values in memory; it prevents ordinary saving back to that opened source. - Using the wrong workbook or sheet reference. Once another file is open, unqualified references such as
Range("A1")can refer to the active sheet in the wrong workbook. Qualify references throughsourceWbandsourceWs, as the example does. - Assuming the first row is data. The sample starts at row 2 because it treats row 1 as a header. If your file has no header, change the starting row and check that the last-row test still includes the data you want.
- Relying on a blank-prone column to find the last row.
End(xlUp)finds the last non-empty cell in the chosen column, not necessarily the last row with content anywhere in the sheet. Choose a consistently populated column or use a more suitable method for the layout. - Forgetting to close the source workbook. A workbook left open can cause confusion or file-locking issues. Close it with
SaveChanges:=Falsewhen the macro is finished reading. Do not use that instruction if your workflow intentionally makes changes you need to keep. - Expecting the macro to run in Excel for the web. Excel for the web can open and edit many workbooks, but it does not run VBA. Use desktop Excel for the macro, whether the workbook is stored locally or in a supported cloud location.
Tips
- Use a workbook variable from the start. Assign the result of
Workbooks.Opento aWorkbookvariable. It makes it clear which file your code is reading and helps prevent accidental references to the macro workbook or another active workbook. - Read a block into an array for larger ranges. Reading a range into a Variant array, as shown, is often more efficient than asking Excel for each cell one at a time. Keep the range limited to the rows and columns you actually need.
- Keep external-link behavior deliberate.
UpdateLinks:=0tells Excel not to update external workbook links while opening. That avoids link-update prompts and means you are reading the values already stored in the file, which may be out of date. Remove or change this argument only if you specifically need a different link-update behavior. - Handle cancellation and cleanup. The file picker returns Boolean
Falsewhen the user cancels. The sample exits in that case and has an error handler that attempts to close the source workbook and restore screen updating if something goes wrong. - Be cautious with macro-enabled source files. Opening an
.xlsmfile is not the same as trusting it. Only work with files from sources you trust, and follow your organization’s macro-security settings. Do not lower Excel’s security protections just to make an unfamiliar file open. - Use the Immediate window for quick checks, not as permanent output.
Debug.Printis convenient while developing, but its output is temporary. For a finished tool, write the values to an explicitly named destination sheet or return them to the calling procedure.
Troubleshooting
The file picker does not show the file. Check that the file is saved with an Excel extension included in the filter. The sample includes .xlsx, .xlsm, .xls, and .xlsb. If you alter the filter, keep its description and extension pattern in the required comma-separated format. You can also navigate to the folder containing the file from the dialog.
You see “Subscript out of range.” The named worksheet probably does not exist in the selected workbook. Compare the tab name carefully with Worksheets("Data"), including spaces. You can inspect sheet names in the workbook or change the code to the actual name. If the sheet is optional, add a check before trying to read its cells.
The macro says it cannot open the file. Confirm the file still exists, that you have permission to read it, and that it is not damaged or blocked by a password prompt. Close another Excel session that may be holding an exclusive lock. If using a hard-coded path, check every folder name and the file extension. For a password-protected workbook, supply credentials securely through an appropriate workflow; avoid putting a real password directly into code that other people can access.
No values appear in the Immediate window. The sample prints only when column A has a value at or below row 2. Check that the selected workbook has data in the expected sheet and columns, and confirm the macro completed without an error. If you want to inspect a different cell, change the range or the array index. Remember that formulas are read as their calculated results when using Value2.
Excel reports a read-only or permission message. A file can already be read-only, opened by someone else, or stored in a location where you lack write access. Since this procedure is intended to read, that may be acceptable. Check that the opened workbook is the expected one and that the code closes it with SaveChanges:=False. If you need to save changes, use a separate output workbook or obtain the required access rather than trying to overwrite the source.
The macro will not run. Verify that the code is in a standard module and that the macro-containing workbook is saved as .xlsm or another format that supports VBA. In desktop Excel, review the Trust Center and your organization’s macro policy; do not enable all macros indiscriminately. If you are using Excel for the web, open the file in desktop Excel to run VBA.
Frequently Asked Questions
Does ReadOnly:=True stop VBA from changing cells?
It opens the workbook as read-only so changes cannot normally be saved back to that source file. Code can still alter values in the open workbook’s memory, so close it with SaveChanges:=False when you only intend to read.
Can I open a workbook without displaying it?
Yes. You can set the workbook’s Visible property to False after opening it, but do that only if your code can still identify and close the correct workbook. Hiding a workbook does not make it read-only or change what data it contains.
Can I read a workbook that is already open?
Yes. If you already have a reference to it, use that workbook object rather than opening another copy. For example, a workbook open in the same Excel instance can be referenced through the Workbooks collection, but use its exact name and confirm you have identified the source rather than the macro workbook.
Does this read formulas or their displayed results?
The example uses Value2, which returns the cells’ calculated values rather than the formula strings. To inspect formulas, read the range’s Formula property instead. A cell’s displayed formatting, such as a date format, is not the same thing as its stored value.
Will the file picker code work on Mac?Application.GetOpenFilename is available in desktop Excel for Mac as well, though dialog appearance, file paths, and keyboard shortcuts differ from Windows. Test the selection and path behavior in your Mac version. VBA does not run in Excel for the web.
What if the workbook is password-protected?
Excel may ask for the password when the file opens. VBA also supports password arguments for opening protected files, but hard-coding a real password can expose it to anyone who can view the code. Prefer a secure, organization-approved way to request and handle credentials.
Related
- How to Indent in Microsoft Excel: Cells, Text, and Lists
- How to Indent Text in an Excel Text Box Step by Step
- How to Indent in the Same Excel Cell
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Intersect: Find Overlapping Ranges in a SheetIn Excel VBA, use Application.Intersect to find the cells shared by two or more ranges. Assign its result…
- Excel VBA Input Box Dropdown List: Options and SetupExcel’s built-in VBA InputBox does not support a dropdown list. To let someone choose from a visible list,…
- Excel VBA Bubble Sort for Arrays: Code and ExplanationTo bubble-sort a one-dimensional VBA array, compare neighboring items and swap them when they are out of order;…
- Excel VBA: Sum Cells by Fill ColorIf by “add cells” you mean total their numbers, a VBA macro can sum values in a range…
- Excel VBA AutoFilter Sort: Filter and Sort Data RowsTo filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s…
- Sudoku Solver in Excel: Setup, Methods, and VBA CodeYes—Excel can solve Sudoku. For a practical solver, enter the puzzle in cells B2:J10 and run a short…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.