Excel Functions

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have two questions. First after exporting a query I
want to insert a column and 2 rows with some text. I
have loaded the excel object library but can't get it to
work.
right now I have
with xlsapp
..worksheets.qryname.select
..etc......
end with
I am getting an runtime erro 438, Object doesn't support
this property or method.

what is the problem. i do i run excel functions from
access code?

also how do i delete a query after I am done with it. I
will be creating a large number of queries with code and
don't need to save them. i would like to keep the
clutter down. how do i get rid of them. thanks.
 
How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

Originally posted by Max Dunn

=================================================
Articles on How to Automate Excel:

I would like the data to simply be placed on a worksheet
that I've already spent time formatting (i.e. column widths, bolded column
headings...etc.)
Is there a way to do this?

You can't do this with TransferSpreadsheet.
You'll need to write VBA code that uses Automation to control Excel and
ensure
that the data ends up where you want it.

If you're new to Automation, here's a useful article giving the
basics of controlling Excel from Access:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/default.aspx?scid=KB;en-us;123859

ACC2000: Using Automation to Create and Manipulate an Excel
Workbook (Q210148)
http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

ACC: Using Automation to Create and Manipulate an Excel
Workbook (Q142476)
http://support.microsoft.com/default.aspx?scid=kb;en-us;142476

Recent versions of Excel have a Range.CopyFromRecordSet method
that can be very handy for importing data from Access.
 
Back
Top