Launch Excel via VBA

W

worksfire1

I have a simple piece of code I am starting with, to just open a test.xls
file. It appears to launch excel, but then stops and prompts me to run a
macro. I will not want to run a macro, so what piece of code do I add the
the below code to disable whatever is causing Excel to want to run macros
(that don't exist) when it is opened?

Sub LaunchExcel()
Dim excelWorkbook As Excel.workbook
Set excelWorkbook = GetObject("\\zion\databases\text.xls")
excelWorkbook.Application.Visible = True
excelWorkbook.Applicatin.Windows("test.xls").Visible = True
End Sub
 
K

kingston via AccessMonster.com

Does it prompt you to run a macro or does it prompt you to allow macros to be
run? If it is the latter, then check your Excel file and application
security settings. Another VBA command that might work is:

Application.FollowHyperlink "\\zion\databases\text.xls"
 
W

worksfire1

It's the former. It promts me to run a macro. Which is strange since there
is no macro in the text.xls file I am opening. That is why I figure there
must be some setting that I need to set in my code to keep it from looking
for macros when it opens excel. Any idea how to stop it?
 
K

Klatuu

There are a couple of issues. Because you have dimmed excelWorkBook as an
excel workbook object, you have forced "early binding". This may provide a
small performance advantage, but it will also set you up for some possible
problems. The reason is, you have bound the object model for the version of
excel you have installed. If another user has a different version, it can
cause errors or not run at all. The prefered method is to use "late
binding". That is to dim the object as generic objects. Then, when you set
them as excel objects, the version of Excel loaded on the computer running
the application will be loaded.

Also, you are starting one level too low. You first need to enstantiate the
Excel
Application. It is very import that all objects be fully qualified when
using Automation. If not done correctly, problems can arise. The one that is
common and very hard to figure out the first time is leaving an instance of
Excel running on your computer.

What happens is that if you use an unqualified reference, Access may not
know who the reference belongs. It will then start up another instance of
Excel to use. Now, when you Quit your application, the Quit will affect only
the instance you started, but the one Access started will still be running.
It will not show up in Task Manager Applications, but in the Processes tab.
So if you are testing your app and either it hangs trying to communicate with
Excel or you open Excel and it appears to hang, then look in Task Manager
Processes for an instance of Excel.exe.

Now here is how it is done.

First, here is some code you will need. Put it in a standard module by
itself:
****************************************************
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
*********************************************************

Private xlApp As Object ' Reference to Microsoft Excel.
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
Private blnExcelWasNotRunning As Boolean ' Flag for final release.


'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")

Now it is important to shut it down completely and in the correct order.
The correct order is the reverse of how you defined them.

xlBook.Close
Set xlSheet = Nothing
Set xlBook = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlApp = Nothing

Notice the if blnExcelWasNotRunning = False, that means Excel was already
open on the computer. That means the user had it open doing something so we
don't want stop it.

This is the guts of it and the hard part. All you need to remember from
here is to be sure you fully qualify all your objects. Did I say that twice?
Well then, it must be important.
 
G

Gerry O

Read this post, but was unsure of where the code after the second set of
asterisks goes? Is this a sub, too? Didn't see and "End Sub" line on the
last part of your code. Sorry for my ignorance, but would love to know!
 
G

Gerry O

Further to my last - I am trying to do a similar thing within Excel only
(i.e., if Excel is already open, my file will open in a new instance of
Excel, if not open, then it will open my file). Thanks!
 
G

Gerry O

Please disregard my last two posts. I figured out what I needed to! Sorry
for the multiple posts.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top