Before I do workbooks.open "toto.xlsx" I want to check if such workbook really exist on my disk.

How do I go about doing this?

Greetings Harald

You're still kicking around eh!

Good to know that.

Other than many years older and no longer an MVP, things haven't
changed much for me.

Drop me an email if you want. Would like to get caught up.

Change phnorton to gorddibb


Gord

On Mon, 31 Oct 2011 01:00:42 +0100, "Harald Staff"
<(E-Mail Removed)> wrote:

>Sub test()
>If Dir("C:\Stuff\toto.xlsx") = "" Then
> MsgBox "Get outta here", vbInformation
> Exit Sub
>End If
>'meaningful code goes here
>End Sub
>
>HTH. Best wishes Harald
>
>
>"Pierre Leclerc" <(E-Mail Removed)> wrote in message
>news:1218490.933.1320017545974.JavaMail.geo-discussion-forums@yqgn17...
>> Before I do workbooks.open "toto.xlsx" I want to check if such workbook
>> really exist on my disk.
>>
>> How do I go about doing this?
>>

Pierre Leclerc used his keyboard to write :
> Before I do workbooks.open "toto.xlsx" I want to check if such workbook
> really exist on my disk.
>
> How do I go about doing this?

Here's a reusable function that returns a boolean when passed a
path/filename.

Function bFileExists(Filename As String) As Boolean
' Checks if a file exists in the specified path
' Arguments: fileName The fullname of the file
' Returns: TRUE if the file exists

On Error Resume Next
bFileExists = (Dir$(Filename) <> "")
' bFileExists = (FileLen(Filename) <> 0) '//optional method
End Function

Example use:
If bFileExists("C:\MyDocuments\toto.xlsx") Then...


Here's another reusable function to test if the file is already open.

Function bBookIsOpen(wbkName) As Boolean
' Checks if a specified workbook is open.
' Arguments: wbkName The name of the workbook
' Returns: True if the workbook is open

Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbkName)
bBookIsOpen = (Err = 0)
End Function

Example use:
If bBookIsOpen("toto.xlsx") then workbooks("toto.xlsx").Activate

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Oct 31, 12:07*am, GS <g...@somewhere.net> wrote:
> Pierre Leclerc used his keyboard to write :
>
> > Before I do workbooks.open "toto.xlsx" I want to check if such workbook
> > really exist on my disk.
>
> > How do I go about doing this?
>
> Here's a reusable function that returns a boolean when passed a
> path/filename.
>
> Function bFileExists(Filename As String) As Boolean
> ' Checks if a file exists in the specified path
> ' Arguments: * *fileName * *The fullname of the file
> ' Returns: * * *TRUE if the file exists
>
> * On Error Resume Next
> * bFileExists = (Dir$(Filename) <> "")
> ' *bFileExists = (FileLen(Filename) <> 0) '//optional method
> End Function
>
> * Example use:
> * If bFileExists("C:\MyDocuments\toto.xlsx") Then...
>
> Here's another reusable function to test if the file is already open.
>
> Function bBookIsOpen(wbkName) As Boolean
> ' Checks if a specified workbook is open.
> ' Arguments: * *wbkName * The name of the workbook
> ' Returns: * * *True if the workbook is open
>
> * Dim X As Workbook
> * On Error Resume Next
> * Set X = Workbooks(wbkName)
> * bBookIsOpen = (Err = 0)
> End Function
>
> * Example use:
> * If bBookIsOpen("toto.xlsx") then workbooks("toto.xlsx").Activate
>
> HTH
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc
============
This is one I use that is called from a double click event where the
filename is typed into a cell.
If file is open it is activated. If not, it is opened.

Sub GetWorkbook()If ActiveCell.Value = "" Then Exit Subworkbookname =
ActiveCell.ValueOn Error GoTo OpenWorkbookWindows(workbookname &
".xls").ActivateExit SubOpenWorkbook:Workbooks.Open(workbookname &
".xls").RunAutoMacros xlAutoOpenEnd Sub