CST-273-01-File
Download
Report
Transcript CST-273-01-File
VBA Basic File I/O
VBA Basic File I/O
VBA includes several objects, methods, and
functions that can be used for file I/O
One possibility for file I/O
The Workbook Object
Its methods for saving and opening files
If A VBA application requires File I/O
It may Require
A relatively small amount of data stored in
program variables
NOT located in a worksheet or document
VBA Basic File I/O
With Excel the programmer can copy the data
To a worksheet
So the user can save the data in the usual way
File Save Menu Item
Directly to a file (the contents of variables)
More convenient to write directly to a file
User does not need to be concerned with
the task
Prevents the user from altering the data
before saving
VBA Basic File I/O
In the Real World
Many types of files are stored on a computers
hard drive
Operating System Files (Mac / Windows / Unix)
Image files
Application Files (Excel)
Most are created by applications and are
proprietary
Proprietary files should only be accessed by
the application form which they were created
VBA Basic File I/O
Proprietary file have unique extension
.accdb
.xlsm
.doc
Etc………….
The file extensions are used by the OS to identify the
application that created the file
A proprietary file (like Excel) can contain
Text and numerical information
Formatting information (bold, font size and type)
Non-textual information
Images
Charts
Can only be opened by the application that created it
VBA Basic File I/O
File I/O Using Workbook and Worksheet
Objects
Contain methods for
Opening
Closing
Saving
In the same manner a user might perform
these operations
You can save workbook files in a variety of
formats with VBA code
The same file formats can save individual
worksheet information within a workbook
VBA Basic File I/O
Opening and Saving Workbooks
Use the Open() method of the Workbooks collection
object to open Excel-compatible files
The Open() method has numerous arguments
Only required argument
Filename
Syntax
Workbooks.Open(FileName, UpdateLinks, ReadOnly,
Format, Password, WriteResPassword,
IgnoreReadOnlyRecommended, Origin, Delimiter,
Editable, Notify, Converter, AddToMru, Local,
CorruptLoad)
VBA Basic File I/O
You will never use most of the arguments
Unfamiliar names can be found in the online help
The following statement opens a workbook named
myWorkbook.xlsm located in the same directory as
the active workbook
Note: The active workbook must be previously saved or
the Path property of the Workbook object will not
return a valid file path
Dim filePath As string
filePath = ActiveWorkbook.Path
Workbooks.Open FileName:=filePath & “\myWorkbook.xlsm”
VBA Basic File I/O
To save a workbook from a VBA program, use
Save() method of the Workbooks collection
object
SaveAs() method of the Workbook object
The Save() method does not accept arguments
Will save to the default directory
The directory last used
The directory specified in the General tab of
Excel’s Options dialog box
If not previously saved
VBA Basic File I/O
VBA Basic File I/O
The SaveAs() method accepts many of the same
arguments as the Open() method of the
Workbooks collection object
Most important
FileName
FileFormat
Type
.xlsm, .csv, .txt, and so on
Should be specified as one of VBA’s defined xlFileFormat
constants
Look in the object browser for a complete list
VBA Basic File I/O
VBA Basic File I/O
Syntax for SaveAs() method
expression.SaveAs(FileName, FileFormat, Password,
WriteResPassword, ReadOnlyRecommended,
CreateBackup, AccessMode, ConflictResolution,
AddToMru, TextCodepage, TextVisualLayout, Local)
The following code saves the active workbook to
the default directory as an Excel 2003 file
(xlWorkbookNormal)
ActiveWorkbook.SaveAs FileName:=“myWorkbook.xls”, FileFormat:=xlWorkbookNormal
VBA Basic File I/O
You may also save data in a specific worksheet using the SaveAs()
method of the Worksheet object
The two main arguments are
FileName
FileFormat
expression.SaveAs(FileName, FileFormat, Password,
WriteResPassword, ReadOnlyRecommended, CreateBackup,
AddToMru, TextCodepage, TextVisualLayout, Local)
You cannot use SaveAs() method of the Worksheet object
to save the entire Workbook
Only data within a specific worksheet.
VBA Basic File I/O
Typically you save the content of a single worksheet as
some type of text file
Comma-delimited
Tab-delimited
.html
.xml
And so on………..
The following saves the data in an active worksheet to
a comma-delimited text file
ActiveSheet.SaveAs FileName:=“MyData.csv”, FileFormat:=xlCSV
VBA Basic File I/O
Using VBA File I/O Methods
In addition to the methods of the Workbooks, Workbook and
Worksheet Objects
Open(), Save(), SaveAs()
VBA includes several more I/O objects such as the
Dialogs, FileDialog, FileSystem, FileSystemObject
and other subordinate objects
Using the FileDialog Object
The standard dialog used in Office applications for opening
and saving files
Allows users to specify files and folders that a program should
use
Will return the paths of the selected files or folders
You can also use the FileDialog object to execute the
associated action of the specified dialog box
VBA Basic File I/O
A reference must be set to the
Microsoft Office object Library before
you can use the FileDialog object.
From the VBA IDE select Tools,
References, and be sure to check the box
labeled Microsoft Office 12.0 Object
Library
VBA Basic File I/O
VBA Basic File I/O
The fileDialog object contains two methods called
Show()
Use the show() method to show one of four possible dialog
boxes depending on the constant passed to the fileDialog
property of the Application object
Dialog Type
Open
Save
File Picker
Folder Picker
VBA Constant(FileDialogType)
msoFileDialogOpen
msoFileDialogSaveAs
msoFileDialogFilePicker
msoFileDialogFolderPicker
Example:
Application.FileDialog(msoFileDialogOpen).Show
VBA Basic File I/O
The execute() method enables the user to
Carry out the specified action of the
dialog box
For files compatable with Excel
.xlsm, .xlt, .csv, …….
For example
The Open dialog box allows users to
select one or more files to open when the
Execute() method of the FileDialog
object is invoked
VBA Basic File I/O
The FileDialogFilters and FileDialogSelectedItems
Collections
The fileDialog object has two subordinate collection
objects
FileDialogFilters
Collection object contains a collection of FileDialogFilter
objects
Represents the file extensions used to filter which files are
displayed
Used with Open and SaveAs dialog boxes
VBA Basic File I/O
Use the filters property of the FileDialog object to
return the FileDialogFilters collection
Use the item property of the FileDialogFilters
collection object to return a FileDialogFilter object
The Description and Extension properties of the
FileDialogFilter object return the description
For example, All Files
And the file extension used to filter the
displayed files
For example (*.*)
VBA Basic File I/O
CheckFileFilters() generates a list of all
possible file filters and their descriptions.
Then outputs the lists via message boxes
The Procedure loops through each
FileDialogFilter object in the
FileDialogFilters collection and
concantonates their Description and
Extension properties to separte string
variables.
VBA Basic File I/O
Public Sub cmdCheckFileFilters_Click()
'Use the Filters property of the FileDialog object to return a FileDialogFilters collection
'A collection of FileDialogFilter objects that represent the types of
'files that can be selected in a file dialog box that is displayed using
'the FileDialog object
Dim fileFilters As FileDialogFilters
'Represents a file filter in a file dialog box displayed through the FileDialog object.
'Each file filter determines which files are displayed in the file dialog box.
Dim fileFilter As FileDialogFilter
Dim i As Integer
Dim descrs As String
Dim xtns As String
Set fileFilters = Application.FileDialog(msoFileDialogOpen).Filters
'loop through a collection and build strings of
'all extensions and descriptions
For i = 1 To fileFilters.Count
Set fileFilter = fileFilters.Item(i)
'add carriage return and line feed to strings
descrs = descrs & fileFilter.Description & vbCrLf
xtns = xtns & fileFilter.Extensions & vbCrLf
Next i
MsgBox descrs
MsgBox xtns
End Sub
VBA Basic File I/O
FileDialogSelectedItems collections object contains the
paths as strings to the files or folders selected by the user
Use the SelectedItems property of the FileDialog object to
return the FileDialogSelectedItems collection
The GetSelectedItem() sub procedure shows
Open dialog
Then loops through all items selected by the user
To build the string containing the file path
The file paths are outputed in a message box
Note that the Item property of the
FileDialogSelectedItems object returns a string
VBA Basic File I/O
You can use the Add() method of the FileDialogFilter collection
object to create your own list of filters
The LoadImage() sub procedure shows the File Picker dialog box
after clearing the FileDialogFilters collection and adding two new
filters
(*.*, and *.bmp)
The Add() method requires a
Description
Extension
Optional Position argument indicates the position of the added
filter in the list
VBA Basic File I/O
Public Sub cmdLoadImage_Click()
Dim fileDiag As FileDialog
Dim imgPath As String
Set fileDiag = Application.FileDialog(msoFileDialogFilePicker) ‘dialog box
With fileDiag
.AllowMultiSelect = False ‘User can only select one file
.Filters.Clear
.Filters.Add Description:="All files", Extensions:="*.*"
.Filters.Add Description:="Image", Extensions:="*.bmp", Position:=1
.FilterIndex = 1
‘sets which filter is selected when the dialog is show (essentially new)
.InitialFileName = ""
.Title = "Select BMP file“
‘called to display after properties are set
If .Show = -1 Then
'User pressed action button, open in this case
imagePath = .SelectedItems(1) ‘returns path to file selected
Sheet1.imgTest.Picture = LoadPicture(imagePath) ‘NOTE: you must add an image control & name
End If
End With
End Sub
VBA Basic File I/O
If you use an execute() method of the FileDialog object
when selecting objects not compatible with Excel
Will generate a runtime error or
Open a workbook with unreadable data
If AllowMultiSelect property is set to true
User allowed to select multiple files
Open button
Execute() method
Attempts to open all selected files
VBA Basic File I/O
The file system object
Is a collection of methods that you can use to set and obtain
information about
Files
Directories
Drives
The Open Statement
Used to read / write data to a file
The following table summarizes
the type of access
Modes or functions available
For reading / writing data to a file with VBA
VBA Basic File I/O
VBA Basic File I/O
The open statement requires several arguments, including
A string that designates the path to the specified file
Also requires an access mode
If the file does not exist then one will be created
Append
Binary
Input
Output
Random
And a file number
You can open multiple files in VBA programs, but they must be assigned unique file numbers
Optional Paramaters include
An access parameter
Read
Write
Read / Write
Lock (used to restrict operations on a file from other programs)
Record length (specifies the length of the buffer or record)
Open “C:\Data\Test.txt” for Input As #
This works if the file exists
1
VBA Basic File I/O
Open “C:\Data\Test.txt” for Output As #1
This will create a file for output if it does not already exist
Sequential File Access
Writing information to a squential access file is like recording music to a cassette tape
Songs vary in length
Recorded one after another
Difficult to access a particular song (location)
When writing to a sequential file
Individual pieces of data are
Usually stored in variables
Vary in length
Are written to the file one after another
“John Smith”, “111-2222”
“Joe James”, “123-4567”
“Jame Johnson”, “456-7890”
VBA Basic File I/O
“John Smith”, “111-2222”
“Joe James”, “123-4567”
“Jame Johnson”, “456-7890”
Names and phone numbers were all written to the file as strings
Enclosed in quotation marks
Numeric values written to a sequential file will not contain quotation marks
The strings containing names vary in length
Require different amounts of memory for storage
If access to part of a sequential file is desired at a later time
i.e. Jame’s phone number
The entire file must be read into memory
Its not possible to know the location of the disired component within the file
After loading the enitre file has to be searched for the desired value
This makes sequential access inefficient with very large files
Squential access works good for small files
VBA Basic File I/O
The CreateSeqFile() sub procedure
Writes textual info form the first three rows
In columns A & B of a worksheet to a sequential access file
Private Sub cmdSeqFile_Click()
Dim filePath As String
Dim i As Integer
filePath = ActiveWorkbook.path & "\SeqPhone.txt"
Open filePath For Output As #1
For i = 1 To 3
Write #1, Cells(i, "A").Value, Cells(i, "B").Value
Next i
Close #1
‘stop i/o
End Sub
VBA Basic File I/O
Using Write # places quotation marks around each value written to the file
Write # adds a new line character to the end of the last value written to the file
Resulting in 3 new lines (loop executed 3 times)
Because the structure of the file is know
It’s a simple task to alter the procedure to read the data
Dim filePath As String
Dim i As Integer
Dim theName As String
Dim theNumber As String
i=1
filePath = ActiveWorkbook.path & "\SeqPhone.txt"
Open filePath For Input As #1
Do While Not EOF(1)
Input #1, theName, theNumber
Cells(i , “A”).value = theName
Cells(i , “B”).value = theNumber
i=i+1
Loop
Close #1
‘stop i/o
VBA Basic File I/O
Random Access File
Allow programmer to access specific values within the file without
loading the entire file into memory
Accomplished by
Ensuring the data elements are the same length before writing to the
file
Consider the phone book example
Instead of storing the information as variable length strings
Store them as fixed length strings
The combination of the two fixed-length strings that follow
require the same amount of memory for every line written to the
file
This makes it easier to locate a particular line in the file when
the data is input
VBA Basic File I/O
Dim theName As String*20
Dim theNumber As String*8
If the name to be stored is less than 20 characters
Spaces are added to match the defined length
If the name is greater than 20 characters, only the first 20 are stored
Important to define the string length for the largest input
The resulting data file may look like this
“John Smith
“Joe James
“Jane Johnson
“, “111-2222”
“, “123-4567”
“, “454-7890”
VBA Basic File I/O
Each line in the file requires the same amount of memory to store
Referred to as a record
Records can be represented by one or more values of the same or different
data types
String, Integer, and so on
Because the record lengths are identical, finding a specific record in a file
is relatively easy
Without having to load the entire file
Rather then declare the individual elements of a record as separate variables
Define a custom data type that can be used in a variable declaration
The variable of the newly defined data type can include all of the
elements of the record
VBA Basic File I/O
To define a phone record for the previous example, a custom data type that includes both string elements must be
declared in the general declarations section of a module.
Private Type Phone
theName as String*20
theNumber as String*20
End Type
‘the following code uses the above data type to write records to a file
Dim phoneRec As Phone
Dim filePath As String
Dim i As Integer, recNum As Integer
recNum = 1 'recordNumber must start with 1
filePath = ActiveWorkbook.path & "\randomPhone.dat"
Open filePath For Random As #1 Len = Len(phoneRec) 'specify the length of the record
For i = 1 To 3
phoneRec.theName = Cells(i, "A").Value
phoneRec.theNumber = Cells(i, "B").Value
Put #1, recNum, phoneRec 'Put writes data to the file (use Get to read data from a file)
recNum = recNum + 1
Next i
Close #1
VBA Basic File I/O
VBA Basic File I/O
VBA Basic File I/O
VBA Basic File I/O
VBA Basic File I/O
VBA Basic File I/O