+ Reply to Thread
Results 1 to 20 of 20

VBA to ignore a specific workbook

  1. #1
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    VBA to ignore a specific workbook

    Hi All,

    Im having a bit of trouble with capturing data from multiple workbook sources. In the code below, I'm pulling data from Sheet2 in workbooks located in a different directory than the workbook that the macro is stored in. This works fine, however there are some workbooks in that directory that will have the same sheet name that I want to ignore when the macro runs. Is there a way to ignore specific workbooks?

    When I add
    Please Login or Register  to view this content.
    to the
    Please Login or Register  to view this content.
    line I get an error.

    Any help would be greatly appreciated, thanks!

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,759

    Re: VBA to ignore a specific workbook

    I would change the line

    Please Login or Register  to view this content.
    to an if statement

    Please Login or Register  to view this content.
    and insert below the DoWhile Statement.

    Then at the end of the instructions before the Loop command add an End if

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to ignore a specific workbook

    Quote Originally Posted by alansidman View Post
    I would change the line

    Please Login or Register  to view this content.
    to an if statement

    Please Login or Register  to view this content.
    and insert below the DoWhile Statement.

    Then at the end of the instructions before the Loop command add an End if

    Alan
    Hello Allen, thanks for the reply!

    When I do that I get a Run Time Error '91', Object variable or With block variable not set.
    Not sure why though, what do you think?

    Thanks

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,759

    Re: VBA to ignore a specific workbook

    Post your new code. Sounds like you may be missing the End if statement.

    What line in the code is highlighted when you select Debug?

  5. #5
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to ignore a specific workbook

    Alan,
    Here is the code:

    Please Login or Register  to view this content.
    This is the highlighted line:
    Please Login or Register  to view this content.
    Thanks!

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    Hi Pierce

    Try this
    Please Login or Register  to view this content.
    I believe your End If needs to be moved
    Please Login or Register  to view this content.
    Last edited by jaslake; 01-10-2014 at 02:22 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  7. #7
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to ignore a specific workbook

    Quote Originally Posted by jaslake View Post
    Hi Pierce

    Try this
    Please Login or Register  to view this content.
    I believe your End If needs to be moved
    Please Login or Register  to view this content.
    Hello John, thanks for the reply!

    When I use that instead, it freezes excel once the merge reaches that workbook. Why do you think this is?

    Thanks!
    Last edited by Pierce Quality; 01-10-2014 at 02:41 PM.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    This Code performs as expected in my test environment...
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to ignore a specific workbook

    Quote Originally Posted by jaslake View Post
    This Code performs as expected in my test environment...
    Please Login or Register  to view this content.
    Thanks John, this does work, I must have not moved the 'End If" when I ran it previously.

    Marking this as solved, thanks again!

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    You're welcome...glad I could help. Thanks for the Rep.

    In the future, please don't quote entire Posts...quote only relevant parts.

  11. #11
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to ignore a specific workbook

    Quote Originally Posted by jaslake View Post
    You're welcome...glad I could help. Thanks for the Rep.

    In the future, please don't quote entire Posts...quote only relevant parts.
    Thanks John, will do!

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to ignore a specific workbook

    Will somebody fix the spelling? Ingore s/b Ignore
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    It's pronounced "In Gorr eee"

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to ignore a specific workbook

    My Gorry - I didn't know that!

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    Ah well...just learned it myself today...when I tested got a "Subscript Out Of Range" when I spelled it incorrectly (Ignore)...worked when I spelled it correctly (Ingore)...learn something new every day...it's a wonderful life.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to ignore a specific workbook

    it's a wonderful life.
    That it is - if you don't weaken

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    At my age the mantra is
    'ya gotta keep movin'
    We'll probably both get hammered for this "Off Topic" conversation...

  18. #18
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,759

    Re: VBA to ignore a specific workbook

    At my age the mantra is
    'ya gotta keep movin'
    We'll probably both get hammered for this "Off Topic" conversation...
    At my age, I need to learn how to spell

  19. #19
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to ignore a specific workbook

    Yeah, well, me too. That's why God made Spell Checkers such as ISpell...though, it's not "Fool Proof"...it's tripped up this Fool more than once.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to ignore a specific workbook

    I've always been gud at speling

+ 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. [SOLVED] Ignore MsgBox in a referenced Workbook
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-14-2014, 12:16 PM
  2. Macro script to ignore open workbook
    By Pantego in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2011, 03:51 PM
  3. VB code help, ignore blanks/specific
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2011, 04:38 PM
  4. ignore cells containing specific words
    By Tony Vargo in forum Excel General
    Replies: 2
    Last Post: 08-07-2008, 08:01 AM
  5. Replies: 3
    Last Post: 06-06-2006, 09:55 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