Check path before opening workbook in excel vb

Posted on 2009-02-23
Last Modified: 2013-11-25
I have the following code  below which opens a workbook in excel, however, i was wondering if there was a way to check if the path is valid before trying to open it up and getting an error. I don't want to use an err.number solution. I also put a pic of the error that shows when it tries to open a workbook that doesn't exist.

Workbooks.Open D.[B2].Text, never
Question by:tchristie33
    LVL 4

    Accepted Solution

    Use FileSystemObject!

    Google or F1 for info...

    Sub Test
       'code probably erranous...
       dim fs as new FilesystemObject
       if fs.FolderExists("c:\My\Folder") then
          msgbox "Exists!"
          msgbox "Exists - not!"
       end if
    end sub

    Open in new window

    LVL 14

    Expert Comment

    whay you don't want to use an err.number solution ?
    LVL 4

    Expert Comment


    Author Comment

    i just don't like to code that way i guess, figure there has to be a better way to do it.
    LVL 76

    Expert Comment

    If Dir$("C:\MyPath\MyFile.Xls") <> "" then
       Set Wbk = WorkBooks.Open("C:\MyPath\MyFile.Xls")
    LVL 4

    Expert Comment

    The code i sent will not work in VBScript. Please see

    for how to use FileSystemObject in VBS.

    (in vbs you gotta use CreateObject() )

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now