PDA

View Full Version : Opening File in VBA code



frubeng
05-12-2009, 06:43 AM
Hello,

I use dthe code as follows (which i found in this great forum):

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error Goto 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub


But it gives me an error saying it can't find the file. I have used the full pathname:

"H:\My Documents\Spreadsheets\Outlier\MSTOP.xls"

Is there something that might be happening?

Also, if I have the file "MSTOP.xls" in the same folder as the running file, is it possible to refer to it as just "MSTOP.xls"? (which would help if i send files to someone else and dont know where she will store them)

Thanks for your help!!


EDIT: Just to point out, I have used my own filename and not the one in the sample code I show above.

lucas
05-12-2009, 06:57 AM
The code works fine for me. I'm guessing that you have the path incorrect or the filename wrong but this works for opening the file if it is in the same directory:

Sub test()
If Not IsFileOpen(ThisWorkbook.Path & "\volker2.xls") Then
Workbooks.Open ThisWorkbook.Path & "\volker2.xls"
End If
End Sub