Running Access Routine From Excel?

[PeteCresswell]

I'm in Excel VBA, trying to make things happen in an MS Access .MDB.
e.g.
----------------------------------------------------------------------------------------------
Private Sub cmdImport_Click()
debugStackPush mModuleName & ": cmdImport_Click"
On Error GoTo cmdImport_Click_err

' PURPOSE: To re-load ProjectedCashFlows...mdb with information from
this very sheet
' and then to re-load the "Final Output" tab on this sheet
with the new data.

Dim CashFlowDB As DAO.Database

Set CashFlowDB = DBEngine(0).OpenDatabase("C:\ProjectedCashFlows.
006\ProjectedCashFlows.002h.mdb")

With CashFlowDB
Run "mcrHelloWorld"
End With

cmdImport_Click_xit:
debugStackPop
On Error Resume Next
Set CashFlowDB = Nothing
Exit Sub

cmdImport_Click_err:
bugAlert True, ""
Resume cmdImport_Click_xit
End Sub
----------------------------------------------------------------------------------------------

 

[PeteCresswell]

For the attempted code, make it:
----------------------------------------------------------------------------------------------
Dim CashFlowDB As Object

Const myDbPath As String = "C:\ProjectedCashFlows.
006\ProjectedCashFlows.002h.mdb"


Set CashFlowDB = CreateObject("Access.Application")

With CashFlowDB
.Visible = False
.OpenCurrentDatabase (myDbPath)
.DoCmd.runcode "HelloWorld"
End With
----------------------------------------------------------------------------------------------

No problem with .DoCmd.RunMacro, but I'd like to be able to run
subs and functions directly - without going through macros.

Is there any hope?

 

[Klatuu]

I haven't tried to do this before, but I do have some insight from an Access
perspective that may help.
I know that in Access Macros, the RunCode action will only run functions.
It will not run subs.
I also wonder if putting the name of a function in quotes is correct, or
should it be just the name of the function without quotes?

Have you asked this question in an Excel group?

 

[Albert D. Kallal]

No problem with .DoCmd.RunMacro, but I'd like to be able to run

subs and functions directly - without going through macros.

Is there any hope?

Use:

CashFlowDB.Run "Name of public sub in standard code module"

 

[Guest]

You can do it that way, but is it worth it? I decided to
keep all of my code in one place. That is all the code in
Excel, no code in Access, or all the code in Access, no
code in Excel.

If you are using VBA code in Access, you can run most
of the same code in Excel. Just use

CashflowDB.execute

to run your import, export, update, append and delete queries.
This won't run user defined functions included inside the query,
but most people don't have those, and they aren't normally
required. If you need calculations, you can do them with code
in Excel.

(david)

I'm in Excel VBA, trying to make things happen in an MS Access .MDB.
e.g.
-------------------------------------------------------------------------- --------------------
Private Sub cmdImport_Click()
debugStackPush mModuleName & ": cmdImport_Click"
On Error GoTo cmdImport_Click_err

' PURPOSE: To re-load ProjectedCashFlows...mdb with information from
this very sheet
' and then to re-load the "Final Output" tab on this sheet
with the new data.

Dim CashFlowDB As DAO.Database

Set CashFlowDB = DBEngine(0).OpenDatabase("C:\ProjectedCashFlows.
006\ProjectedCashFlows.002h.mdb")

With CashFlowDB
Run "mcrHelloWorld"
End With

cmdImport_Click_xit:
debugStackPop
On Error Resume Next
Set CashFlowDB = Nothing
Exit Sub

cmdImport_Click_err:
bugAlert True, ""
Resume cmdImport_Click_xit
End Sub
--------------------------------------------------------------------------

--------------------

 

[PeteCresswell]

You can do it that way, but is it worth it? I decided to
keep all of my code in one place.

This started out with all the code in Excel.

Then somebody told me about .xla files and I moved it there - to
enhance the one-stop-shopping aspect.

Then, the user came up with some fairly heinous UI requirements that I
couldn't figure out how to do in Excel. They were probably 100%
doable - but I didn't have the expertise to implement them in a time-
efficient manner....so I spun off the .MDB, moved the .XLA code to
there, and built on that.

Now they've come full circle and all they want to see is a single
"Import" button on the Excel worksheet - which does it all.

I'd *like* to move the code back to a .XLA but there's a goodly number
of queries and some of them are queries-within-queries that culminate
in pivot table presentations - and I'm intimidated by that.

For now, we have a (to me, at least) slightly-suspect situation:
My Excel button is instantiating an instance of MS Access, and then
opening the .MDB through it. When it calls the desire routine, it
passes it's own path (the .XLS's path) to the .MDB routine. Then
the .MDB routine instantiates an instance of Excel, uses it to open up
the same .XLS that is doing the call, scrapes data from it, and then
creates a temporary .XLS and closes the calling .XLS.

The routine in the calling .XLS then opens up the temporary .XLS and
copies a sheet into itself.


Messy business, if you ask me.....and I'm not exactly proud of it.

viz:
----------------------------------------------------------------
Private Sub cmdImport_Click()
1000 debugStackPush mModuleName & ": cmdImport_Click"
1001 On Error GoTo cmdImport_Click_err

' PURPOSE: To re-load ProjectedCashFlows...mdb with information
from this very sheet
' and then to re-load the "Final Output" tab on this sheet
with the new data.
'
' NOTES: 1) The .MDB creates a temp sheet in C:\TEMP and then we
copy it into this workbook

1002 Dim CashFlowDB As Object

Dim myParmArray(1) As String
Dim myDbPath As String
Dim myOwnPath As String
Dim tempBookName As String
Dim myOwnBookName As String

Const tempPath As String = "C:\Temp
\AccrualAmountsReceived_FinalProduct.xls"
Const myOwnDir As String = "C:\ProjectedCashFlows.006"
Const myDbName As String = "ProjectedCashFlows.003a.mdb"
Const prodSheetName As String = "Final Result"
Const tempSheetName As String =
"qryActualAmountsReceived_FinalP"

Const myOwnSheetName As String = "Loss Analysis_Formula"

' --------------------------------------------------------------
' Issue confirmation dialog in case user clicked this button by
mistake

1003 If MsgBox("Do you really want to re-import the data", vbQuestion
+ vbYesNo, "Please Confirm") = vbYes Then
1009 Application.Cursor = xlWait

' --------------------------------------------------------------
' Concoct the paths we need

1010 myDbPath = myOwnDir & "\" & myDbName
1019 myOwnPath = myOwnDir & "\" & Workbooks(1).Name

' --------------------------------------------------------------
' Call the MS Access routine that creates the temp sheet

1020 myParmArray(1) = myOwnPath
1029 Set CashFlowDB = CreateObject("Access.Application")

1030 With CashFlowDB
1031 .OpenCurrentDatabase (myDbPath)
1032 .Visible = False
1033 .Run "LossRatioSpreadSheet_Import", myParmArray
1034 .Quit
1039 End With

' --------------------------------------------------------------
' Open up the temp workbook just created and move it's single
' worksheet to this .XLS

1040 myOwnBookName = Workbooks(1).Name
1041 Workbooks.Open tempPath
1042 tempBookName = Workbooks(2).Name
1043 Workbooks(tempBookName).Activate

On Error Resume Next
Workbooks(myOwnBookName).Worksheets(prodSheetName).Delete
On Error GoTo cmdImport_Click_err

1045 Workbooks(tempBookName).Worksheets(tempSheetName).Move
After:=Workbooks(myOwnBookName).Sheets(myOwnSheetName)
1049 Worksheets(tempSheetName).Name = prodSheetName

1990 Application.Cursor = xlDefault
1999 End If

cmdImport_Click_xit:
debugStackPop
On Error Resume Next
Set CashFlowDB = Nothing
Exit Sub

cmdImport_Click_err:
bugAlert True, ""
Resume cmdImport_Click_xit
End Sub
----------------------------------------------------------------------

 

[PeteCresswell]

Messy business...

Oh yeah... one mitigating factor for the .MDB: We need a repository
of data that only gets imported once (or, maybe very rarely). Yes,
it could be an invisible worksheet in the .XLS, but now it seems like
we're trying to do database things with an Excel sheet.... and I've
seen too much of that...

 

[Guest]

Real life is like that...

There is also a command that lets you run forms (.OpenForm)
and another command that lets you run code (like cmd_button_press)
in the form, but I haven't got it in front of me.

(david)

 

[Kikue Hata]

Real life is like that...

There is also a command that lets you run forms (.OpenForm)
and another command that lets you run code (like cmd_button_press)
in the form, but I haven't got it in front of me.

(david)

 

[êùóôás ãåùñãáôïs]

Êáëçìåñá! ðïéïs îåñåé Åëëçíéêá or putin Romanest?
Buna ziua! tsine stii Rumanest ç ëéãá Åëëçíéêá;