none
Can't open Excel File manually after MS Project has opened it via VBA RRS feed

  • Question

  • I was testing a section of code I got from a forum that opens a Excel Workbook via MS Project VBA so that I could use it to update a Excel Workbook that I have. The macro opens the Workbook as intended but the main problem I'm encountering is that after I've run the macro, saved and closed the Workbook again, I'm unable to open it manually either from Recents or directly from folder location. I'm using Project 2010 (non Server) and Excel 2010 - Windows 7.

    I'm also having trouble getting it to select "Sheet2" of the Workbook when it opens so I've Commented it out, but it would be a bonus if someone could shed some light on that one as well.

    Edit: I worked out how to select the Sheet...I've updated the code Below. 

    Sub OpenTest()
        Dim xlApp As Excel.Application
        Dim xlWkb As Excel.Workbook
        Set xlApp = CreateObject("Excel.Application")    
        Set xlWkb = xlApp.Workbooks.Open_ ("C:\Users\myName\Desktop\Export Test1.xlsx")
        xlApp.Visible = True
        xlApp.Sheets("Sheet2").Select
        Set xlWkb = Nothing
        End Sub

    Thanks!

    Matt


    • Edited by Fatt Matt Tuesday, September 30, 2014 8:20 AM
    Tuesday, September 30, 2014 3:35 AM

Answers

  • Matt,

    Actually your macro code works fine and I can re-open the file without problem.

    I don't recall asking in your other post but do you have SP2 installed for Project 2010? And, do you have SP2 installed for Office 2010, which will cover Excel? They are separate updates. It would also be a good idea to install the June 2013 cumulative update for Project 2010 as it was released after SP2 and it does fix some VBA issues as I recall.

    As I indicated, your code works fine on my PC which is also Project 2010, Office 2010 and Windows 7. Try this. After you run the macro and then save and close the workbook, does Excel also get closed automatically? If it does, (and it should), after you close the workbook, take a look at the applications manager to see which Windows applications are currently running. Does Excel still show up?

    John

    • Marked as answer by Fatt Matt Wednesday, October 1, 2014 8:57 AM
    Tuesday, September 30, 2014 4:47 PM

All replies

  • Hi Matt,

    Have you tried to close the workbook from your VBA code and see if that would help?

    http://www.exceltip.com/files-workbook-and-worksheets-in-vba/close-a-workbook-using-vba-in-microsoft-excel.html

    Hope this helps

    Paul

    Tuesday, September 30, 2014 1:13 PM
  • Matt,

    Actually your macro code works fine and I can re-open the file without problem.

    I don't recall asking in your other post but do you have SP2 installed for Project 2010? And, do you have SP2 installed for Office 2010, which will cover Excel? They are separate updates. It would also be a good idea to install the June 2013 cumulative update for Project 2010 as it was released after SP2 and it does fix some VBA issues as I recall.

    As I indicated, your code works fine on my PC which is also Project 2010, Office 2010 and Windows 7. Try this. After you run the macro and then save and close the workbook, does Excel also get closed automatically? If it does, (and it should), after you close the workbook, take a look at the applications manager to see which Windows applications are currently running. Does Excel still show up?

    John

    • Marked as answer by Fatt Matt Wednesday, October 1, 2014 8:57 AM
    Tuesday, September 30, 2014 4:47 PM
  • Hi Guys,

    It seems to have corrected itself, as I've tried to replicate it today but it wont break (which is a good thing), perhaps it was from a hidden popup. *shamed* I did check Task manager yesterday when it happened but saw no active Excel apps running.

    @ John , I'm still on SP1 so i'll have to chase I.T. up for SP2.

    thanks again!

    Wednesday, October 1, 2014 8:55 AM
  • Fatt Matt,

    You're welcome and thanks for the feedback. I've found that sometimes while doing extensive editing, troubleshooting and testing with VBA, the system will get into a "confused state" and exhibit strange behavior. When doing that type of work it's a good idea to periodically save and then close Project, and Excel if applicable. Then re-open and start working again. And in some cases, you might even have to restart to "clean the cobwebs" out of Windows, so to speak.

    And when I'm doing edits to a macro I'm developing, I always save the Global before making any test runs. That of course assumes the macro resides in the Global.

    When you talk to TI don't just limit the update to SP2 for both Project and Office. You should also have them install the June 2013 cumulative update for Project.

    John

    Wednesday, October 1, 2014 3:17 PM