Check for file before opening
 

Hi,

I need excel to open another excel file (workbook) as part of a macro.
How do I checkt the presence of the file before I try to open it so I
don't generate an error if the file is not there. If the file does not
exist, I will then create it, which I can do.

Thanks, Simon


--
simonhall
------------------------------------------------------------------------
simonhall's Profile: http://www.excelforum.com/member.php...o&userid=30582
View this thread: http://www.excelforum.com/showthread...hreadid=561328

Check for file before opening
 

Use the dir function

ie

x = Dir("c:\windows\file.log") will return x= file.log if the file
exists and "" if the file doesn't exist.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561328

Check for file before opening
 

Hi Simon

This might help you.

Regards

Andrew Bourke

Sub Update()
Application.DisplayStatusBar = True 'Show progress
Application.ScreenUpdating = False
'uPath = update path location in ref section on Control page
uPath = Range("uPath")
'1. Check for latest update
Application.StatusBar = "Checking for latest update..."
On Error Resume Next
Namer = Trim(Dir(uPath))
If Left(Namer, 6) < "ABData" Then
MsgBox "Update file not located."
GoTo Ender
End If



'2. Has someone already opened the file ?
Application.StatusBar = "Checking if file open already..."
For i = 1 To Workbooks.Count
If Left(Workbooks(i).Name, 6) = "ABData" Then
MsgBox "File already open ..."
GoTo Ender
End If
Next i

'3. Load data from source
Application.StatusBar = "Loading data from source..."

Workbooks.OpenText Filename:=uPath, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, Comma:=True


..
..
..
end sub



simonhall wrote:
Hi,

I need excel to open another excel file (workbook) as part of a macro.
How do I checkt the presence of the file before I try to open it so I
don't generate an error if the file is not there. If the file does not
exist, I will then create it, which I can do.

Thanks, Simon

Check for file before opening
 

Simon,
Why not attempt to open and trap the error.
Errors are not inherently bad; they provide you with information.
Dim WB As workbook
On Error Resume Next
Set WB=workbooks.open(PathToYourMaybeFile)
on error goto 0
If WB is nothing then
Set WB=workbooks.add
'Or whatever creation code you need
End if

NickHK

"simonhall" wrote
in message ...

Hi,

I need excel to open another excel file (workbook) as part of a macro.
How do I checkt the presence of the file before I try to open it so I
don't generate an error if the file is not there. If the file does not
exist, I will then create it, which I can do.

Thanks, Simon


--
simonhall
------------------------------------------------------------------------
simonhall's Profile:
http://www.excelforum.com/member.php...o&userid=30582
View this thread: http://www.excelforum.com/showthread...hreadid=561328

Check for file before opening
 

Thanks Nick,

The line
If workbooks.open(filename) is nothing then creatfile()
is what I needed.

It seems funny to be writing code in words that I understand (ie "if __
is nothing then"). Just a matter of knowing what words work and what
order to put them in.

Works well now.

Simon


--
simonhall
------------------------------------------------------------------------
simonhall's Profile: http://www.excelforum.com/member.php...o&userid=30582
View this thread: http://www.excelforum.com/showthread...hreadid=561328

Check for file before opening
 

Simon,
You will need the On Error Resume Next also.
And better to include On Error Resume 0 after.

That's the beauty of BASIC.

NickHK

"simonhall" wrote
in message ...

Thanks Nick,

The line
If workbooks.open(filename) is nothing then creatfile()
is what I needed.

It seems funny to be writing code in words that I understand (ie "if __
is nothing then"). Just a matter of knowing what words work and what
order to put them in.

Works well now.

Simon


--
simonhall
------------------------------------------------------------------------
simonhall's Profile:
http://www.excelforum.com/member.php...o&userid=30582
View this thread: http://www.excelforum.com/showthread...hreadid=561328