+ Reply to Thread
Results 1 to 7 of 7

Check if wb exists

  1. #1
    Registered User
    Join Date
    01-25-2022
    Location
    Veszprem
    MS-Off Ver
    2013
    Posts
    3

    Check if wb exists

    Hi all!

    I have a little issue here. I'd like to insert a check if method with a warning message into my code, but can't get the desired result.
    I'd like to check if the workbook exits, before it's opened and copied the sheet. If not I need a message and stop the whole process, else go on.
    I've tried the 'if dir method = 0 or "" then --- else' but nothing. Determined the file and path but did nothing, only just placed a cmdbutton to the current sheet without the warning message.
    My code:
    Please Login or Register  to view this content.
    This works fine, but I'd like to add a check method with a warning message.
    Can someone help?
    Thank you.
    Last edited by Vinidis; 01-25-2022 at 05:05 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2301
    Posts
    1,060

    Re: Check if wb exists

    The Dir method will return a file name if the file exists and a blank if not, we can use this to create a small function to get a boolean response. Something like the below:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-25-2022
    Location
    Veszprem
    MS-Off Ver
    2013
    Posts
    3

    Re: Check if wb exists

    Hi CheeseSandwich!

    I think I'm doing something wrong. If I modify my code with your solution the result is always the same message: "File name: xxxx does not exist."
    Where should I insert your code to get the proper result?
    Can you please explain?
    Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2301
    Posts
    1,060

    Re: Check if wb exists

    Is there a space before your file name as the below code will create a file location like:
    "H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\ FileName.xlsx"
    notice the space before the FileName part - is this intentional?

    Should it not be:
    "H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\FileName.xlsx"

    Please Login or Register  to view this content.
    This could be why we are drawing a blank as it can't find the file with the above file path.

  5. #5
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2301
    Posts
    1,060

    Re: Check if wb exists

    If you wanted to incorporate the check then it would look something like the below (untested)

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,922

    Cool Hi, try this


    A VBA demonstration for starters :

    PHP Code: 
    Sub Demo1()
      Const 
    ".xlsx""H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\"
        Dim F$
            F = Workbooks("
    Moulding check.xlsm").ActiveSheet.[C2&" "&N2]
            If Dir(P & F & E) = "" Then MsgBox "
    File not found", 48: Exit Sub
        With Workbooks.Open(P & F & E, 0)
            .Sheets(F).Copy ThisWorkbook.Sheets("
    Meretek")
            .Close False
        End With
            Kill P & F & E
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  7. #7
    Registered User
    Join Date
    01-25-2022
    Location
    Veszprem
    MS-Off Ver
    2013
    Posts
    3

    Re: Check if wb exists

    Yes, it was a mistake, but in this case did not matter because I did the same typo when the sheet was saved. So the file name had that space too.

    After trying your combined code it works.
    I modified the first filename like you did (and refer that after the .open method too), maybe that was the problem.

    Your solution works fine, thank you.

+ 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] Macro to check if sheet name exits
    By robrobet in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-06-2015, 01:00 PM
  2. [SOLVED] if sheet2 not exits then creat one
    By asdzxc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2013, 02:27 AM
  3. if sheet2 not exits then creat one
    By asdzxc in forum Excel General
    Replies: 1
    Last Post: 09-21-2013, 11:47 PM
  4. Reprotect after macro exits.
    By D1TrueGod in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2008, 12:34 AM
  5. Excel exits without prompts when tabbing. Please HELP!
    By pavlouc in forum Excel General
    Replies: 0
    Last Post: 07-31-2007, 11:27 AM
  6. Sub Exits Unexpectedly
    By Walker in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2005, 06:06 PM
  7. way to run sub after user exits textbox
    By funkymonkUK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2005, 09:06 AM

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