Solved

Activate a running Excel File from Outlook in VBA

Posted on 2009-05-06
6
521 Views
Last Modified: 2012-05-06
Hi,

I would like to know how I can activate and use macros which are in a excel file already running.

The idea is to generate a event when a mail arrived in my outlook. I filter the subject and call a procedure if it meats a certain criteria.

I know how to open new excel instance and open a file but here my excel file is running already and I have several excel application (each one a different process in the task manager) running. In each excel application I have several excel files open.

I want to use something similar to

Set Xlapp = GetObject(, "Excel.application")

But i want to test if the excel file is runiing in that application or in another one. If it's not in that one, i need to test the seconf application. If no excel application has the file open I will open it but i need to make sure it's not open already.

Then once the file is found, I want to run a macro in that file.

Thanks for your help !

0
Comment
Question by:potter09
  • 4
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 24315346
Use GetObject with the workbook path and assign the result to a workbook variable. If the file is already open, you will grab a reference to it; if not, it will be opened.
Regards,
Rory
0
 

Author Comment

by:potter09
ID: 24315423
Would it looks like:

Sub OpenXlFile

  Dim Xlbook as workbook  '/ ojbect which one?
  Dim TargetCell as range

  set Xlbook  = getobject("Y:\GCF\LiveFile.xls","Excel.application")

  TargetCell = Xlbook.sheets(1).range("A1").value

End Sub
0
 

Author Comment

by:potter09
ID: 24315492
I tried with

  set Xlbook  = getobject("Y:\GCF\LiveFile.xls") but I have an Automation error :

"The message filter indicated that the application is busy"

I tried with a C:\Testfile.xls (blank excel file) and it worked so i'm not sure what's going on.

On the Live file, bloomberg is runniing to have a live feed of stock market information, that's it.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:potter09
ID: 24315856
Ok issue solved. It had to do with the server path. I put the complete path instead of the shortcut "Y" and it worked. Thanks rorya !
0
 

Author Comment

by:potter09
ID: 24315883
Jus a question:

there is a password on the file to open. If the file is running there is no problem, but is there a way to put the password if the file is close ?

Thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24316078
I don't think you can supply a password with GetObject, no.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question