Solved

VBA Excel 2000 - File existing or not

Posted on 2011-03-25
3
434 Views
Last Modified: 2012-05-11
Dear Experts,

I have a macro which assign a certain file name to a string variable, based on that after it opens it

Dim FileNev As String
Dim FileNevWorkbook As Workbook
FileNev = "D:\PR\Conv " & FileNap & "." & modMilyenHonap & ".xls"
Set FileNevWorkbook = Workbooks.Open(FileNev)

But sometimes it happens that the file does not exist, and the opening attempt brings error. Is there way by VBA to check whether that file exist and if not then bringing a MsgBox instead of the open?

thanks,
Zsolt
0
Comment
Question by:csehz
3 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 35214398
something like this

hth
Dave
Sub testfile()
    If Len(Dir("D:\PR\Conv " & FileNap & "." & modMilyenHonap & ".xls")) > 0 Then
        Set FileNevWorkbook = Workbooks.Open(FileNev)
    Else
        MsgBox "File not found."
    End If
End Sub

Open in new window

0
 
LVL 5

Assisted Solution

by:Kannan253984
Kannan253984 earned 250 total points
ID: 35214408
Hi,

Please take the below function.

KK,
Public Function FileFolderExists(strFullPath As String) As Boolean
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0

End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:csehz
ID: 35214433
Thanks very much for the two different approaches
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

17 Experts available now in Live!

Get 1:1 Help Now