DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Sending MS Access Data to Excel - Part 4

Part 1 of this series looked at the basics of opening an Excel worksheet and adding data to it.

Part 2 of this series looked at how to change font sizes, add bold to the font, trigger auto column width, set a custom column width, and do a centre-merge operation on a title.

Part 3 of this series looked at how to shade the cells and put borders around them.

Part 4 will look at what to do if your Excel template provides cells that are "moving targets".

What do you do if the cell you want to write to changes its location. Maybe you have 3 versions of the same template. Version 1's cell is at "D24", version 2's is at "F14", and version 3's is at "G44". Worse yet, the addresses can change from week to week. The code I have shown you so far has the address hard coded.

Let's take advantage of Excel's cell naming facility. We will give the cell a name, and then Access can look for that name.

How do you name a cell in Excel? In version 2000, here is how I do it.

  1. open the Excel file that is used for a template in the Access program
  2. select the target cell
  3. use the menu system to choose Insert, Name, Define
  4. type a name for the cell
  5. click on the Add button
  6. click on the OK button
  7. notice that in the top part of the spreadsheet, where you normally see a cell reference like D14, you now see the name
  8. save the Excel file

Now for the code that references this. As usual, I am not suggesting that I have discovered the best code, but it works. Let me know if you have found a "slicker" way to do this.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Also, this code will only work if you have Excel references turned on. The References section is found under the Tools menu in the VBA editor. See the above mentioned article on DAO for more details on the References menu.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim local_variable as String

'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = False '--- I do not need to see it working!!
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A.xls")

'--- I always name my worksheets something specific, and this next
'--- line refers to a specific worksheet (Excel names the first
'--- one "sheet 1" by default)

Set objSht = objWkb.Worksheets("output")

local_variable = objSht.Range("Name_of_Excel_cell_goes_here")

objWkb.Close False
objXL.Quit
set objSht = nothing
set objWkb = nothing
set objXL = nothing

Your Access program now has the data from the named cell. If you want to, instead, put data INTO the named cell, just turn the one line of code around. (assumes you have pre-populated the local_variable with some data from a form or recordset)
objSht.Range("Name_of_Excel_cell_goes_here") = local_variable

Further Suggestions:

To make sure that you find out whenever we are adding more tips, subscribe to our newsletter. Just click the Subscribe button to the left to start the process.

Part 5 shows how to create a formula for a total of a column, See it here.

Entire series ...

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007 Richard W. Killey. All Rights Reserved.