Link Textbox Contents to Worksheet Data

Link Textbox Contents to Worksheet Data on Glenn Lloyd's "OfficeTipsAndMethods" blog is a nice discussion of some really good techniques for working with charts in Excel.

There's a big difference between doing a chart and doing a chart well. For instance I've seen charts done lots of times that couldn't easily be updated to the next months data. When discussing one of his techniques, Glenn says "Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title." Hear, hear, Glenn!

Excel: Using Custom Functions in Dynamic Ranges

This is neat. Define ranges in Excel not as a static group of cells but using a function which will dynamically determine which cells to include.

TransferSpreadsheet: Specifiying the range or worksheet to export to

The help for the TransferSpreadhseet action says you can't do this, but that's not true. It says, in part:

TransferSpreadsheet Action

You can use the TransferSpreadsheet action to import or export data between the current Microsoft Access database (.mdb) or Access project (.adp) and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access.

...

The TransferSpreadsheet action has the following arguments.

.....

Range

The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax does not work in Microsoft Access 97 or Microsoft Access 2000). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, Excel 8.0, or Excel 2000 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.

Note: When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

The last point is incorrect. You can specify a range when exporting. The following code will export the specified query to a specific worksheet within the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyWorksheetName"

And this example exports it to a specfic named range withing the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyRange"

Excel won't close!

If you're using automation from Access to Excel (or I suppose between any two MS Office apps) and the application you've opened through automation won't close, it may be because of "unqualified references". For a complete explanation see http://support.microsoft.com/kb/319832.

The likely culprit is a line like:

  Cells(6,3)=SomeValue

VBA figures out that Cells must refer to the active worksheet of the excel application, but when it does so it leaves something hanging somewhere and bad things happen later.

Instead, specify what Cells relates to- something like:

  dim xlMySheet as Excel.Worksheet
  ... (you have to Set xlMySheet to the worksheet in here somewhere)
  xlMySheet.Cells(6,3)=SomeValue

The other symptom you sometimes see caused by this error is "it works once, but then doesn't work the second time".