Using an Excel Worksheet function in Access Module

  • Thread starter Thread starter keith
  • Start date Start date
K

keith

Public Sub test()
Hello,

I thought I asked this before, but can't seem to find the
answer when I search the forum. Hope it's not too much
trouble to ask again.

I have a test subroutine in Access. I need to call the
Excel NORMDIST(...) function, and use the answer in Access.
It seems to me that the following code should work, but
for adding the right language where the "????"s are. But
can't find this in the help file.

any suggestions?


public sub test()

Dim MyVar1 As Double

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

MyVar1 =ExcelSheet.?????

ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

End Sub
 
Keith

This is some code I wrote for a VB6 application where I needed some Excel Functions. It should be able to be tailored pretty easily

Public Sub setup_Rest(
Dim excel_app As Excel.Applicatio
Dim excel_sheet As Excel.Workshee

excel_app = New Excel.Applicatio
excel_app.Visible = Fals
excel_app.Workbooks.Add(
If Val(excel_app.Application.Version) >= 8 The
excel_sheet = excel_app.ActiveShee
Els
excel_sheet = excel_ap
End I

' r_df & er_df are doubles defined elsewhere
excel_sheet.Cells._Default(1, 1) = "=FINV(0.05," & r_df & "," & er_df & ")

r_fc = excel_sheet.Cells._Default(1, 1

excel_app.ActiveWorkbook.Close(False
excel_app.Quit(
excel_sheet = Nothin
excel_app = Nothin

End Sub
 
Hi Jim,
Thanks very much
Keith
-----Original Message-----
Keith,

This is some code I wrote for a VB6 application where I
Click to expand...
needed some Excel Functions. It should be able to be
tailored pretty easily.
 
HI Jim,

I appreciate your suggestion, and find that something is
different here. on the Excel_app = New
Excel.Application statement, I receive an "Invalid use of
property" error, and both the ...Add and ...quit
statements are red -- won't run

I have the Excel 10.0 object library loaded.

Any thoughts on this?

Keith


-----Original Message-----
Keith,

This is some code I wrote for a VB6 application where I
Click to expand...
needed some Excel Functions. It should be able to be
tailored pretty easily.
 
Keith

What language are you programming in

-Ji

----- Keith wrote: ----

HI Jim

I appreciate your suggestion, and find that something is
different here. on the Excel_app = New
Excel.Application statement, I receive an "Invalid use of
property" error, and both the ...Add and ...quit
statements are red -- won't ru

I have the Excel 10.0 object library loaded

Any thoughts on this

Keit


-----Original Message----
Keith
Click to expand...
needed some Excel Functions. It should be able to be
tailored pretty easily
 
Hi Jim,
It is VBA in an Access module.
Keith
-----Original Message-----
Keith,

What language are you programming in?

-Jim

----- Keith wrote: -----

HI Jim,

I appreciate your suggestion, and find that something is
different here. on the Excel_app = New
Excel.Application statement, I receive an "Invalid use of
property" error, and both the ...Add and ...quit
statements are red -- won't run

I have the Excel 10.0 object library loaded.

Any thoughts on this?

Keith


where I
needed some Excel Functions. It should be able to be
tailored pretty easily. (0.05," &
r_df & "," & er_df & ")"
.
Click to expand...
 
Hi Jim,
Thanks very much. I really apreciate your follow-up.
I'll work with this code.
Have a great day,
keith

-----Original Message-----
Keith,

Sorry about that, I just pasted from my VB6 code and
Click to expand...
didn't even check to see if it worked with VBA.
The first thing you need to do add a reference to the
Click to expand...
Microsoft Excel Object Library (Tools->References) Make
sure Microsoft Excel Object Library is checked.
Second thing, my code was all sorts of buggy. Here is a
Click to expand...
version that works (I even tried this one).
 
Back
Top