Solved

VBA Excel 2000 - File existing or not

Posted on 2011-03-25
3
437 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:Kannan K
Kannan K 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

789 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