Tutorial for Microsoft Access Visual Basic: Read From and Write To XLS Files (Part 1)
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:
Example:
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
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
Similarly,
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).
xlBook.SaveAs FileName:=“C:\Users\Admin\Documents\Work\Spreadsheet.xls”
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
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.
Image Credit
<em>Wikimedia Commons.ZyMos</em>
Resource
Based on the writer’s experience