+ Reply to Thread
Results 1 to 21 of 21

Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Thumbs down Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    I seem to run into some nasty glitches (see http://www.excelforum.com/excel-prog...to-appear.html ) but this one takes the cake.

    Suddenly Excel wont allow
    Please Login or Register  to view this content.
    ! I first noticed this happening when a function I have used for years, suddenly hit an error on this line.

    It's not the function because a) it works fine on other machines and because b) if I open a new workbook and type
    Please Login or Register  to view this content.
    into the Immediate Window, it still triggers a 1004 error.

    Has anybody else run into this problem before? Was there any way to fix this (short of a full re-install of Excel!)?
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Whet happens when you just try this code?

    Please Login or Register  to view this content.
    Cheers!
    Deep Dave

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    If you open Excel in safe mode and then try it, does it work?
    Remember what the dormouse said
    Feed your head

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Hi Rory, I confirm that the 1004 err still occurs when running Excel in safe mode

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by NeedForExcel View Post
    Whet happens when you just try this code?

    Please Login or Register  to view this content.
    How would that be different to running that line in the Immediate Window?

    OK testing now. Yes I still get error message 1004 "Method 'Add' of object 'Workbooks' failed"

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Check that there are no references (Tools > References) in the Visual Basic Editor that are ticked and start with the text MISSING

    If there are, deselect them and re-select them from the Available References list.
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by Trebor76 View Post
    Check that there are no references (Tools > References) in the Visual Basic Editor that are ticked and start with the text MISSING

    If there are, deselect them and re-select them from the Available References list.
    Thanks for the suggestion. However there are no references starting with MISSING

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    I removed the registry keys for VBA & Excel and then opened excel again. Problems are still happening (e.g. If I open a new workbook and click the Macro Recorder I get an "Unable to Record" message)

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    May be Re-installing MS-Office can fix the issue


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by :) Sixthsense :) View Post
    May be Re-installing MS-Office can fix the issue
    Thanks Sixthsense. It may come to that, but IT want to see if there is another solution first (as per my OP - 'any way to fix this short of a reinstall of Excel')

  11. #11
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Do you actually turn off your machine at night? Perhaps your cache is so full there's no memory left to execute any commands.

  12. #12
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    I presume since you think a reinstall is on the cards that rebooting and testing it in a brand new excel file still fails, even if you have all your add-ins turned off?

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by scottiex View Post
    I presume since you think a reinstall is on the cards that rebooting and testing it in a brand new excel file still fails, even if you have all your add-ins turned off?
    Yes I have tested in a new workbook with all AddIns (XLAM & COM) disabled. I still have the issue

  14. #14
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Yes I have tested in a new workbook with all AddIns (XLAM & COM) disabled. I still have the issue
    And you have rebooted your machine?

  15. #15
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by Trebor76 View Post
    Do you actually turn off your machine at night? Perhaps your cache is so full there's no memory left to execute any commands.
    The machine I am having this problem on is a terminal server. So while I log off my account, the server is still running.

    After reading your suggestion, I'm reading up on Excel cache. I'm going to clear it and see if that has any effect. (I've already tried deleting Excel & VBA registry keys and my XLSB file)

    UPDATE: I've deleted my entire cache folder and I'm still having the problem
    Last edited by mc84excel; 07-14-2015 at 07:26 PM.

  16. #16
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Maybe your account profile is corrupt? Maybe worth asking IT to schedule a re-boot of the server when everyone has logged out say at 2:00am?

  17. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by mc84excel View Post
    The machine I am having this problem on is a terminal server.
    You might have led with that information...

  18. #18
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    The machine I am having this problem on is a terminal server.
    You might have led with that information...
    Absolutely!!

  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by romperstomper View Post
    You might have led with that information...
    Sorry Rory, I didn't think that it would make any difference so I didn't mention it.

    (Would it make any difference?? Personally I've never noticed any differences between Excel 2010 running on laptop/PC/server!)

  20. #20
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Quote Originally Posted by Trebor76 View Post
    Maybe your account profile is corrupt? Maybe worth asking IT to schedule a re-boot of the server when everyone has logged out say at 2:00am?
    Hi Trebor. Sorry for the delay in replying, I've been away.

    Rebooting didn't fix it. However the problem recently suddenly stopped occurring. Not sure why.

    Thank you for all suggestions you made. Appreciated your help.

  21. #21
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Obscure VBA glitch/bug - Workbooks.Add triggers 1004 error?!

    Rebooting didn't fix it. However the problem recently suddenly stopped occurring. Not sure why.
    Seems more than a coincidence??

    Anyway as long as it's now resolved that's the main thing I suppose.

    Robert

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Workbooks Loop Run-time error '1004':
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2015, 11:55 AM
  2. Run Time Error -'1004' - while copying data between workbooks
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2014, 03:23 AM
  3. [SOLVED] Error 1004 when creating new workbooks
    By Jedit in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-22-2013, 06:00 AM
  4. changing cell triggers Run-time error 1004
    By Langston in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2009, 11:36 AM
  5. Unexpected error 1004 when using workbooks.open
    By AHD in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2005, 07:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1