Checking if a workbook exists
 

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?

Checking if a workbook exists
 

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" 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?

Checking if a workbook exists
 

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"
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" 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?

Checking if a workbook exists
 

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

Checking if a workbook exists
 

On Oct 31, 12:07*am, GS 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