Checking and opening a file

I want to write a procedure, where the user enters a name of an Excel File and this is going to be opened. First there is a check whether it is already opened. Two problems, one is, that the check doesn't work and the second thing is that, if the entered file doesn't exist, the program should say so and exit instead of giving an error message.

The check works here, when the name of the file is set:


DateiOffen = False ' File opened = false

Mappenname = "Quelle.xls" ' Bookname = Quelle.xls
For Each wb In Workbooks
If wb.Name = Mappenname Then
DateiOffen = True
MsgBox ("Bereits offen") ' Already opened
End If
Next wb

If DateiOffen = False Then Workbooks.Open FileName:="G:\Quelle.xls"



It doesn't work here, if I enter "quelle":


Sub Datei()
Dim dateiname As String
dateiname = Application.InputBox("Dateiname angeben") ' Enter filename
MsgBox dateiname

If dateiname = "Falsch" Then Exit Sub

DateiOffen = False

Mappenname = dateiname
For Each wb In Workbooks
If wb.Name = Mappenname Then
DateiOffen = True
MsgBox ("Bereits offen") ' Already opened
End If
Next wb

If DateiOffen = False Then Workbooks.Open FileName:=dateiname

1) Mappenname = "Quelle.xls" ' Bookname = Quelle.xls

You must get rid of the .xls from Mappenname
The name property of the workbooks will return Quelle not Quelle.xls


2) You might want to use GetOpenFilename to get your filename rather than an inputbox

fileToOpen = Application.GetOpenFilename

Cool, using GetOpenFilename, I get the file opening window. However the Check still doesn't work, means, the program still opens the file which is already opened instead of giving out the message that it has been already opened.


Sub Datei()
Dim dateiname As String
dateiname = Application.GetOpenFilename
MsgBox dateiname

If dateiname = "Falsch" Then Exit Sub

DateiOffen = False

Mappenname = dateiname
For Each wb In Workbooks
If wb.Name = Mappenname Then
DateiOffen = True
MsgBox ("Bereits offen")
End If
Next wb

If DateiOffen = False Then Workbooks.Open FileName:=dateiname

End Sub


In a different project, I want the user to enter 3 different numbers. I think I have to use a userform for this, rather than 3 Input Boxes...

One correction about what I said about the Name property the name property will return the file extention (.xls) if the workbook has been saved once already.
Try using UCase the names.

If UCase(wb.Name) = UCase(Mappenname) Then

Also because GetOpenFileName returns the entire path you need to remove the path from the filename

myfile = Application.GetOpenFilename
'reverse string
myfile = StrReverse(myfile)
'take string to the left of the first \
myfile = Left(myfile, InStr(myfile, "\") - 1)
'return string to correct order
myfile = StrReverse(myfile)

VBA doesn't know StrReverse. Don't tell me, this is only possible in 2000? I'm using 97 here, but I will try it at home (2000). Could you write a function please which reverses text?

Could I not just use the Right command without reversing the text? I tried that, but it didn't quite work.

Edit: Here:


Sub Datei()
Dim dateiname As String
Dim dateiRev As String
Dim dateiRev2 As String

dateiname = Application.GetOpenFilename

'reverse string
dateiRev = ""

For i = 0 To Len(dateiname) - 1
dateiRev = dateiRev & Mid$(dateiname, Len(dateiname) - i, 1)
Next

MsgBox dateiRev

'take string to the left of the first \
dateiRev = Left(dateiRev, InStr(dateiRev, "\") - 1)

'return string to correct order
For i = 0 To Len(dateiRev) - 1
dateiRev2 = dateiRev2 & Mid$(dateiRev, Len(dateiRev) - i, 1)
Next

MsgBox dateiRev2

Exit Sub

Using just the Right$ function would work if the path name only has one "\" in it. The reason for reversing the string was to get the last "\" to become the first "\" because InStr returns the position of the first "\". Now to write your own revsersestring function you need to use
For i = Len(strFile) To 1 Step -1
rather than
For i = 0 To Len(dateiname) - 1


Function ReverseString(strFile As String) As String
Dim i As Long
Dim strReturn As String
For i = Len(strFile) To 1 Step -1
strReturn = strReturn & Mid$(strFile, i, 1)
Next
ReverseString = strReturn
End Function