VBA in MS project to check if workbook open?

[gtswat]

Hi, I am new in this forum and also new in VBA.
I am trying to code in MS project, linking the tasks from project to excel. I stuck at how to decide if there is a specified excel workbook active or not. eg.if the workbook name "Data.xlsm" when I run the code in MS Project, Data.xlsm will automatic open if it is not activate, otherwise keep the file open.

I searched a lot online, I got several answers which worked when I coded in Excel, but if I use the same code in MS Project, it did not work.

Anyone knows how to code in Project? Thanks in advance!

Please Login or Register  to view this content.

[Leith Ross]

Hello ,

The big problem here is that you have two applications running in separate threads. Since Windows runs each thread in its own protected memory area, you can not directly access the other application's objects. You can access them if you create or start the application from the MS Project code.

This macro will check all the top level Windows (applications) that are running. It will match the caption of the window you are looking for and return true if found or false if not found. Copy this code into a separate VBA module in your project. You can then call it from anywhere else in your code.

A word of caution: If you have more than one workbook open in a single instance of Excel, the macro will fail if the workbook is not the active workbook. If you need to know if a specific workbook if open, let me know. I have code for that also.

Please Login or Register  to view this content.

Example

Please Login or Register  to view this content.

[gtswat]

Hi Leith, Thank you sooo much for the prompt replay!!
I am pretty new to the VBA, I am trying to understand what your said but it is really hard....
May I ask some questions regarding your reply?
1. What do you mean separate threads?
2. If I copy your code to a new module, How can I call that module?
3. Which code is represent for the excel file name? (Your code is really hard for me to understand, but I will search for it.)
Thanks so much!!

[Leith Ross]

Hello gtswat,

I need to provide you with a couple of definitions before I can answer your questions.

VBA itself is a Process. A process can contain many threads. A process owns resources allocated by the operating system like memory, file handles, device and windows. Typically processes do not share the their memory space or file resources with other processes.

Threads are subset of a process. A thread is the smallest code routine that be scheduled by the operating system to run. A thread can run independently of and concurrently with other portions of the program. A Sub or Function procedure would be an example of a VBA thread.


Answer 1:
Because you are running MS Project and Excel, there are two separate processes each with different threads. You can not directly access objects, variables and constants in MS Project from Excel and vice-versa because they don't share the same memory space.

What you can do is check which processes or applications are running using the Windows API (Applications Programming Interface). That is code that is hard to read and makes no sense. The API is low level code that provides access to operating system directly and many of its critical sub systems.

Because each application runs in a main Window, you can check the caption or window title of the application for one you are searching for. In this case you are searching for an Excel workbook name. The function returns a true value if either a partial or full match was made or a false value if no match was found.

Answer 2:
You do not call the module, just the function inside the module. The purpose of placing code inside a module is to make it available to all other parts of your VBA project.

Answer 3:
I have modified your test macro to call the function to test if the workbook is open or not. Remeber this checks for the workbook's name not the worksheet. The code would need to be modified if you want to check for a worksheet name.

Please Login or Register  to view this content.

[gtswat]

Hi Leith, In order to ask you the basic stupid questions, I did a lot of search, instead of coding in MS project, I coded in Excel, but refernced the project library. I just done the whole codes, it works pretty good so far.

Thanks so much for the information, even though some of them I still not understand. My VBA book will arrive this week, and I will begin with the basic stuff and grammar.