Open another MS application from Excel 2

[RFeniuk]

What I am trying to do is open Access database from Excel and then have Access run a module to import some of my Excel Data. I need to run it from a button since my users aren't particularily computer literate. In otherword I need the coding to put on a button to run the operations. Can someone help me?

 

[mustangcoupe]

I am trying to do the same thing but I am still having some problems. the following code will open an access database on the click of command button1 access database XYZ and then close. i have not gotten the access to a module to work yet. maybe someone can help us both! but this is a start.

Todd


Private Sub CommandButton1_Click()
'Microsoft Access database object
Dim appAccess As Object
'Create an instance of Microsoft Access
Set appAccess = CreateObject("Access.Application&quot
'Open the database "XYZ.mdb"
appAccess.OpenCurrentDatabase "C:\XYZ.mdb"
'close access
appAccess.Application.Quit acEXIT
Set appAccess = Nothing
End Sub

 

[xlbo]

If your module has code all under one sub - lets say it's called "mySub"
Then
appAccess.doCmd Runcode "mySub"
should run whatever's in that sub

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[mustangcoupe]

Geoff

I have added your code to mine but I get a compiler expected end of statement error. i had tried this earlier in my writing of this code and it dosent seem to like the docmd command or something.
I have created a button on my form to envoke the sub I need to run but with my limited code I cant get that to run either.
appAccess.Forms![ESU form]!cmdhiddenbutton.Click()
that gives me compiler error expected =

expected = what??????

thanks
todd

 

[Zathras]

Expected an equal sign.

VBA is funny that way. If you aren't using the return value of a function, it doesn't want the parentheses. In other words, use either

    nMyVariable = SomeFunction( nParm1, nParm2 )

or

    SomeFunction nParm1, nParm2

In your case, try
[blue]

    appAccess.Forms![ESU form]!cmdhiddenbutton.Click

[/color]

 

[TonyJollans]

Hi All,

For once, I think it's simpler than you are making it. You should just be able to do ...

appAccess.Run "MySub"

Enjoy,
Tony

 

[mustangcoupe]

If I remove the parentheses then the expected = error goes away but i get a runtime error 2465 application defined or object defined error.
and with Tony's change I get a runtime error 2517

??

Todd

 

[TonyJollans]

Hi,

There are two different problems here, and I can't answer either immediately
[ul][li]Using Application.Run works for me with Access 2000, but gives the 2517 error with Access 97. I'm not sure why at the moment[/li][li]The 2465 error means that Access can't find the code, but I am getting confused here. Why are you trying to run code behind a button on a form when you are driving this from another application? Why not just put it in a module? If you are presenting the user with the Access form you shouldn't need to have your own code somewhere else - whatever is on the form will run fine when the user presses the button.[/li]

Am I making sense? Or am I simply failing to understand the blindingly obvious (again)?

Enjoy,
Tony

 

[TonyJollans]

Hi again,

I got the 2517 error in 97 due to my own stupid mistake in copying code from one machine to another. If you haven't made a typo anywhere I don't knoq what else to suggest at the moment.

Enjoy,
Tony

 

[mustangcoupe]

OK Tony, I will start at the beginning. I have an access database (someone else wrote-remember this!) I dont want the user to have to access. Most of our time here is spent in excel and we have automated most of our common tasks either by VB or just using formulas in excel. About three times a day each of us has to open access enter in a list of numbers in a form and print (and export in RTF) the report. So I figured if I could open access enter the numbers in the form (from a list in excel) print and export the report and then exit access with the screen frozen(no update) it would be much faster than waiting for this slow machine to load a app, then me click the buttons ect. SO I load access, then the form (the code above does not show the enter of data) enter the data one number at a time, this is where my problem is. The way the database form is configured you eneter the number in the form and press enter and it updates for you (using the afterupdate procedure) and then the user exports and prints the forms. This way the titles and other info can be predefined by my VB. I cant get the afterupdate to function from in the vB script.

so to put it bluntly I dont want the user to mess up the reports or forget to do one so I figured the VB could do it all for them.

Todd

 

[mustangcoupe]

OK tony you got me thinking... and my button was not the same ..... it was mybutton not cmdmybutton.. so i fixed this and now i get a runtime 438 I guess it just dosent want me to click this button!!

todd

 

[TonyJollans]

Hi Todd,

I don’t know how to press a button on a form in code, so the code has to be invoked directly. Access creates Event Procedures as Private, so they cannot be seen from outside. If you change your Click Procedure to Public you should be able to invoke it. With a very simple Form I have the following code working …

Dim appAccess As Object

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "c:\My Documents\db2.mdb"

appAccess.Docmd.Openform "Form1"
appAccess.Forms!Form1!Field1 = "Some data"

appAccess.Forms!Form1.Button1_Click

appAccess.Quit
Set appAccess = Nothing

Hope it helps.

Enjoy,
Tony

 

[mustangcoupe]

hey one more thought... I didnt realize before but now I do, this machine is running office 2000 but access 97. (I dont know why ill need to ask around) but is there a code diffrence for pushing buttons ect in 97? Your code is basically what I have been trying and it dosent work eithter. But like i said 2000 vs 97?

thanks
TODD

 

[TonyJollans]

It works for me in 97 as well as 2000. The critical factor was making sure the form code was not Private. Public Sub Button1_Click or just Sub Button1_Click both worked, but Private Sub Button1_Click which is what Access generated didn't work.

 

[mustangcoupe]

Tony You deserve a BIG STAR for that one..... without knowing it you lead me to the answer. the diffrence is that I had button1.click and you had button1_click ...
I did not see that yesterday

Thanks a bunch
Todd

 

[mustangcoupe]

Tony,
I have another question for you. On my access report when it loads I get a message box that opens with one text box. The heading says Enter Parmeter Value. Im not sure but I beleive it is access generated I dont see where in the report it calles for this to open. I need to enter info in this text box but I dont know how to address it.

Todd

 

[TonyJollans]

Hi Todd,

You are right that the Input Box is generated by Access. It is because there is a parameter in the SQL behind the Report which it needs to resolve at run time. That was the easy part of the answer! I don't know how to address it in code. Can you design your Report differently? A couple of ideas might be to use a Filter, or to set up a parameter table which you can pre-populate. I sometimes struggle with the Access Object Model but if I find anything I'll report back.

If this is no help, I'm sorry. It might be an idea to start a new thread with a title which might bring some expert in - we have already hi-jacked someone else's thread! Alternatively I'm happy to have a look if it's not too large or too sensitive to e-mail (Tony@Jollans.com).

Tony