PDA

View Full Version : Solved: Using IsInPlace



kathyb0527
07-15-2008, 04:12 PM
I'm writing code where the user needs to open the "source data" which is a different file each time. I've used the getopenfilename and assigned a variable and all is working as it should.
stSourcefilename = Application.GetOpenFilename
If stSourcefilename = "False.xls" Then
Exit Sub
End If
wbSourcefile = Workbooks.Open(stSourcefilename)
However, I want to make sure that the user doesn't open a workbook already opened so I tried this

If Workbooks(stSourcefilename).IsInplace = True Then
Set wbSourcefile = Workbooks(stSourcefilename)
Else: Set wbSourcefile = Workbooks.Open(stSourcefilename)
End If
I get a subscript out of range error.

Is it because stSourcefilename contains the whole path? Is there a way to get just the file name? Or am I totally off base with what I'm doing?

Thanks for your help,
Kathyb0527 :think:

Bob Phillips
07-15-2008, 04:23 PM
stFullName = Application.GetOpenFilename
stShortName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, "\"))
If stShortName = "False.xls" Then
Exit Sub
End If
On Error Resume Next
Set wbSourceFile = Workbooks(stShortName)
On Error GoTo 0
If wbSourceFile Is Nothing Then
Set wbSourceFile = Workbooks.Open(stFullName)
End If