Need Help on opening Excel File when Database is executed. 4

[deevaetodin]

Hi;

Was wondering if I could get some help. I need a particular excel file to launch automatically as soon as the database is opened, minimized, and to stay open for the entire session. As well it would be nice to have it closed automatically when the database is closed as well.

Is there a simple macro that I could use or do I need to use code. If code could you explain where to put this code and do I need to run it from a macro.

I am pretty green at this so a detailed explanation would be very much appreciated.

Thanks for all help in advance.

 

[Databaseguy]

1. Create a new Module. Name it whatever you want. In the module, cut & paste the following code:

Public Sub OpenExcelAp ()
Dim objExcel As Object
Dim file as string
File = Your path & File Name"
Set objExcel = New Excel.Application
With objExcel
'Open the file
.Workbooks.Open FileName:=File
'Make it visible
.Visible = True
.Application.WindowState = xlMaximized
End With

2. Create a new macro. Have in perform the RunCode command. In the RunCode Argument, type OpenExcelApp(). Save the Macro and name it Autoexec.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[deevaetodin]

Thanks so much.

This is great.

 

[deevaetodin]

Tyrone;

I have tried what you mentioned however when I try to open the database I am confronted with the following information in a pop-up.

"The expression you entered has a function name my database can't find"

Any suggestions?

Thanks for all your help.

MJM.

 

[Databaseguy]

Double check the name of the Function you're calling and make sure that it exactly matches the name in the module. Also, don't give the function and the module the same name.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[deevaetodin]

I am assuming the function is OpenExcelAp() and that I am calling it using the Autoexec macro.

The module is named Open Excel First. Within the Autoexec Macro the argument is OpenExcelApp(). I have tried it with the argument being OpenExcelAp() as well. No luck.

Let me know how I am screwing this up.

MJM.

 

[deevaetodin]

Here is the code for the module.

Public Sub OpenExcelAp()
Dim objExcel As Object
Dim file As String
file = "C:\test file.xls"
Set objExcel = New Excel.Application
With objExcel
'Open the file
.Workbooks.Open FileName:=file
'Make it visible
.Visible = True
.Application.WindowState = xlMaximized
End With

Does this help?

Thanks again.

MJM.

 

[jebry]

Hi!

You can't run the code of a procedure with the run code action, it must be a function. Change your Subs to Functions in the above code and it should work.

hth
Jeff Bridgham

 

[deevaetodin]

I think that worked, however I am now presented with another error.

"compile error: User defined type not defined"

Within the code the follwing is highlighted:

Set objExcel = <b>New Excel.Application</b>

What does this now mean?

MJM.

 

[jebry]

Hi again!

It probably means that you don't have a reference set to the Excel object library. Go to your code window and choose Tools from the menu bar and select References. Find the reference to Excel 8.0 object library and check it. That should take care of that problem.

hth
Jeff Bridgham

 

[deevaetodin]

Amazing it is working.

One more request. Is there anyway of changing the focus so that Excel opens and then is minimized leaving the database the immediate focus.

Right now the database opens and then excel will open right on top of it.

This is great.

MJM.....

 

[deevaetodin]

Ah..

Figured it out, changed to minimized in the code.

Perfect....

MJM.

 

[jebry]

Hi!

Find the following line:

.Application.WindowState = xlMaximized

And change xlMaximized to xlMinimized

hth
Jeff Bridgham

 

[jebry]

LOL

I see we posted the same answer at the same time! We must be good!

Jeff Bridgham