Checking if a workbook exists

[Pierre Leclerc]

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?

 

[Gord Dibben]

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

 

[GS]

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

 

[Don Guillett]

Pierre Leclerc used his keyboard to write :



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