Importing a Spreadsheet
You can easily learn how to have Microsoft Access Visual Basic read from and write to .xls files. One of the simplest things you can do to
integrate Excel and Access is to import an .xls spreadsheet file into Access as a new or existing table. There’s already a Bright Hub guide to tell you how to do this, but if you have the need to use VBA, then you’ll need this article. The simplest way to do it is to use the following command:
Docmd.TransferSpreadsheet , , , , ,
You’ll need to replace the terms in angled brackets with the relevant data as follows:
- Choose from a dropdown when entering the command. You’ll want acImport to import data from a spreadsheet.
- Again, you’ll be shown a dropdown when you get to this part of the command. The choices you see will depend on the version of Access you are running, but acSpreadsheetTypeExcel9 refers to Excel 2003 .xls files.
- Enter the name of the table you wish to import the data into. If it doesn’t exist, it will be created; if it does exist, then the field names must match (or at least the number of columns must match). Existing data in the table will not be deleted.
The full filepath and name of the Excel spreadsheet you wish to import.
True or False, if True then the top row of the spreadsheet will be imported as fieldnames; if False, it will be imported as another row of data.
If you only wish to import certain rows or columns, you can enter the range here. You can also specify a worksheet here. If you don’t, the leftmost worksheet will be used (even if it’s hidden).
Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, “Test”, “C:\Users\Admin\Documents\Work\Spreadsheet.xls”, True, “March!A:H”
The above example will import Columns A-H of the March worksheet of the Spreadsheet.xls Workbook into a table called Test. Row 1 will be used to determine fieldnames for the table.
Exporting a Spreadsheet
Using the Docmd.Transferspreadsheet method above. Only a few changes are required to export an existing Table or Query to a spreadsheet. Instead of acImport, use acExport and do not enter anything for or . is now the name of the table or query you wish to export, and is the path where the spreadsheet should be created. The worksheet will be named after the table or query you export (although it may be truncated if it’s too long). Note that if the spreadsheet already exists, any worksheet with that name will be replaced by the data you export.
Please continue on page 2 for more about Microsoft Access Visual Basic: Read from and Write to XLS
Microsoft Access Visual Basic - Read and Write to .XLS (Excel Spreadsheet) Using VBA
There might be times when you don’t want to import a whole worksheet, but you want to use Access to read some information from an Excel spreadsheet. You can use VBA to “open” an existing spreadsheet and then you can completely control that spreadsheet from within Access. From reading the cell contents, to changing the formatting, and everything that you normally do in Excel can be replicated using VBA code, allowing you to programmatically alter or create entire spreadsheets. Use the snippet of code below to open an existing spreadsheet (change the path in the second line to a spreadsheet on your pc).
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Open(“C:\Users\Admin\Documents\Work\Spreadsheet.xls”)
Set xlSheet = xlBook.Worksheets(1)
This last line refers to the worksheet that you see when you open up the file in Excel. You can change the number in brackets to refer to any of the other sheets in the workbook, or you can enter the name of the sheet, like this:
Set xlSheet = xlBook.Worksheets(“Sheet1”)
From here, you can read and write the data however you want.
strCell = xlSheet.Cells(2,1) - this command will copy the text in cell A2 to a variable called strCell
xlSheet.cells(1, 2) = “Access” - will replace the contents of cell B1 with the word Access.
That gives you the basics of reading and writing the data from an Excel Spreadsheet. The only thing left to do is to close the spreadsheet again after you’ve finished (saving it, if you’ve created a new one, or made edits to an existing one that you wish to save).
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
What Else Can You Do?
Using the methods in the last section, you can do pretty much anything you want to the spreadsheet while you have it open in Access VBA. This can become a very powerful tool in automating the creation of spreadsheet reports and can also be expanded to cover Word documents and Powerpoint presentations.
Based on the writer’s experience