Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to quickly check any error for opening excel files? | Excel Discussion (Misc queries) | |||
Opening file in Excel 2003 opens multipule instances of same file | Excel Discussion (Misc queries) | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) |