VBA or Visual Basic for Applications is closely related to Visual Basic. This application is useful in adding functionality to the Microsoft Office suite. VBA is simple to use and automates routine tasks especially in Microsoft Excel. Programmers widely use it to create efficient and elegant macros. MS Excel has a built-in Visual Basic Editor where you can write and test your VBA code. Excel VBA can open existing worksheets and run macros on them. File is an important part of any computer system. It could vary from being a simple text file to an executable file. Most programming languages have in-built functions to process and manipulate files. Today, in this intermediate level tutorial we introduce you to VBA file open function. We assume that you are familiar with MS Excel, VBA and concepts of file manipulation. If you want to first learn the basics, we highly recommend this introductory course on Excel VBA.
What is a File?
A computer file is a specific piece of data. It has a name that identifies the file. The file has an extension name which tells the operating system and associated programs what type of file it is. Note that every file has a size including the size zero. Every file has a location in the computer system and is indexed. A computer file may be created or deleted, moved or copied, or changed. Every file contains attributes which determine its use. These attributes include permissions to see, open, read, alter the file and other permissions.
Types of Files
The operating system recognizes files as regular, directory or special. There exist many variations of these basic types of files.
- Regular files – These are the most common files and are used to store data. They come in two types namely text or binary files. Text files contain information stored in ASCII format which are readable by the user. Binary files contain information readable by the computer.
- Directory files – These files contain information that the system requires to access all types of files.
- Special files – Special files are temporary files created by various processes. They come in three types, namely, FIFO, block and character. The last two file types define devices.
Note that every file comes with a set of file privileges that determine who can read, alter or execute the file.
What is VBA Open File
The Open statement enables the user to open a file. The syntax for this statement is given as:
Open pathname For mode [Access access] [lock]
Lets take a close look at the open file parameters
- Pathname -This field is required. It specifies a file name that includes its directory and drive.
- Mode – This field is also required. The mode can be append, Binary, output, input or random. The default mode is random.
- Access – This parameter is optional. It specifies operations that are permitted on an open file. It can be read, write or read write.
- Lock – It’s an optional parameter. It specifies the operations such as shared, lock read, lock write and lock read-write which are restricted on an open file.
Note that, we have used dummy file locations in all the examples given here. We advise you to give the actual file location on your computer.
Example 1: Program to Open an Existing File in MS Excel
Sub openworksheet() Workbooks.Open Filename:= _ "C:\Desktop/Emp_details.xlsx" End Sub
This macro is written in Visual Basic Editor. In order to open a file, the location address of the file is given. When you write your code, give the full file location of your Excel file. For instance, if your file is store in My Documents, give file address as “C:\MyDocuments/Emp_details.xlsx”
To learn more on VBA file functions, we suggest that you go take this course on VBA macros.
Example 2: Program to Open and Manipulate a CSV File
Note that the CSV file has three fields and that the fields are separated by commas.
Sub OpenTextFile() Dim File_Path As String File_path=" C:\Users\owner\John\employee.csv" Open File_Path for Input as #1 row_num= 0 Do until EOF(1) Line Input #1, Line_FromFile Line_Items = Split(Line_FromFile, ",") ActiveCell.Offset(row_num, 0).Value = Line_Items(2) ActiveCell.Offset(row_num, 1).Value = Line_Items(1) ActiveCell.Offset(row_num, 2).Value = Line_Items(0) row_num = row_num + 1 Loop Close #1 End Sub
In this program, the mode is input which is used to read a file. The FileNumber is 1. In “Line Input #1, Line_FromFile”, the first 3 items before the comma refer to a single line of input from your file. After the comma you tell VBA to place this line in the variable Line_FromFile. In each loop iteration a new line will be grabbed from the text file and placed in this variable. Since the line has commas in it, you need to parse it. By using Split function, you can place each item from a line into an array called Line_Items. The text file has three items per line. So the array goes from 0 to 2 positions.
Then we place each item into a cell on the spreadsheet. In each loop iteration, increment the row_num variable. The loop terminates when the end of file is reached. Finally, we close the file. If you miss closing a file, the file handle and the memory space allocated to the file remains locked, in a zombie state. This means that the memory cannot be reused later on or recycled. So it’s important you remember to close all unused files to avoid running out of memory.
Example 3: Program to Open an Excel Worksheet in Access
Sub openWorkbook() Dim ABC_App As Excel.Application Dim XYZ_Book As Excel.Workbook Set XYZ_Book = Workbooks.Open("C:\employee_details.xlsx") Set ABC_App = XYZ_Book.Parent ABC_App.Visible = True End Sub
In this program, the variables ABC_App and XYZ_Book are declared. They are used to access the Excel Objects Excel.Application and Excel.Workbook respectively. To open the Excel workbook provide the path and spreadsheet name. Finally, display the spreadsheet by setting its visible attribute to “True.” Mr Excel has some good tips in this course on VBA Macros that you may want to look up.
In the next example, we take a look at how to open a PDF file and print it using VBA functions.
Example 4: Program to Open and Print a PDF File
Sub OpenPDF() Dim PDF_File As String PDF_File = \"C:\\employee.pdf\" If Not FileLocked(PDF_File) Then Documents.Open PDF_File) End If End Sub Sub PrintPDF (PDF_File as string) Dim PDF_Reader as String PDF_Reader = \"C:\\Program Files\\Adobe\\Acrobat 6.0\\Reader\\AcroRd32.exe\" RetVal = Shell(PDF_Reader & \"/P\" & Chr(34) & PDF_File & Chr(34), 0) End Sub Sub CommandButton_Click() Call OpenPDF Call PrintPDF End Sub
Give the whole path and the filename of the PDF file that you want to open. Check if the file is already open. If not then continue opening the PDF file. In the code to print the file, give the full path to the Adobe Reader, Acrobat or any PDF reader on your computer. Finally open the PDF document and print when the button is clicked. Remember to open the PDF before printing.
Dealing with files is an important part of creating useful applications. VBA provides us the tools to process and manipulate files. Do write your own code so that you will master the art of handling files. We hope this tutorial helped you in understanding how to deal with files. Once you’re ready to move to the next level, try out this Ultimate VBA course to take things further.