"open" dialogue box if file does not exist

[bzsurf03]

I have VBA code in an excel module that opens another workbook with a hardcoded path. Most likely, this path will stay the same, but I want to have another way of opening the file. If the program cannot find the file, I want the code to prompt the user with a save dialogue box. I am having a tough time figuring out how to go about coding the part that checks if the file exists or not. Any help would be great. Thanks in advance.

 

[EBGreen]

You could look at the documentation for the Scripting.FileSystemObject.

You could also just try to open the form and trap the error then pop the dialog if the error indicates that the workbook is not there.

 

[Loomah]

So you may end up with something like this

Sub a()
Dim ofs As Object
Set ofs = CreateObject("Scripting.FileSystemObject")
If Not ofs.fileexists("C:/Toby.xls") Then
    Application.Dialogs(xlDialogOpen).Show
End If
Set ofs = Nothing
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Bowers74]

Just a couple of FAQ's for your information (and as a reminder to others who may have forgotten them) ;-)

faq707-4116 File and Folder Procedures
faq707-4114 Open File(s) using the GetOpenFilename dialog

Enjoy!!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[PHV]

prompt the user with a save dialogue box
Take a look at the Application.GetSaveAsFilename function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi bzsurf03,

Why not just trap the error that will occur if the file isn't found when you try to open it ..

[blue]On Error Resume Next
Workbooks.Open ("[i]YourPath[/i]\[i]YourFile[/i].xls")
If Err.Number = 1004 Then 
    MsgBox "File Not Found"
    [green]' Do what you want here[/green]
    :
    :[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.