Outlook vba test for excel already open

[danomaniac]

I have an Outlook macro that opens an excel workbook when an email arrives to a specific folder - but if excel is already open it opens another instance - also if the desired workbook is already opened, it opens a read-only copy.

This is what I have:

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

Set xlApp = New Excel.Application
Set wb = GetObject("sampleworkbook.xls")

How do I:

If excel isn't open, open it and open the workbook.
If excel is open, but the workbook isn't, open the workbook.
if excel is open, and the workbook is open, activate the workbook?

Thanks.


"It's more like it is now, than it ever has been."

 

[TonyJollans]

Something like this:

[blue]On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then ' Err.Number <> 0 Then
    Set xlApp = CreateObject("Excel.Application")
End If
On Error Resume Next
Set wb = xlApp.workbooks("sampleworkbook.xls")
On Error GoTo 0
If wb Is Nothing Then
    Set wb = xlApp.workbooks.Open("sampleworkbook.xls")
End If[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[danomaniac]

Thanks Tony -
Worked perfectly -

"It's more like it is now, than it ever has been."

 

[fumei]

Just out of curiosity, and with the hope of learning something myself, why do I never see any code using Tasks for this? Since 2002, Office applications have access to Tasks, like this:

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

If Tasks.Exists("Microsoft Excel") = True Then
   Set xlApp = GetObject(, "Excel.Application")
Else
    Set xlApp = CreateObject("Excel.Application")
End If

Tasks are "tasks" running on the system, and include a surprising amount of things.

If Tasks.Exists("Calculator") = True Then
    Tasks("Calculator").WindowState = wdWindowStateNormal
End If

for example checks to see if Calculator is running, and if it is, brings its window state to Normal.

Or:

Dim aTask As Task
For Each aTask In Tasks()
    If aTask = "C:\TestData" Then
        aTask.Visible = True
    End If
Next

This checks to see if there is a Windows Explorer tasks AND that the current folder in Explorer is "c:\testdata". If there is, it makes Explorer visible.

Note that Explorer Tasks are named by the folder. Regardless of what you do in Explorer, it always has a current folder. Even if it is the default "My Documents".

Tasks (as objects) have useful properties and methods. You can move it, resize it, or send it window messages.

An example from Help.

Dim taskLoop As Task

For Each taskLoop In Tasks
    If InStr(taskLoop.Name, "Notepad") > 0 Then
        taskLoop.Activate
        taskLoop.SendWindowMessage &h111, 11, 0
    End If
Next taskLoop

Anyway, in all the threads I have seen about getting/setting other applications, I do not think I have ever seen anyone use Tasks. To the real gurus (PHV, Tony, Skip et al), is there something not quite right with using Tasks?

faq219-2884

Gerry
My paintings and sculpture

 

[TonyJollans]

Habit!

I have never really looked at Tasks. I will now. Thanks, Gerry.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[PHV]

When dealing with automation the Tasks collection is not so useful, IMO.

 

[fumei]

You may want to take a look at what Windows considers Tasks.

Dim aTask As Task
For Each aTask in Tasks
   Selection.TypeText Text:=aTask & vbCrLf
Next

Assuming (of course!) you are in Word. Here is what is typed. I have IE open to this thread page, and a blank Word document, running the code above from the VBE (thus, I think, the first Task - rather logically - is "Run Sub").

Run Sub/UserForm
TF_FloatingLangBar_WndTitle
CiceroUIWndFrame
AutoSuggest Drop-Down
Start Menu
SysFader
VirusScan On-Access Scan Messages
Network Associates Error Reporting Service
NetDDE Agent
Insert
Insert
Insert
Insert
Microsoft Visual Basic - Document2 [running]
Toolbar 32779
Toolbar 32779
Toolbar 32779
Toolbar 32779
File
File
File
File
Document2 - Microsoft Word
VBA Visual Basic for Applications (Microsoft) - Outlook vba test for excel already open - Windows Internet Explorer
Document2 - Microsoft Word
Acrobat IEHelper
VBEOnKey-ID
Properties
DDE Server Window
DDE Server Window
JavaUpdate SysTray Icon
Update Manager Control Panel
AXWIN Frame Window
FireTray_Hidden_Window
McAfee ClientLib Prefs Communication
Corel Media Indexer
Connections Tray
McAfeeFrameworkIcons
McTray
McAfee AutoUpdate
ePolicy Orchestrator Agent Monitor
VirusScan On-Access Scan Statistics
VirusScan On-Access Scan Statistics - BCK1356284E
QTPlayer Tray Icon
MediaCenter
Agere Systems Soft Modem Monitor
MS_WebcheckMonitor
Power Meter
NVSVCPMMWindowClass
McAfee Fire IKE and Preferences Server
McAfee Fire Prefs Communication
McAfeeHookAndProcessWorker
McAfeeFireWorker
McAfee Fire GUI-Server Communication
PGPnetKernelWorker
McAfeeEnterceptWorker
GDI+ Window
GDI+ Window
Program Manager
Microsoft Visual Basic - Document2 [running]
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
M
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
Default IME
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
Default IME
Default IME
Default IME
Microsoft Visual Basic - Document2 [running]
Default IME
Default IME
Default IME
Default IME
Default IME
Default IME
Default IME
Default IME
Default IME



Some of these can of course be seen in Processes, under Task Manager. But i have no bloody idea what:

Insert
Insert
Insert
Insert

OR

Toolbar 32779
Toolbar 32779
Toolbar 32779
Toolbar 32779
File
File
File
File

mean as "Tasks".

Shrug.

However, as a means to get an instance of Excel (for example) I use Tasks. I just do not know if this is essentially moot, regarding whether it is any "better" or "worse" (efficient vs inefficient???) than error trapping the result of GetObject.

Ooops, just scanned up and saw PHV's post. OK, you are most certainly a source I will listen to. Why?

Oh....never mind. Because it does go through all that other crap.

And there ya go.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

OK, kill that idea.

faq219-2884

Gerry
My paintings and sculpture