MS excel functions in MS ACCESS

[officework13]

I would like to use the excel functions in access. i have had some luck with referencing MS excel 9.0 library and using a module.

however there are a couple of fxns in excel that do not work.
Public Function ExcelDAY(serial_number As Double) As Double
ExcelDAY = Excel.WorksheetFunction.Day(serial_number)
End Function

this function which is to pick what day of the week from a date does not work. as well the fxn WEEKNUM does not work.

any assistance would be appreciated.

is there a simple way to access all the excel files when doing a query through the expression builder. or do you have to do each one individually through a module.


as well are all the functions in my present excel functions list available through this way or only those listed on the object browser "worksheet functions"

 

[MichaelRed]

Sosme Excel functions require 'arguments' which are generally ambigious and counter intutive from within Ms. A. Some Excel functions are just implemented slightly differently in Ms. A Some Exce; functions exist (at least by name) in Ms. A but act differently (require different args, or return different (types) of values.

Using EXCEL lib in Ms. A., is (to me) somewhat likend to the def of a camel - a horse built by a committee.

My ultimate suggestion is to divorce the two and learn to use one or the other more intensively. Date functions exist 'aplenty' in Ms. A., and can easily be used to construct the DOW (Day of the Week) function.

One such function happened to be discussed earlier

Given a DATE, return the date of a day within the calendar week, as in for this week, what is/was Tuesdays date

Which is answered as:
? basWkDayDt(Date, vbTuesday)

and returns:
8/27/02
(of course it would help to know this was 'asked' on 8/30/2002) - but anyone actually interested enough to get this far would be able to glean it from the posting date.

Public Function basWkDayDt(DtIn As Date, WkDay As Integer) As Date
    basWkDayDt = DateAdd("d", -1 * Weekday(DtIn, WkDay) + 1, DtIn)
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[officework13]

That did not work for me.
i am looking to see what day (mon - fri)and what week number the following date was (June 5, 2001)what would be the access formula for this.

 

[mikey69]

Agree with MichaelRed that mixing is not a good idea. For week no use DatePart, for DOW use Weekday. Search the help file for all the detail.