macro for checking if file exists

**sieger007** · 05-25-2011, 05:26 PM

Hi Folks
I have an Excel Macro that looks for 4 files and modifies them like this :

Code:

Workbooks.Open Filename=F1
#  progam code here
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename=F2
#  progam code here
ActiveWorkbook.Save
ActiveWorkbook.Close

Problem is that if F1 is NOT present , it ends with error.I'd like to do something so that , whether F1 is found or not it will STILL check for F2 ..F4 . Same stuff for F2..F4
Finally I would like to Rename the file with this naming convention
Rename F1xls to YYYYMMDD_F1.xls
then invoke a zip program ( Its already there in my directory called zip.exe and i know the command line arguments to it ) to zip each file.
Thank You ,so so ,much

**jaslake** · 05-25-2011, 06:18 PM

Hi sieger007
See this link to check for existence of file or folder http://www.excelguru.ca/node/30
Or you could try

On Error Resume Next
'Your Code
On Error Goto 0

**shg** · 05-25-2011, 06:30 PM

Maybe like this:

Sub x()
    Const sPath   As String = "C:\myPath\"    ' note trailing "\"
    Dim vsFile    As Variant

    For Each vsFile In Array("F1.xls", "F2.xls", "F3.xls")
        If Len(Dir(vsFile)) Then
            With Workbooks.Open(Filename:=sPath & vsFile)

                '  code here

                .Close SaveChanges:=True
                Name sPath & vsFile As sPath & Format(Date, "yyyymmdd_") & vsFile
            End With
        End If
    Next vsFile
End Sub

Thread: macro for checking if file exists

Thread Tools

Display

macro for checking if file exists

Re: macro for checking if file exists

Re: macro for checking if file exists

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions