Using relative path names

[Asterix]

Dear all,

using this line...
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
... it seems that I can't open a StrFilePath that includes spaces. In fact, MrExcel has a thread which also refers to this problem (but that does not propose a solution). Is there a solution?

Thought about replacing the spaces with "%20" like in an URL but that causes it's own problems (i.e. it doesn't seem to work).

[Asterix]

I think I should qualify this to avoid fixing the symptom and not the problem.

I'm using this line as part of a function to check whether a file is open or not. If it's open, I want to activate it. If it's not open, I want to open it.

Both file path and name include spaces and there is no drive mapped (so I'm using relative pathes).

Any solution will do me, but understanding how to work around the "space issue" (assuming it is a true issue) would arm me for the future.

[Bob Phillips]

Works okay for me. What is the full code, including where the variable is primed.

[Asterix]

Having corrected a simple error (using the wrong result value for a condition), I think in hindsight the problem was in my coding.

I.e. this is a false alarm/ coder error.

Sorry for wasting any time.

[mdmackillop]

I think in hindsight the problem was in my coding.

What a great signature quotation!

Glad your sorted.
MD

[Bob Phillips]

What a great signature quotation!

Glad your sorted.
MD

It would become a bit tedious though :devil2:

[Asterix]

thanks for the support:p:bow::devil2:

[mdmackillop]

It would become a bit tedious though :devil2:
:thumb

[Asterix]

Sorry, thought I had cracked this but obviously not.

StrFilePath = "O:\NGMData7\Reserved\IT Library\Scheduling\Resource Allocations\Resource Allocations Spreadsheet\Resource Allocations - wc080301v1.xls"

It is a shared file that I do not control and that changes it's title every week. All I know is that it always starts with "Resource Allocations - wc..." (I have already found out how to capture the entire path and filename).

The function below was sourced from the net so am assuming it's good code (looks logical to me) and the reason why I added "...ED" to the function title is lost in the mysteries of time.

Problem is, if the file is NOT open, whenever the "Open StrFilePath line is executed, the file will not open. This is compounded by Err.Number still returning "0" and isfileopenED therefore being made "1" (i.e. the switch indicates that the file is open, but it ain't).



Public Function isfileopenED(StrFilePath As String) As Integer
Dim FileNum As Integer

'First check filepath exists
If Len(Dir(StrFilePath)) > 0 Then
FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
If Err.Number = 0 Then
isfileopenED = 1 'File open
Else
isfileopenED = 0 'File Closed
End If
Close FileNum
Else
isfileopenED = 2 'File not found
End If

End Function

[Bob Phillips]

I think it is because you are setting the return value to 1 if the file is not open, whereas TRUE in VBA is -1.

... but, you should return TRUE or FALSE not 1 or 0, that is a boolean, it is more logically sound.

... and a function called ISxxxx should return Yes/No, which is silly here as you want a Boolean result, so I would call the Function FileIsOpen.

... and you are using one function to do two things, test if the file exists and if it is open, hence the silly integer result. You should split them into two functions and test each separately for good code abstraction.

And it would look like this



Public Function FileExists(ByRef StrFilePath As String) As Boolean
FileExists = Len(Dir(StrFilePath)) > 0
End Function

Public Function FileIsOpen(ByRef StrFilePath As String) As Boolean
Dim FileNum As Integer

FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
FileIsOpen = Err.Number = 0
Close FileNum
End Function

[Asterix]

Cheers, I'll adapt as you suggest.

In the meantime, I had got it "working" by replacing
Open StrFilePath For Input Lock Read As #FileNum with
Workbooks.Open (StrFilePath).

Is that reasonable or have I lost something by doing that?

[Bob Phillips]

I updated my post after you posted, so please check back with it.