If an Excel spreadsheet is not found then...

[mv8167]

I am working on a function that the initial seeting of a ile bath is set to FaxFilePathName = "Empty". Depending on when a specific Test is run, either the FaxFilePathName variable is not "Empty", if "Empty" call Call CheckingFaxFilePathName () that will set the correct FaxFilePathName. If FaxFilePathName is not found, then exit the itteration as no file was ever created.

I have the main part complete except the line:

Code:

Set objWorkbook = objExcel.WorkBooks.Open(FaxFilePathName)

How can I check if a file exists or if not?

My code:

Code:

If FaxFilePathName = "Empty" Then
        Call CheckingFaxFilePathName ()
        Set objExcel = CreateObject("Excel.application") 'Create a new Microsoft Excel object
        objExcel.Visible = True
        Set objWorkbook = objExcel.WorkBooks.Open(FaxFilePathName)
        Set objDriverSheet = objWorkbook.Worksheets("Sheet1")
        If IsNull(objWorkbook) = True Then
                Reporter.ReportEvent micWarning,"Missing FaxFilePathName" , " The file for 'FaxFilePathName = " & FaxFilePathName & "' and was thus not found. Fax testing was not completed"
                Exit Function
        End If
Else
        Set objExcel = CreateObject("Excel.application") 'Create a new Microsoft Excel object
        objExcel.Visible = True
        Set objWorkbook = objExcel.WorkBooks.Open(FaxFilePathName)
        Set objDriverSheet = objWorkbook.Worksheets("Sheet1")
End If

---

I think I figured it out: (let me know if this is bad coding)

Code:

Set objExcel = CreateObject("Excel.application") 'Create a new Microsoft Excel object
Set objFso=CreateObject("Scripting.FileSystemObject")
objExcel.Visible = True

If FaxFilePathName = "Empty" Then
        Call CheckingFaxFilePathName ()
                If Not objFso.FileExists(FaxFilePathName)  Then
                Reporter.ReportEvent micWarning,"Missing FaxFilePathName" , " The file for 'FaxFilePathName = " & FaxFilePathName & "' and was thus not found. Fax testing was not completed"
                Exit Function
        End If
End If

Set objWorkbook = objExcel.WorkBooks.Open(FaxFilePathName)
Set objDriverSheet = objWorkbook.Worksheets("Sheet1")

[sshukla12]

Hi,
U can use FSO, it will help u out.

Code:

Set fso=CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(file_location)) Then
    'do something
    else
    'do something    
End If

file_location---> complete path for ur file to open/read

Regards,
Sankalp

[mv8167]

Thx Sankalp,

I must have read your mind. I completely forgot about doing this. Thx for confirming the code. ;-)

Have a great weekend.

Lorena

[sshukla12]

u r welcome..