Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA Open Workbook Macro hangs

  1. #1
    Join Date
    6th January 2011
    Posts
    3

    VBA Open Workbook Macro hangs

    I am trying to open a series of workbooks, copy a location from one already open workbook to the newly opened one and then save and close it. This I can do except opening the workbook cancels the rest of the macro.

    The macro will open the workbook, activate it and then skip to the end of the macro without executing any other commands. No error message is triggered. Yet, when stepping through the macro in debug mode everything works as it should. I suppose there is a timing problem, but I can not solve it. I have tried inserting a MsgBox right after the open workbook statement, but it never gets displayed - except in debug mode. I have tried nested For Next loops to put in a delay, but they never are started. I have tried opening the workbook directly

    VB:
    Workbooks.Open Filename:= _ 
    "C:\Users\Peter\Documents\Scallop\2010\EPA Study 2010\2010 Sept Survey\25C.xlsm" 
    
    
    and I have tried using a variable

    VB:
     ' open the workbook
    Set wb = Workbooks.Open("C:\Users\Peter\Documents\Scallop\2010\EPA Study 2010\2010 Sept Survey\1B.xlsx") 
     ' then activate it
    wb.Activate 
    
    
    Both work in debug mode when stepping through, and neither work when run at speed. It does not matter if macros are enabled in the workbook or nor. Nor does it matter if the workbook is in 2003 format (.xls).

    I have tried putting a workbook_open event, but it does not get triggered, even though the workbook is open, activated and behaves normally.

    In other words, the following macro does not work except when I step through one line at a time.

    VB:
    Sub OpenTest1() 
         '
         ' OpenTest1 Macro
         '
         ' Keyboard Shortcut: Ctrl+Shift+O
         '
        Workbooks.Open Filename:= _ 
        "C:\Users\Peter\Documents\Scallop\2010\EPA Study 2010\2010 Sept Survey\1B.xlsm" 
         
        Range("E9").Select 
         
    End Sub 
    
    
    Any suggestions would be appreciated. I find this weird and have run out of things to try. I found
    this same question on one other site, but no satisfactory answer was provided. Thanks.
    I am using Excel 2010 and Windows 7.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd August 2010
    Location
    Union City, Pa - USA
    Posts
    607

    Re: VBA Open Workbook Macro hangs

    Did you literally type in your own workbook_Activate event or did you select it from the "This Workbook" declarations? I dont see why you code doesnt work. Maybe add.....

    VB:
     
    Dim wb As Workbook 
     
    Set wb = Workbooks.Open("C:\Users\Peter\Documents\Scallop\2010\EPA Study 2010\2010 Sept Survey\1B.xlsx") 
     ' then activate it
    wb.Activate 
    
    
    or have you just omitted this part?
    The Only Dumb Question Is A Question Not Asked.

  3. #3
    Join Date
    6th January 2011
    Posts
    3

    Re: VBA Open Workbook Macro hangs

    Quote Originally Posted by yegarboy View Post
    Did you literally type in your own workbook_Activate event or did you select it from the "This Workbook" declarations? I dont see why you code doesnt work. Maybe add.....

    VB:
     
    Dim wb As Workbook 
     
    Set wb = Workbooks.Open("C:\Users\Peter\Documents\Scallop\2010\EPA Study 2010\2010 Sept Survey\1B.xlsx") 
     ' then activate it
    wb.Activate 
    
    
    or have you just omitted this part?
    The code works in debug mode without the dimension statement, and adding it does not help when running the macro in regular mode.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,941

    Re: VBA Open Workbook Macro hangs

    Long shot - try assigning another keyboard shortcut key without using Shift...

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th January 2011
    Posts
    3

    Re: VBA Open Workbook Macro hangs

    Quote Originally Posted by cytop View Post
    Long shot - try assigning another keyboard shortcut key without using Shift...
    Hey, sometimes long shots work! Many thanks to cytop.
    So, for my configuration, if I want to open a workbook, I have to run the macro with a Cntrl-letter, not a Cntrl-Shift-letter shortcut. I have not tried assigning the macro to a button.

    This problem is solved.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st September 2010
    Posts
    7,941

    Re: VBA Open Workbook Macro hangs

    Not using the Shift key in the shortcut might have sounded like a daft suggestion, but if the code executes while the key is still pressed...

    An interesting consequence of this is that, if Excel is running in the background and opening a workbook and you are running another program in the foreground; if you hold the (Left only, I think) shift key down - say typing a block of text in upper case, the same will happen. Excel monitors the key state on a system wide basis...

    This is reported as a bug in Excel 2000 - 2003, and was supposed to be fixed. Hmmm.

    The Wonderful World of Microsoft!
    Last edited by cytop; January 7th, 2011 at 20:54. Reason: Typos

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: May 25th, 2010, 13:07
  2. Workbook Hangs When Opened With Macros Enabled
    By RB123 in forum EXCEL HELP
    Replies: 3
    Last Post: January 30th, 2008, 06:41
  3. Macro Hangs After Opening New Workbook
    By Reged in forum EXCEL HELP
    Replies: 6
    Last Post: October 26th, 2007, 09:59
  4. Macro hangs after execution
    By sheetal in forum EXCEL HELP
    Replies: 3
    Last Post: July 11th, 2006, 17:19
  5. Replies: 16
    Last Post: September 29th, 2005, 23:13

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno