Opening, Closing, and Opening Excel workbooks from MS Project

**m007schneider** · 04-02-2013, 04:44 PM

I am running VBA code in MS Project 2010 where I filter by resource, collect information on open tasks, and place into a spreadsheet (report) for the resource. My process is to open up a new excel workbook and save as with the resources name in the file name. Place the necessary data into the workbook, format, save the file, close out of MS Excel all together, move on to the next resource and start the process all over again (a loop).

Everything works perfect for the first spreadsheet. But when I get to the second spreadsheet, it opens and is saved with the 2nd resources name, but the first attempt to select a cell fails with the error Run-time erro '1004': Method 'Range' of object '_Global' failed.

I've added 'wait' loops to slow things down, but have no idea what is going on.

I've attached the bas files if that will help.

Any help appreciated.

Thanks in advance.Project_VBA_BAS_Files.zip

**m007schneider** · 04-03-2013, 08:56 AM

Any ideas ??? Anyone ? Is the application losing focus somehow ? I've tried re-activating to regain focus.

Need help.

Thanks in advance.

MS

**m007schneider** · 04-04-2013, 03:06 PM

Well, no one seems to want to respond so, given enough (painful) time of trying a plethora of different strategies to resolve the problem, I finally figured it out. I was using "Excel.Range(cell address)" and other "Excel.*" vba program calls that worked fine for the first resource. However, when moving on to the second resource, the program errorred out as described in original post. By substituting the variable reference to the Excel object MyXL in my program calls (as in MyXl.Range("A1") instead of Excel.Range("A1"), the program appears to not lose focus and runs like a champ.

In case anyone else searches and finds this thread while trying to solve a similar problem, I thought I would post my solution.

MS

**Norie** · 04-04-2013, 03:22 PM

I've only just seen this thread.

Is everything now working as expected?

I ask because there's a few things in the code that should be avoided, especially when automating Excel from another application.

The main thing is the use of ActiveCell/Select/Selection etc.

For example this,

Please Login or Register  to view this content.

could be done like this.

Please Login or Register  to view this content.

Instead of MyBook.ActiveSheet you could use MySheet, which is declared earlier in your code, but you would need to set it to reference the relevant worksheet.

Anyway, your code might work just fine as it is but it might be worth updating it.

**m007schneider** · 04-05-2013, 07:51 AM

thanks for the feedback. Yes, everything appears to be working as expected. I did attempt to use the With / End With where possible, but was getting an object error. Since I have fixed the code, I could go back and fix as described above. Thanks again for the follow up.