View Full Version : Access Function from Excel
akn112
03-12-2008, 08:04 AM
Hey all,
was just wondering how you can fire off an access function from excel. My code currently (does not run) looks like this:
Dim db As Access.Application
Set db = GetObject("c:\CSV.mdb")
db.Run "InsertDates", Nz(cboFromDate, "2005-01"), Nz(cboToDate, CStr(Format(Date, "yyyy-mm")))
Set db = Nothing
Thanks!
BreakfastGuy
03-12-2008, 08:11 AM
You can probably find what you need here (http://www.excelguru.ca/taxonomy/term/9)
akn112
03-12-2008, 08:17 AM
You can probably find what you need here (http://www.excelguru.ca/taxonomy/term/9)
i'm not quite sure which article your referring to. :think:
BreakfastGuy
03-12-2008, 08:26 AM
try this one! (http://www.excelguru.ca/node/23)
akn112
03-12-2008, 08:37 AM
was looking through the article and could only find examples of connecting to the database and passing an sql statement to retrieve a specific recordset from it. i guess my question is more specifically,
how do you run a public function (within a module) in an Access database from excel?
sorry about the confusion
Bob Phillips
03-12-2008, 09:03 AM
I am no Access guru (hate Access with a passion), but couldn't you just issue a DoCmd via automation?
BreakfastGuy
03-12-2008, 09:04 AM
Something like this?
Public Sub Access_Procedure()
?this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = CurrentDb
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing
End Sub
BreakfastGuy
03-12-2008, 09:07 AM
Don't forget in Excel you need to set the reference for the Microsoft Access Object Library, in Excel, go to VBA editor (press ALT + F11), then select Tools from the top menu and then References.
Bob Phillips
03-12-2008, 09:13 AM
Using DoCmd as I suggested would be something like this
Sub CallAccessMacro()
Dim oAccess As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Open "C:\bob.mdb"
oAccess.DoCmd.RunMacro "MyMacro"
'rest of the code
oAccess.Quit
Set oAccess = Nothing
End Sub
Bob Phillips
03-12-2008, 09:14 AM
Don't forget in Excel you need to set the reference for the Microsoft Access Object Library, in Excel, go to VBA editor (press ALT + F11), then select Tools from the top menu and then References.
No you don't, you are using late binding.
BreakfastGuy
03-12-2008, 09:16 AM
I stand corrected!
:ipray:
akn112
03-12-2008, 09:18 AM
BreakfastGuy: the .Run function doesnt seem to kick off my function. It's what i was trying to use before but couldnt get it to work
xld: I had thought about using docmd.runmacro but my function requires inputs which need to be passed from my excel sheet. I'm not sure if there's a way to specify dynamic parameters for a function within a macro in access.
edit: I had the reference in cause i was using early binding. The drop down list of possible functions is like my "cheat sheet" =P
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.