The Workbook and Worksheet objects contain methods for opening, closing, and saving workbooks in the same manner a user might perform these operations from the Excel application. You can open and save workbook files using a variety of formats with VBA code. The same file formats may also be used to save individual worksheets within an existing workbook.
You use the Open() method of the Workbooks collection object to open Excel-compatible files. The Open() method accepts numerous arguments, but the only required argument is the Filename. The syntax for the Open() method of the Workbooks collection object, including all arguments, follows:
Workbooks. Open(Filename, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
You will never use most of these arguments, but those with unfamiliar names can be found in the online help. The following statement opens a workbook named MyWorkbook.xls located in the same directory as the active workbook. Note that the active workbook must be previously saved or the Path property of the Workbook object will not return a valid file path. Alternatively, you may use a string to specify the full path.
Dim filePath As String filePath = ActiveWorkbook.Path
Workbooks.Open Filename:=filePath & "\MyWorkbook.xls"
To save a workbook from a VBA program, use either the Save() method of the Workbooks collection object or the SaveAs() method of the Workbook object. The Save() method does not accept arguments and will save a new workbook to the default directory (the directory last used or the directory specified in the General tab of Excel's Options dialog if a workbook has not been previously saved).
The SaveAs() method accepts many of the same arguments as the Open() method of the Workbooks collection object. Most important are the Filename and FileFormat arguments used to specify the file's name and path, and the file type (.xls, .csv, .txt, and so on). The
FileFormat argument should be specified as one of VBA's defined xlFileFormat constants (look up xlFileFormat in the Object Browser to see a complete list). The syntax for the SaveAs() method of the Workbook object follows:
expression.SaveAs(Filename, Fileformat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
The following line of code saves the active workbook to the default directory as an Excel 2003 file (xlWorkbookNormal).
ActiveWorkbook.SaveAs Filename:= "MyWorkbook.xls", FileFormat:=xlWorkbookNormal
You may also save data in a specific worksheet using the SaveAs() method of the Worksheet object. Again, the two main arguments are Filename and FileFormat.
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)
You cannot use the SaveAs() method of the Worksheet object to save the entire workbook, but only data within a specific worksheet. 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 example saves the data in the active worksheet to a comma delimited text file named MyData.csv.
Text files only contain characters from the ANSI character set. The ANSI character pr^Os, set is comprised of 256 characters that represent the characters from your key-^"""XjJ board (alphabetical, numerical, punctuation, and so on).
ActiveSheet.SaveAs Filename:="MyData.csv", FileFormat:=xlCSV
Figure 7.10 shows an Excel worksheet with random numerical data that has been saved as a comma-delimited text file. Figure 7.11 shows the resultant file opened in WordPad.
Was this article helpful?