VBA Basic File Input and Output Options with Excel

1. VBA Basic File I/O

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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 )

8. 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”

9. 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

10. VBA Basic File I/O

11. 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

12. VBA Basic File I/O

13. 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

14. 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.

15. 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

16. 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

17. 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

18. VBA Basic File I/O

19. 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 VBA Constant(FileDialogType) Open msoFileDialogOpen Save msoFileDialogSaveAs File Picker msoFileDialogFilePicker Folder Picker msoFileDialogFolderPicker Example: Application.FileDialog(msoFileDialogOpen).Show

20. 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

21. 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

22. 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 (*.*)

23. 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.

24. 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

25. 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

26. 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

27. 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

28. 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

29. 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

30.

31.

32. VBA Basic File I/O

33. VBA Basic File I/O The open statement requires several arguments, including A string that designates the path to the specified file If the file does not exist then one will be created Also requires an access mode 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 # 1 This works if the file exists

34. 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”

35. 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

36. 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

37. 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

38. 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

39. 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 “, “111-2222” “Joe James “, “123-4567” “Jane Johnson “, “454-7890”

40. 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

41. 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

42. VBA Basic File I/O

43. VBA Basic File I/O

44. VBA Basic File I/O

45. VBA Basic File I/O

46. VBA Basic File I/O

47. VBA Basic File I/O